I need to run a query to find all items that DO NOT have a barcodeformat = 7 OR an aliasname in the alias table. Any help would be appreciated - think I'm getting my joins mixed-up...
Thanks Dan
I need to run a query to find all items that DO NOT have a barcodeformat = 7 OR an aliasname in the alias table. Any help would be appreciated - think I'm getting my joins mixed-up...
Thanks Dan
Hi Danny,
Go to SO Administrator, then go to File and Connect the DB, then go to qUERY AND CLICK ON NEW, now type the following query
SELECT * FROM ITEM WHERE BARCODEFORMAT 7 -- list you all items
Now suppose you wanted to update those which has no 7 so run the update query.
update item set barcodeformat=7 where barcodeformat 7
Rate this if you see it is working.
Akber
"Danny" wrote:
Hi Akber
I want to update barcodeformat for items with no barcodeformat (just using barcodeformat = 7) and items with no alias. Some background - we have our own numbering system for ILC - this year we purchased a scanner - so during invenotry time we entered all the barcode's as Aliases. Since one department had no items with barcodes, I was able to update those barcodeformat field very easily with Update barcodeformat = 7 from item with departmentid = 1 - now I would like to do all other departments but NOT the items with alias (which is the manufacturers companies barcode).
Thanks Danny
Hi Akber
If I do the following sql :
select i.itemlookupcode, i.barcodeformat, i.description, a.alias from item i LEFT OUTER JOIN alias a ON i.id = a.itemid
I get all my Items - what I want is ONLY Items with barcodeformat7 or a.alias = '' (blank)
Danny
Hi Danny - you can try using a subquery as well...
select i.itemlookupcode, i.barcodeformat, i.description, a.alias from item i left join alias a on i.id = a.itemid where i.barcodeformat 7 or i.id not in (select itemid from alias)
My understanding is that the barcode format setting dictates what barcode format will print on a barcode tag you print using the label wizard -- if your items come with a barcode and you've added the barcode as an alias, the "barcode format" doesn't come into play -- its primarily your scanner being programmed to recognize the barcode format of the "alias" tag...most scanners today will work fine - you can test the barcode by scanning it into a notepad and comparing what you see in the notepad with what you assigned as the alias
- if the numbers match, you're good to go, no real need to re-assign barcode formats > Hi Akber
hi do this: select i.itemlookupcode, i.barcodeformat, i.description, a.alias from item i LEFT OUTER JOIN alias a ON i.id = a.itemid Where i.barcodeformat7 or a.alias is null
"Danny" wrote:
Akber/Convoluted
Worked great - thanks
1) Now I have to turn on the barcodeformat type to 7 for all my items where I don't have an alias... 2) Need report to give me value of inventory at start as of Jan 1, 2007 and at end of Dec., 31, 2007.Danny PS. Rated both replies - thanks guys
Hi Danny - search the newsgroup for the report - I don't have it available but I recall seeing this request more than a few times... you can use this query to set your barcodeformat to 7 for items not in the alias table - BACKUP FIRST...
update item set barcodeformat = 7 where id not in (select itemid from alias)
happy friday....
"Danny" wrote:
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.