:

Delete inactive items

Hi,
Can someone please give me a SQL Query make all On Hand quantities zero and to delete all Inactive items? I am opening a new store and have copied my database to the new location, but I don't think it's necessary to keep 3 year old items on file.
Reply to
geokar
I do not know your queries, but to delete all quantities (OH and negative) you should just do a full physical inventory to set the qty = 0.
SO Mgr> Inventory> Physical Inv> Generate for all items> Calculate> Commit.
Then back report in HQ Mgr with a Request Full Inv Count and a task 190.
--
Jocelyn


"geokar" wrote:
 Click to see the full signature
Reply to
=?Utf-8?B?am9jZWx5bnA=?=
hi,
normal procedure..before running any update or delete query, back up your data..
this is the query to delete inactive items
* I assume that you have updated/set your item into inactive --------------------------------------------- delete from item where inactive=1 --------------------------------------------- or if you want to delete 2000 year old data you can use this --------------------------------------------- delete from item where (item.lastupdated Great,
and
year
Reply to
GregDxb
This is a multi-part message in MIME format.
------=_NextPart_000_030C_01C70270.F2337600 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Geokar,
Again, this routine will leave orphaned records. This will only delete = the entries in the Item database, it will not delete any Supplier's = numbers or Alias' for starters.
You're better off, if you _really_ want to delete them, to do it though = the program!
--=20
Jeff=20 Check Point Software
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D
You must be using Outlook Express or some other type of newsgroup reader = to see and download the file attachment(s). If you are not using a reader, = follow the link below to setup Outlook Express. Click on "Open with = newsreader" under the MS Retail Management System on the right.
formatting link
=3D=3D=3D
hi,
normal procedure..before running any update or delete query, back up = your=20 data..
this is the query to delete inactive items
* I assume that you have updated/set your item into inactive --------------------------------------------- delete from item where inactive=3D1 --------------------------------------------- or if you want to delete 2000 year old data you can use this --------------------------------------------- delete from item where (item.lastupdated Great,
quantities zero and=20
copied my=20
to keep 3 year=20
------=_NextPart_000_030C_01C70270.F2337600 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
=EF=BB=BF
Geokar,   Again, this routine will leave orphaned = records. =20 This will only delete the entries in the Item database, it will not = delete any=20 Supplier's numbers or Alias' for starters.   You're better off, if you _really_ want to = delete them,=20 to do it though the program! -- Jeff Check Point Software   =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D   You must be using Outlook Express or some other type of newsgroup = reader=20 tosee and download the file attachment(s).  If you are not = using a=20 reader, followthe link below to setup Outlook Express.  Click = on "Open=20 with newsreader"under the MS Retail Management System on the = right.  
formatting link

"GregDxb" < snipped-for-privacy@discussions.mic= rosoft.com>=20
snipped-for-privacy@microsoft.com...hi,no= rmal=20 procedure..before running any update or delete query, back up your=20 data..this is the query to delete inactive items* = I assume=20 that you have updated/set your item into=20 inactive---------------------------------------------delete = from item=20 where = inactive=3D1---------------------------------------------or if = you=20 want to delete 2000 year old data you can use=20 this---------------------------------------------delete from = item=20 where (item.lastupdated <=3D CONVERT(DATETIME, '2001-01-01 = 00:00:00',=20 102))--------------------------------------------Run this = query=20 after backup is made successfully.Good Luck"geokar"=20 wrote:> Great,> Now how about deleting inactive=20 > > > OK, = here is=20 your delete qty query from SO Admin:> > > > = *Backup=20 first*> > > > UPDATE Item SET Quantity =3D = 0> >=20 > > I would also back report this one in HQ Mgr.> = >=20 > > > > > > > > > > = "geokar"=20 wrote:> > > > > Hi,> > > > = >=20 > Can someone please give me a SQL Query make all On Hand = quantities zero=20 and > > > to delete all Inactive items?  I am = opening a new=20 store and have copied my > > > database to the new = location, but=20 I don't think it's necessary to keep 3 year > > > old = items on=20 file.
------=_NextPart_000_030C_01C70270.F2337600--
Reply to
CptSoft
Good Point jeff,
I forgot the alias table :) ---delete first the alias--- delete from alias where itemid in (select id from item where inactive=1) ---then delete the item--- delete from item where inactive=1
this is just a solution of what you want.. This is just a practical solution to delete n # of records rather than going to your records n times depends on how many inactive you want to delete.
entries in the Item database, it will not delete any Supplier's numbers or Alias' for starters.
program!
zero and
copied my
3 year
Reply to
GregDxb
You're missing a table. You need to clear the entry for the inactive item from the supplierlist table as well. Otherwise you will have orphaned records in the supplierlist table.
Backup your database first!
delete from alias where itemid in (select id from item where inactive=1) delete from supplierlist where itemid in (select id from item where inactive=1) delete from item where inactive=1
If you are in an HQ environment you will also need to delete the records out of the itemdynamic table as well as the 3 tables above. The ItemDynamic table is not present in SO databases. So for HQ databases: Backup Database!
delete from alias where itemid in (select id from item where inactive=1) delete from supplierlist where itemid in (select id from item where inactive=1) delete from itemdynamic where itemid in (select id from item where inactive=1) delete from item where inactive=1
entries in the Item database, it will not delete any Supplier's numbers or Alias' for starters.
program!
follow
zero and
copied my
keep 3 year
Reply to
Database Badger
This is a multi-part message in MIME format.
------=_NextPart_000_078F_01C70361.E7D22B10 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Geokar, GregDXB, Jocelyn, Database, and whoever else has the stupid idea = to do this,
Yeah, that would delete some more of the orphaned records, now how about = the orphans here in these tables;
ItemClassComponent InventoryOffline InventoryTransferLog Kit LimitEntry PurchaseOrderEntry Serial TransactionHoldEntry
and there maybe even more cuz I didn't look very hard. ;-)
************************************************
AGAIN, MY POINT IS THAT YOU SHOULDN'T BE DELETING ITEMS THIS WAY. DO IT = THROUGH THE RMS PROGRAM!!!!!!
*************************************************
--=20
Jeff=20 Check Point Software
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D
You must be using Outlook Express or some other type of newsgroup reader = to see and download the file attachment(s). If you are not using a reader, = follow the link below to setup Outlook Express. Click on "Open with = newsreader" under the MS Retail Management System on the right.
formatting link
=3D=3D=3D
"Database Badger" wrote in = message news: snipped-for-privacy@microsoft.com... You're missing a table. You need to clear the entry for the inactive = item=20 from the supplierlist table as well. Otherwise you will have orphaned =
records in the supplierlist table.
Backup your database first!
delete from alias where itemid in (select id from item where = inactive=3D1) delete from supplierlist where itemid in (select id from item where=20 inactive=3D1) delete from item where inactive=3D1
If you are in an HQ environment you will also need to delete the = records out=20 of the itemdynamic table as well as the 3 tables above. The = ItemDynamic=20 table is not present in SO databases. So for HQ databases: Backup Database!
delete from alias where itemid in (select id from item where = inactive=3D1) delete from supplierlist where itemid in (select id from item where=20 inactive=3D1) delete from itemdynamic where itemid in (select id from item where = inactive=3D1) delete from item where inactive=3D1
inactive=3D1)
solution=20
depends on=20
delete the entries in the Item database, it will not delete any = Supplier's numbers or Alias' for starters.
though the program!
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D
reader to
reader, follow
newsreader"
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D
back up your=20
quantities zero and=20
and have copied my=20
necessary to keep 3 year=20
------=_NextPart_000_078F_01C70361.E7D22B10 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
=EF=BB=BF
Geokar, GregDXB, Jocelyn, Database, and = whoever else=20 has the stupid idea to do this,   Yeah, that would delete some more of the = orphaned=20 records, now how about the orphans here in these tables;   ItemClassComponent InventoryOffline InventoryTransferLog Kit LimitEntry PurchaseOrderEntry Serial TransactionHoldEntry   and there maybe even more cuz I didn't look = very hard.=20 ;-)     ************************************************   AGAIN, MY POINT IS THAT YOU SHOULDN'T BE = DELETING ITEMS=20 THIS WAY.  DO IT THROUGH THE RMS PROGRAM!!!!!!   ************************************************* -- Jeff Check Point Software   =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D   You must be using Outlook Express or some other type of newsgroup = reader=20 tosee and download the file attachment(s).  If you are not = using a=20 reader, followthe link below to setup Outlook Express.  Click = on "Open=20 with newsreader"under the MS Retail Management System on the = right.  
formatting link

"Database Badger" <Database snipped-for-privacy@discussions.micro= soft.com>=20
snipped-for-privacy@microsoft.com...You're=20 missing a table.  You need to clear the entry for the inactive = item=20 from the supplierlist table as well.  Otherwise you will have =
orphaned records in the supplierlist table.Backup your = database=20 first!delete from alias where itemid in (select id from item = where=20 inactive=3D1)delete from supplierlist where itemid in (select id = from item=20 where inactive=3D1)delete from item where = inactive=3D1If you are=20 in an HQ environment you will also need to delete the records out = of the=20 itemdynamic table as well as the 3 tables above.  The ItemDynamic =
table is not present in SO databases.So for HQ = databases:Backup=20 Database!delete from alias where itemid in (select id from = item where=20 inactive=3D1)delete from supplierlist where itemid in (select id = from item=20 where inactive=3D1)delete from itemdynamic where itemid in = (select id=20 from item where inactive=3D1)delete from item where=20 > Good = Point=20 jeff,> > I forgot the alias table :)> ---delete = first the=20 alias---> delete from alias where itemid in (select id from = item where=20 inactive=3D1)> ---then delete the item---> delete from = item where=20 inactive=3D1> > this is just a solution of what you = want.. This is=20 just a practical solution > to delete n # of records rather = than going=20 to your records n times depends on > how many inactive you want = to=20 delete.> > > "Jeff @ Check Point Software" = wrote:>=20 > > Geokar,> > > > Again, this routine = will=20 leave orphaned records.  This will only delete the entries in the = Item=20 database, it will not delete any Supplier's numbers or Alias' for=20 starters.> > > > You're better off, if you = _really_ want=20 to delete them, to do it though the program!> > > = > --=20 > > > > Jeff > > Check Point = Software>=20 > > >=20 = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D> > >=20 > You must be using Outlook Express or some other type of newsgroup = reader=20 to> > see and download the file attachment(s).  If you = are not=20 using a reader, follow> > the link below to setup Outlook=20 Express.  Click on "Open with newsreader"> > under the = MS=20 Retail Management System on the right.> > > >
formatting link
= >=20 = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D> > >=20 >   "GregDxb" < snipped-for-privacy@discussions.mic= rosoft.com>=20
snipped-for-privacy@microsoft.com...>=20 >   hi,> > > >   normal=20 procedure..before running any update or delete query, back up your = >=20 >   data..> > > >   this is = the=20 query to delete inactive items> > > >   = * I=20 assume that you have updated/set your item into inactive>=20 >   ---------------------------------------------> =
>   delete from item where inactive=3D1> = >  =20 ---------------------------------------------> >   = or if=20 you want to delete 2000 year old data you can use this>=20 >   ---------------------------------------------> =
>   delete from item where (item.lastupdated <=3D=20 CONVERT(DATETIME, '2001-01-01 > >   00:00:00',=20 102))> >  =20 --------------------------------------------> > >=20 >   Run this query after backup is made = successfully.>=20 > > >   Good Luck> > >=20 > > > = >   >=20 Great,> >   > Now how about deleting inactive=20 items?> >   > > >   > = "jocelyn"=20 wrote:> >   > > >   > = > OK,=20 here is your delete qty query from SO Admin:> >   = >=20 > > >   > > *Backup first*>=20 >   > > > >   > > UPDATE = Item SET=20 Quantity =3D 0> >   > > > = >   >=20 > I would also back report this one in HQ Mgr.> = >  =20 > > > >   > > > = >   >=20 > > >   > > > >   = > >=20 > = >  =20 > > > >   > > > Hi,>=20 >   > > > > >   > > = > Can=20 someone please give me a SQL Query make all On Hand quantities zero = and=20 > >   > > > to delete all Inactive = items?  I=20 am opening a new store and have copied my > >   = > >=20 > database to the new location, but I don't think it's necessary to = keep 3=20 year > >   > > > old items on=20 file
------=_NextPart_000_078F_01C70361.E7D22B10--
Reply to
CptSoft
Hi Geokar,
WOw! your post knockdown every other post in this newgroup because of the Brightest idea that Jeff ??? gave... but seriously...both ideas will give you the same result but there are some adverse effect that JEFF wants to strongly point out which I agree...
Now, having an orphaned records would not hamper your day to day operations but rather if the item we have deleted have track of movement before..meaning if you want to get a historical data then problem will come.
So, if this is a stand alone and a shop data...I don't think it will cause you some trouble...
And for you Jeff, this a free world of interaction. If you want to prove your point do it in a subtle manner.Not to shout !!! ( I don't know if your the same Jeff that make a nasty comment to Roxanne , way back but I think it is not cool).
Good Day
Note* you will not get my VOTE for MVP ( for now !) chiao :)
this,
orphans here in these tables;
THROUGH THE RMS PROGRAM!!!!!!
message news: snipped-for-privacy@microsoft.com...
inactive=1)
solution
on
the entries in the Item database, it will not delete any Supplier's numbers or Alias' for starters.
the program!
to
follow
your
quantities zero and
copied my
to keep 3 year
Reply to
GregDxb
This is a multi-part message in MIME format.
------=_NextPart_000_011C_01C7038B.F7287630 Content-Type: multipart/alternative; boundary="----=_NextPart_001_011D_01C7038B.F7287630"
------=_NextPart_001_011D_01C7038B.F7287630 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Geokar,
GIGO (Garbage in, Garbage out), If you not going to do it right, why = bother? Two years from now, you won't remember why you or the person = you're on the phone with trying to figure out why this orphaned info is = here, you'll suddenly remember this group of posts. ;-)
Greg,
Actually I tried subtle in my very first post about this;
though the program!
But everyone kept adding to the fire, so I figured I'd yell. ;-)
Roxanne Hunter from Maui?
The posts below got you in a tizzy??
Roxanne,=20
If you've been training and supporting POS software for 8 years, a) why = are=20 you posting a very basic question, b) in the public end-user newsgroup, = c)=20 without even searching this newsgroup on how to add a column to a = report,=20 that's been discussed 100's of times, d) to display your lack of basic=20 knowledge about the RMS program.=20
Glad I didn't buy it from you. You give the rest of us a bad name.=20
Or are you that rabble rouser reseller from that's trying to bail from=20 failing Retail Pro program?=20
Rather than coming here to ask all of the end-users to help you learn = about=20 RMS/POS, a) try looking back at your training manuals, b) or your 300 = page=20 Partner Implementation Guide, c) and/or use the PartnerSource = Knowledgebase=20 that has these and many, many, many more answers to your basic to very=20 complicated questions.=20
If you _still_ can't figure it out, ask the question in either the = reseller=20 or the managed reseller group!=20
Rant off!=20 --=20 *=20
"Roxanne" wrote in message=20
news: snipped-for-privacy@microsoft.com...=20 Thanks for the response. I guess everybody out there knows how to "add a =
column" to a report. But I don't. I was looking around trying to figure = it=20 out but I just don't have the time. I would so appreciate either=20 instructions=20 to add the column or if anybody could just create a valuation report = with=20 the=20 unit of measure column - I will try to reciprocate with whatever skill I =
might have to offer in exchange.=20 My client is actually waiting for me so that he can go and start = counting=20 his bulk warehouse.=20 for future use I guess. thanks all.=20
and as for the inability to simply scan items and create tags - = Ludicrous.=20 Who do I contact that cares enough about a "Product Suggestion"?=20
I have been selling installing training and supporting Point of Sale=20 software and hardware for the last 8 years now and it is quite obvious = that=20 whoever has finalized these products has not been in the "field" or the=20 "trenches" as they say. Or else they had pretty mellow customers who = didn't=20 really want to utilize the software as a total solution.=20
I love the RMS software and its active report windows, etc. I think it = has=20 some really trick and needed features. But just when you get somebody = hooked=20 on such a fine solution such as the active reporting, you cannot do=20 something=20 as basic as put a shelf tag in your store so that people can buy the=20 product.=20 Not easily at least. Or import data collected items for purchase order=20 creation. Transfer in/out.=20
I believe if you guys can customize a report, you can certainly modify = the=20 QSImport Utility to include importing to these specified files. What do = you=20 think? . =20
And she responded with;
"Roxanne" wrote in message=20
=20 I do apologize for allowing my frustrations of finding solutions=20 to be aired on this customer site. As I am sure we have all=20 experienced, there are times when I am in a bind and reaching=20 out for a solution in any area of Microsoft.=20
Unprofessional on my part and I will be more aware of my actions=20 in the future.=20
I thank Glenn Adams very much for coming through with such a=20 fine solution for me. Thanks alot Glenn.=20
And Jeff, you are correct in that - the resources are available=20 through Microsoft - which is why I choose Microsoft. In Hawaii,=20 we sometimes feel like we are on the edge of the earth when it=20 comes to support with the time differences etc. Microsoft and=20 its network of people who are associated with Microsoft (whether = customer=20 or reseller) is the one provider who we can depend upon.=20
I only have to remember how distraught I get when I read posts=20 that are negative about RMS software and this will keep me and=20 my opinions in check.=20
All software programs leave something to be desired to a=20 particular user. but in general if 98% of the features and=20 functions satisfy the application, you should be able to work=20 around those few things that may seem so important at the time.=20
To all that log in here, you do such a good job supporting=20 each other and that is what it is all about.=20
Jeff, if you knew me, you would buy from me because I as a=20 reseller am so involved in helping my customers get things done=20 without disrupting their normal day to day jobs. I feel that is=20 my job to make this experience of implementing their point of=20 sale system as easy and effortless as possible.=20
And no - I am not just a "rabble rouser" who is going to "rant=20 off". What I am is a dedicated reseller with my customers'=20 satisfaction as the main priority. Fortunately, Microsoft=20 solutions have been the best solutions to meet this requirement.=20
Thanks all. I hope to be able to reciprocate with some little=20 tip or trick I may have found.=20
Mahalo,=20 Roxanne=20
=20
Why did this upset you? Would you _really_ want to buy from her? She's = said she's been doing POS for 8 YEARS!!! Never said anything about RMS.
There are posts here almost everyday about the crappy support their = resellers are giving them!! Anything from a $500 support call to = install a keyboard with a mag stripe reader to _never_ calling back, = _ever_!!
I don't rememeber if she _ever_ posted in the private reseller groups, = as suggested. In one of the newsgroups, the questions are even answered = by _MS support people_ and I still don't rememeber her ever asking = there.
Do you really want a reseller that learns by asking her customers how = RMS works? In essence, that's what she is/was doing here on this public = newsgroup.
Look at it this way, I'm trying to improve the resellers that you (not = you, you, but everyone reading/posting here) deal with. If they = can't/won't take the hints, suggestions, statements, ridicule from us, = the resellers will never be worth a crap, just box movers!!
--=20
Jeff=20 Check Point Software
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D
You must be using Outlook Express or some other type of newsgroup reader = to see and download the file attachment(s). If you are not using a reader, = follow the link below to setup Outlook Express. Click on "Open with = newsreader" under the MS Retail Management System on the right.
formatting link
=3D=3D=3D
Hi Geokar,
WOw! your post knockdown every other post in this newgroup because of = the=20 Brightest idea that Jeff ??? gave... but seriously...both ideas will = give you=20 the same result but there are some adverse effect that JEFF wants to = strongly=20 point out which I agree...
Now, having an orphaned records would not hamper your day to day = operations=20 but rather if the item we have deleted have track of movement = before..meaning=20 if you want to get a historical data then problem will come.
So, if this is a stand alone and a shop data...I don't think it will = cause=20 you some trouble...
And for you Jeff, this a free world of interaction. If you want to = prove=20 your point do it in a subtle manner.Not to shout !!! ( I don't know = if your=20 the same Jeff that make a nasty comment to Roxanne , way back but I = think it=20 is not cool).
Good Day
Note* you will not get my VOTE for MVP ( for now !) chiao :)
idea to do this,
about the orphans here in these tables;
DO IT THROUGH THE RMS PROGRAM!!!!!!
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D
reader to
reader, follow
newsreader"
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D
inactive item=20
orphaned=20
inactive=3D1)
where=20
records out=20
ItemDynamic=20
inactive=3D1)
where=20
inactive=3D1)
inactive=3D1)
practical solution=20
times depends on=20
only delete the entries in the Item database, it will not delete any = Supplier's numbers or Alias' for starters.
it though the program!
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D
newsgroup reader to
a reader, follow
newsreader"
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D
message news: snipped-for-privacy@microsoft.com...
back up your=20
Hand quantities zero and=20
store and have copied my=20
necessary to keep 3 year=20
------=_NextPart_001_011D_01C7038B.F7287630 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
=EF=BB=BF
Geokar,   GIGO  (Garbage in, Garbage out), If you = not going=20 to do it right, why bother?  Two years from now, you won't=20 remember why you or the person you're on the phone with trying to = figure=20 out why this orphaned info is here, you'll suddenly remember this group = of=20 posts.  ;-)     Greg,   Actually I tried subtle in my very first = post=20 about this; >   > = > You're=20 better off, if you _really_ want to delete them, to do it though the=20 program! But everyone kept adding to the fire, so I = figured I'd=20 yell.  ;-)       Roxanne Hunter from Maui?     The posts below got you in a = tizzy??   Roxanne, If you've been training and supporting POS = software for 8=20 years, a) why are you posting a very basic question, b) in the = public=20 end-user newsgroup, c) without even searching this newsgroup on how = to add a=20 column to a report, that's been discussed 100's of times, d) to = display your=20 lack of basic knowledge about the RMS program. Glad I didn't buy it from you.  You give = the rest of=20 us a bad name. Or are you that rabble rouser reseller from = that's trying=20 to bail from failing Retail Pro program? Rather than coming here to ask all of the = end-users to=20 help you learn about RMS/POS, a) try looking back at your training = manuals,=20 b) or your 300 page Partner Implementation Guide, c) and/or use the=20 PartnerSource Knowledgebase that has these and many, many, many more = answers=20 to your basic to very complicated questions. If you _still_ can't figure it out, ask the = question in=20 either the reseller or the managed reseller group! Rant off! -- * "Roxanne" = in message=20 news: snipped-for-privacy@microsoft.com... Thanks for the response. I guess everybody out = there knows=20 how to "add a column" to a report. But I don't. I was looking around = trying=20 to figure it out but I just don't have the time. I would so = appreciate=20 either instructions to add the column or if anybody could just = create a=20 valuation report with the unit of measure column - I will try to =
reciprocate with whatever skill I might have to offer in exchange. = My=20 client is actually waiting for me so that he can go and start counting = his=20 bulk warehouse. for future use I guess. thanks all. and as for the inability to simply scan items = and create=20 tags - Ludicrous. Who do I contact that cares enough about a = "Product=20 Suggestion"? I have been selling installing training and = supporting=20 Point of Sale software and hardware for the last 8 years now and it = is quite=20 obvious that whoever has finalized these products has not been in = the=20 "field" or the "trenches" as they say. Or else they had pretty = mellow=20 customers who didn't really want to utilize the software as a total=20 solution. I love the RMS software and its active report = windows,=20 etc. I think it has some really trick and needed features. But just = when you=20 get somebody hooked on such a fine solution such as the active = reporting,=20 you cannot do something as basic as put a shelf tag in your = store so=20 that people can buy the product. Not easily at least. Or import = data=20 collected items for purchase order creation. Transfer in/out. =
I believe if you guys can customize a report, = you can=20 certainly modify the QSImport Utility to include importing to these=20 specified files. What do you think? .    And she responded with;   "Roxanne"=20 in message=20
I do apologize for allowing my frustrations of = finding=20 solutions to be aired on this customer site. As I am sure we = have all=20 experienced, there are times when I am in a bind and reaching = out=20 for a solution in any area of Microsoft. Unprofessional on my part and I will be = more aware=20 of my actions in the future. I thank Glenn Adams very much for coming = through=20 with such a fine solution for me. Thanks alot Glenn. =
And Jeff, you are correct in that - the = resources=20 are available through Microsoft - which is why I choose = Microsoft.=20  In Hawaii, we sometimes feel like we are on the edge of = the=20 earth when it comes to support with the time differences etc.=20 Microsoft and its network of  people who are associated = with=20 Microsoft (whether  customer or reseller) is the one = provider who=20 we can depend upon. I only have to remember how distraught I = get when I=20 read posts that are negative about RMS software and this will = keep me=20 and my opinions in check. All software programs leave something to = be desired=20 to a particular user. but in general if 98% of the features = and=20 functions satisfy the application, you should be able to work=20 around those few things that may seem so important at the = time.=20 To all that log in here, you do such a = good job=20 supporting each other and that is what it is all about. =
Jeff, if you knew me, you would buy from = me because=20 I as a reseller am so involved in helping my customers get = things done=20 without disrupting their normal day to day jobs. I feel that = is my=20 job to make this experience of implementing their point of = sale system=20 as easy and effortless as possible. And no - I am not just a "rabble rouser" = who is=20 going to "rant off". What I am is a dedicated reseller with my =
customers' satisfaction as the main priority. Fortunately, = Microsoft=20 solutions have been the best solutions to meet this = requirement.=20 Thanks all. I hope to be able to = reciprocate with=20 some little tip or trick I may have found. Mahalo, Roxanne=20 Why did this upset you?  Would you = _really_ want=20 to buy from her?  She's said she's been doing POS for 8 = YEARS!!! =20 Never said anything about RMS.     There are posts here almost everyday about = the crappy=20 support their resellers are giving them!!  Anything from a $500 = support=20 call to install a keyboard with a mag stripe reader to _never_ calling = back,=20 _ever_!!   I don't rememeber if she _ever_ posted in the = private=20 reseller groups, as suggested.  In one of the newsgroups, the = questions are=20 even answered by _MS support people_ and I still don't rememeber her = ever asking=20 there.   Do you really want a reseller that learns by = asking=20 her customers how RMS works?  In essence, that's what she = is/was doing=20 here on this public newsgroup.   Look at it this way, I'm trying to improve = the=20 resellers that you (not you, you, but everyone reading/posting here) = deal=20 with.  If they can't/won't take the hints, = suggestions, statements,=20 ridicule from us, the resellers will never be worth a crap, just box=20 movers!! -- Jeff Check Point = Software   =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D   You must be using Outlook Express or some other type of newsgroup = reader=20 tosee and download the file attachment(s).  If you are not = using a=20 reader, followthe link below to setup Outlook Express.  Click = on "Open=20 with newsreader"under the MS Retail Management System on the = right.  
formatting link

"GregDxb" < snipped-for-privacy@discussions.mic= rosoft.com>=20
snipped-for-privacy@microsoft.com...Hi=20 Geokar,WOw! your post knockdown every other post in this = newgroup=20 because of the Brightest idea that Jeff ??? gave... but = seriously...both=20 ideas will give you the same result but there are some adverse = effect that=20 JEFF wants to strongly point out which I agree...Now, = having an=20 orphaned records would not hamper your day to day operations but = rather if=20 the item we have deleted have track of movement before..meaning if = you=20 want to get a historical data then problem will come.So, if = this is a=20 stand alone and a shop data...I don't think it will cause you some =
trouble...And for you Jeff, this a free world of interaction. = If you=20 want to prove your point do it in a subtle manner.Not  to = shout !!! (=20 I don't know if your the same Jeff that make a nasty comment = to =20 Roxanne , way back but I think it is not cool).Good=20 DayNote* you will not get my VOTE for MVP ( for now !) chiao=20 > Geokar, = GregDXB,=20 Jocelyn, Database, and whoever else has the stupid idea to do = this,>=20 > Yeah, that would delete some more of the orphaned records, = now how=20 about the orphans here in these tables;> >=20 ItemClassComponent> InventoryOffline>=20 InventoryTransferLog> Kit> LimitEntry>=20 PurchaseOrderEntry> Serial> TransactionHoldEntry> =
> and there maybe even more cuz I didn't look very hard. = ;-)>=20 > > = ************************************************>=20 > AGAIN, MY POINT IS THAT YOU SHOULDN'T BE DELETING ITEMS THIS=20 WAY.  DO IT THROUGH THE RMS PROGRAM!!!!!!> >=20 *************************************************> > -- = >=20 > Jeff > Check Point Software> >=20 = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D> > You=20 must be using Outlook Express or some other type of newsgroup reader=20 to> see and download the file attachment(s).  If you are = not using=20 a reader, follow> the link below to setup Outlook = Express.  Click=20 on "Open with newsreader"> under the MS Retail Management = System on the=20 right.> >
formatting link
=
= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D>=20 >   "Database Badger" <Database snipped-for-privacy@discussions.micro= soft.com>=20
snipped-for-privacy@microsoft.com...> &nbsp= ;=20 You're missing a table.  You need to clear the entry for the = inactive=20 item >   from the supplierlist table as well.  = Otherwise=20 you will have orphaned >   records in the = supplierlist=20 table.> >   Backup your database = first!>=20 >   delete from alias where itemid in (select id from = item=20 where inactive=3D1)>   delete from supplierlist where = itemid in=20 (select id from item where >  =20 inactive=3D1)>   delete from item where = inactive=3D1>=20 >   If you are in an HQ environment you will also = need to=20 delete the records out >   of the itemdynamic table = as well=20 as the 3 tables above.  The ItemDynamic >   = table is not=20 present in SO databases.>   So for HQ=20 databases:>   Backup Database!> = >  =20 delete from alias where itemid in (select id from item where=20 inactive=3D1)>   delete from supplierlist where = itemid in=20 (select id from item where >  =20 inactive=3D1)>   delete from itemdynamic where itemid = in=20 (select id from item where inactive=3D1)>   delete = from item=20 where inactive=3D1> > > > = >  =20 > >   > Good Point=20 jeff,>   > >   > I forgot the = alias=20 table :)>   > ---delete first the=20 alias--->   > delete from alias where itemid in = (select id=20 from item where inactive=3D1)>   > ---then delete = the=20 item--->   > delete from item where=20 inactive=3D1>   > >   > this = is just a=20 solution of what you want.. This is just a practical solution=20 >   > to delete n # of records rather than going = to your=20 records n times depends on >   > how many inactive = you=20 want to delete.>   > >   >=20 >   > "Jeff @ Check Point Software"=20 wrote:>   > >   > >=20 Geokar,>   > > >   > > = Again,=20 this routine will leave orphaned records.  This will only delete = the=20 entries in the Item database, it will not delete any Supplier's = numbers or=20 Alias' for starters.>   > > = >   >=20 > You're better off, if you _really_ want to delete them, to do it = though=20 the program!>   > > >   > = > --=20 >   > > >   > > Jeff=20 >   > > Check Point = Software>   >=20 > >   > >=20 = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D>   >=20 > >   > > You must be using Outlook Express = or some=20 other type of newsgroup reader to>   > > see = and=20 download the file attachment(s).  If you are not using a reader,=20 follow>   > > the link below to setup Outlook=20 Express.  Click on "Open with newsreader">   = > >=20 under the MS Retail Management System on the = right.>   >=20 > >   > >
formatting link
sp; =20 > >=20 = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D>   >=20 > >   > >   "GregDxb" < snipped-for-privacy@discussions.mic= rosoft.com>=20
snipped-for-privacy@microsoft.com...> &nbsp= ;=20 > >   hi,>   > > = >  =20 > >   normal procedure..before running any update or = delete=20 query, back up your >   > >  =20 data..>   > > >   >=20 >   this is the query to delete inactive=20 items>   > > >   > = >  =20 * I assume that you have updated/set your item into=20 inactive>   > >  =20 --------------------------------------------->   > =
>   delete from item where = inactive=3D1>   >=20 >  =20 --------------------------------------------->   > =
>   or if you want to delete 2000 year old data you can = use=20 this>   > >  =20 --------------------------------------------->   > =
>   delete from item where (item.lastupdated <=3D=20 CONVERT(DATETIME, '2001-01-01 >   > = >  =20 00:00:00', 102))>   > >  =20 -------------------------------------------->   > = >=20 >   > >   Run this query after backup = is made=20 successfully.>   > > >   >=20 >   Good Luck>   > > = >  =20 >   > >=20 >   > >   > = Great,>  =20 > >   > Now how about deleting inactive=20 items?>   > >   > = >  =20 >   >=20 >   > >   > >   > = > OK,=20 here is your delete qty query from SO Admin:>   >=20 >   > > >   > >   = >=20 > *Backup first*>   > >   > = >=20 >   > >   > > UPDATE Item SET = Quantity=20 =3D 0>   > >   > > = >  =20 > >   > > I would also back report this one in = HQ=20 Mgr.>   > >   > > = >  =20 > >   > > >   > = >  =20 > > >   > >   > >=20 >   > >   > > = >   >=20 >   >=20 >   > > >   > >   = >=20 > > Hi,>   > >   > > > =
>   > >   > > > Can someone = please=20 give me a SQL Query make all On Hand quantities zero and = >  =20 > >   > > > to delete all Inactive = items?  I am=20 opening a new store and have copied my >   >=20 >   > > > database to the new location, but I = don't=20 think it's necessary to keep 3 year >   > = >  =20 > > > old items on file
------=_NextPart_001_011D_01C7038B.F7287630--
------=_NextPart_000_011C_01C7038B.F7287630 Content-Type: image/gif; name="dot_clear.gif" Content-Transfer-Encoding: base64 Content-Location:
formatting link

------=_NextPart_000_011C_01C7038B.F7287630--
Reply to
CptSoft
This is a multi-part message in MIME format.
------=_NextPart_000_0135_01C7038E.4D708E90 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Greg,
You can read all of her posts here;
formatting link
art=3D0&
If you read them all, she has answered exactly one question in the = group. Everything else she asked for help, complained it didn't work, = etc. =20
She is/was a taker, not much of a giver!
Hmmm, I wonder if she charged her customer(s) for my/your/the group's = knowledge?=20
--=20
Jeff=20 Check Point Software
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D
You must be using Outlook Express or some other type of newsgroup reader = to see and download the file attachment(s). If you are not using a reader, = follow the link below to setup Outlook Express. Click on "Open with = newsreader" under the MS Retail Management System on the right.
formatting link
=3D=3D=3D
Hi Geokar,
WOw! your post knockdown every other post in this newgroup because of = the=20 Brightest idea that Jeff ??? gave... but seriously...both ideas will = give you=20 the same result but there are some adverse effect that JEFF wants to = strongly=20 point out which I agree...
Now, having an orphaned records would not hamper your day to day = operations=20 but rather if the item we have deleted have track of movement = before..meaning=20 if you want to get a historical data then problem will come.
So, if this is a stand alone and a shop data...I don't think it will = cause=20 you some trouble...
And for you Jeff, this a free world of interaction. If you want to = prove=20 your point do it in a subtle manner.Not to shout !!! ( I don't know = if your=20 the same Jeff that make a nasty comment to Roxanne , way back but I = think it=20 is not cool).
Good Day
Note* you will not get my VOTE for MVP ( for now !) chiao :)
idea to do this,
about the orphans here in these tables;
DO IT THROUGH THE RMS PROGRAM!!!!!!
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D
reader to
reader, follow
newsreader"
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D
inactive item=20
orphaned=20
inactive=3D1)
where=20
records out=20
ItemDynamic=20
inactive=3D1)
where=20
inactive=3D1)
inactive=3D1)
practical solution=20
times depends on=20
only delete the entries in the Item database, it will not delete any = Supplier's numbers or Alias' for starters.
it though the program!
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D
newsgroup reader to
a reader, follow
newsreader"
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D
message news: snipped-for-privacy@microsoft.com...
back up your=20
Hand quantities zero and=20
store and have copied my=20
necessary to keep 3 year=20
------=_NextPart_000_0135_01C7038E.4D708E90 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
=EF=BB=BF
Greg,   You can read all of her posts = here;  
formatting link
  If you read them all, she has answered = exactly one=20 question in the group.  Everything else she asked for help, = complained it=20 didn't work, etc.    She is/was a taker, not much of a = giver!     Hmmm, I wonder if she charged her customer(s) =
for my/your/the group's knowledge? -- Jeff Check Point Software   =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D   You must be using Outlook Express or some other type of newsgroup = reader=20 tosee and download the file attachment(s).  If you are not = using a=20 reader, followthe link below to setup Outlook Express.  Click = on "Open=20 with newsreader"under the MS Retail Management System on the = right.  
formatting link

"GregDxb" < snipped-for-privacy@discussions.mic= rosoft.com>=20
snipped-for-privacy@microsoft.com...Hi=20 Geokar,WOw! your post knockdown every other post in this = newgroup=20 because of the Brightest idea that Jeff ??? gave... but = seriously...both=20 ideas will give you the same result but there are some adverse = effect that=20 JEFF wants to strongly point out which I agree...Now, = having an=20 orphaned records would not hamper your day to day operations but = rather if=20 the item we have deleted have track of movement before..meaning if = you=20 want to get a historical data then problem will come.So, if = this is a=20 stand alone and a shop data...I don't think it will cause you some =
trouble...And for you Jeff, this a free world of interaction. = If you=20 want to prove your point do it in a subtle manner.Not  to = shout !!! (=20 I don't know if your the same Jeff that make a nasty comment = to =20 Roxanne , way back but I think it is not cool).Good=20 DayNote* you will not get my VOTE for MVP ( for now !) chiao=20 > Geokar, = GregDXB,=20 Jocelyn, Database, and whoever else has the stupid idea to do = this,>=20 > Yeah, that would delete some more of the orphaned records, = now how=20 about the orphans here in these tables;> >=20 ItemClassComponent> InventoryOffline>=20 InventoryTransferLog> Kit> LimitEntry>=20 PurchaseOrderEntry> Serial> TransactionHoldEntry> =
> and there maybe even more cuz I didn't look very hard. = ;-)>=20 > > = ************************************************>=20 > AGAIN, MY POINT IS THAT YOU SHOULDN'T BE DELETING ITEMS THIS=20 WAY.  DO IT THROUGH THE RMS PROGRAM!!!!!!> >=20 *************************************************> > -- = >=20 > Jeff > Check Point Software> >=20 = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D> > You=20 must be using Outlook Express or some other type of newsgroup reader=20 to> see and download the file attachment(s).  If you are = not using=20 a reader, follow> the link below to setup Outlook = Express.  Click=20 on "Open with newsreader"> under the MS Retail Management = System on the=20 right.> >
formatting link
=
= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D>=20 >   "Database Badger" <Database snipped-for-privacy@discussions.micro= soft.com>=20
snipped-for-privacy@microsoft.com...> &nbsp= ;=20 You're missing a table.  You need to clear the entry for the = inactive=20 item >   from the supplierlist table as well.  = Otherwise=20 you will have orphaned >   records in the = supplierlist=20 table.> >   Backup your database = first!>=20 >   delete from alias where itemid in (select id from = item=20 where inactive=3D1)>   delete from supplierlist where = itemid in=20 (select id from item where >  =20 inactive=3D1)>   delete from item where = inactive=3D1>=20 >   If you are in an HQ environment you will also = need to=20 delete the records out >   of the itemdynamic table = as well=20 as the 3 tables above.  The ItemDynamic >   = table is not=20 present in SO databases.>   So for HQ=20 databases:>   Backup Database!> = >  =20 delete from alias where itemid in (select id from item where=20 inactive=3D1)>   delete from supplierlist where = itemid in=20 (select id from item where >  =20 inactive=3D1)>   delete from itemdynamic where itemid = in=20 (select id from item where inactive=3D1)>   delete = from item=20 where inactive=3D1> > > > = >  =20 > >   > Good Point=20 jeff,>   > >   > I forgot the = alias=20 table :)>   > ---delete first the=20 alias--->   > delete from alias where itemid in = (select id=20 from item where inactive=3D1)>   > ---then delete = the=20 item--->   > delete from item where=20 inactive=3D1>   > >   > this = is just a=20 solution of what you want.. This is just a practical solution=20 >   > to delete n # of records rather than going = to your=20 records n times depends on >   > how many inactive = you=20 want to delete.>   > >   >=20 >   > "Jeff @ Check Point Software"=20 wrote:>   > >   > >=20 Geokar,>   > > >   > > = Again,=20 this routine will leave orphaned records.  This will only delete = the=20 entries in the Item database, it will not delete any Supplier's = numbers or=20 Alias' for starters.>   > > = >   >=20 > You're better off, if you _really_ want to delete them, to do it = though=20 the program!>   > > >   > = > --=20 >   > > >   > > Jeff=20 >   > > Check Point = Software>   >=20 > >   > >=20 = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D>   >=20 > >   > > You must be using Outlook Express = or some=20 other type of newsgroup reader to>   > > see = and=20 download the file attachment(s).  If you are not using a reader,=20 follow>   > > the link below to setup Outlook=20 Express.  Click on "Open with newsreader">   = > >=20 under the MS Retail Management System on the = right.>   >=20 > >   > >
formatting link
sp; =20 > >=20 = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D>   >=20 > >   > >   "GregDxb" < snipped-for-privacy@discussions.mic= rosoft.com>=20
snipped-for-privacy@microsoft.com...> &nbsp= ;=20 > >   hi,>   > > = >  =20 > >   normal procedure..before running any update or = delete=20 query, back up your >   > >  =20 data..>   > > >   >=20 >   this is the query to delete inactive=20 items>   > > >   > = >  =20 * I assume that you have updated/set your item into=20 inactive>   > >  =20 --------------------------------------------->   > =
>   delete from item where = inactive=3D1>   >=20 >  =20 --------------------------------------------->   > =
>   or if you want to delete 2000 year old data you can = use=20 this>   > >  =20 --------------------------------------------->   > =
>   delete from item where (item.lastupdated <=3D=20 CONVERT(DATETIME, '2001-01-01 >   > = >  =20 00:00:00', 102))>   > >  =20 -------------------------------------------->   > = >=20 >   > >   Run this query after backup = is made=20 successfully.>   > > >   >=20 >   Good Luck>   > > = >  =20 >   > >=20 >   > >   > = Great,>  =20 > >   > Now how about deleting inactive=20 items?>   > >   > = >  =20 >   >=20 >   > >   > >   > = > OK,=20 here is your delete qty query from SO Admin:>   >=20 >   > > >   > >   = >=20 > *Backup first*>   > >   > = >=20 >   > >   > > UPDATE Item SET = Quantity=20 =3D 0>   > >   > > = >  =20 > >   > > I would also back report this one in = HQ=20 Mgr.>   > >   > > = >  =20 > >   > > >   > = >  =20 > > >   > >   > >=20 >   > >   > > = >   >=20 >   >=20 >   > > >   > >   = >=20 > > Hi,>   > >   > > > =
>   > >   > > > Can someone = please=20 give me a SQL Query make all On Hand quantities zero and = >  =20 > >   > > > to delete all Inactive = items?  I am=20 opening a new store and have copied my >   >=20 >   > > > database to the new location, but I = don't=20 think it's necessary to keep 3 year >   > = >  =20 > > > old items on file
------=_NextPart_000_0135_01C7038E.4D708E90--
Reply to
CptSoft

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.