Ray- Take a look at my last suggestion. You might need to compare using the Mid function if the check digits don't match. Mid(T1.GTIN, 3, 11) = Mid(T2.GTIN, 3, 11)
Or something similar. John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of raysgoogle Sent: Wednesday, December 14, 2005 11:34 PM To: [email protected] Subject: [ms_access] Something a little more difficult (was: Re: Something probably pretty basic? I'm considering that I might have to leave the left leading digits on the CaseCode and the checkdigits. How difficult would it be to make the same detection of similiarity using the two GTIN numbers instead of the UPCs? Or could I possibly still use the UPCs? Thanks. Ray. --- In [email protected], "raysgoogle" <[EMAIL PROTECTED]> wrote: > > Sigh. Nope. That's not right. When I FIXED the UPC codes, here's the > results. > > UPC ItemCode CaseCode Description > 65555500001 00655555000012 10655555000013 Cocktail Franks > 65555500002 00655555000025 10655555000025 Natural Casing Hotdogs > 65555500003 00655555000030 10655555000031 Skinless Hotdogs > > which IS apparently what I'm looking for. Thanks. Case closed. > > I'm going to bed. > > Ray. > > --- In [email protected], "raysgoogle" <[EMAIL PROTECTED]> wrote: > > > > Sorry, the other query is from the actual table with their specific > > names. I built something easier to look at that is fairly close to > > what I have (with a few hundred less records). Ok, assume a table > > like below: > > > > UPC GTIN Description > > 655555000012 00655555000012 Cocktail Franks > > 655555000025 00655555000025 Natural Casing Hotdogs > > 655555000030 00655555000030 Skinless Hotdogs > > 10655555000013 10655555000013 Cocktail Franks > > 10655555000025 10655555000025 Natural Casing Hotdogs > > 10655555000031 10655555000031 Skinless Hotdogs > > > > What I need to accomplish is create a table with the following > fields: > > > > UPC ItemCode CaseCode Description > > 65555500001 00655555000012 10655555000013 Cocktail Franks > > ... > > > > where the GTINs with preceding "00" are the ItemCode and GTINs with > > preceding "10" are the CaseCode. > > > > When I run the query below, it gives me an empty table. > > > > SELECT T1.UPC, T1.GTIN As ItemCode, T2.GTIN As CaseCode, T1.Desc As > > Description > > FROM tblT1 As T1 INNER JOIN tblT1 As T2 > > ON T1.UPC = T2.UPC > > WHERE T1.GTIN <> T2.GTIN > > AND Left(T2.GTIN, 2) = "10"; > > > > Yields: > > UPC ItemCode CaseCode Description > > > > I thought it may have to do with the messy UPC codes so I cleaned > > them up manually. The results are: > > > > UPC ItemCode CaseCode Description > > 6555550000 10655555000031 10655555000013 Skinless Hotdogs > > 6555550000 10655555000025 10655555000013 Natural Casing Hotdogs > > 6555550000 00655555000030 10655555000013 Skinless Hotdogs > > 6555550000 00655555000025 10655555000013 Natural Casing Hotdogs > > 6555550000 00655555000012 10655555000013 Cocktail Franks > > 6555550000 10655555000031 10655555000025 Skinless Hotdogs > > 6555550000 10655555000013 10655555000025 Cocktail Franks > > 6555550000 00655555000030 10655555000025 Skinless Hotdogs > > 6555550000 00655555000025 10655555000025 Natural Casing Hotdogs > > 6555550000 00655555000012 10655555000025 Cocktail Franks > > 6555550000 10655555000025 10655555000031 Natural Casing Hotdogs > > 6555550000 10655555000013 10655555000031 Cocktail Franks > > 6555550000 00655555000030 10655555000031 Skinless Hotdogs > > 6555550000 00655555000025 10655555000031 Natural Casing Hotdogs > > 6555550000 00655555000012 10655555000031 Cocktail Franks > > > > which isn't what I was looking for. > > > > I hope this is clear enough. If not, tell me what I can do. This is > > getting frustrating. > > > > Thanks for your help. > > > > Ray. > > > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > > > > > Ray- > > > > > > I'm lost. What are the real field names? What is EANUCC Code, > and > > why are > > > you matching on 11 characters? Post some more sample data with > the > > real > > > table and field names. > > > > > > John Viescas, author > > > "Building Microsoft Access Applications" > > > "Microsoft Office Access 2003 Inside Out" > > > "Running Microsoft Access 2000" > > > "SQL Queries for Mere Mortals" > > > http://www.viescas.com/ > > > > > > > > > -----Original Message----- > > > From: [email protected] > [mailto:[EMAIL PROTECTED] > > On Behalf > > > Of raysgoogle > > > Sent: Wednesday, December 14, 2005 10:05 AM > > > To: [email protected] > > > Subject: [ms_access] Re: Something probably pretty basic? > > > > > > I've been beating my head on this for awhile. THanks for the > code, > > > it got me in the right frame of mind, but Access is really > messing > > > with that. > > > > > > I used the code you provided and adapted it to the real names. > > > Access automatically changes it around. What I usually end up > with > > > is either no data, or all the fields have the "10" number in all > > > columns with multiple iterations of those lines. > > > > > > I did a plain Access formula (which I'm getting pretty > comfortable > > > with thanks again) and the SQL code: > > > > > > SELECT tbl_tr2_GTIN.[EANUCC Code] AS UPC, tbl_tr2_GTIN.[Global > > Trade > > > Item Number] AS [Item GTIN Code], tbl_tr2_GTIN_1.[Global Trade > Item > > > Number] AS [Case GTIN Code (purchase)], tbl_tr2_GTIN.[Brand > Name], > > > tbl_tr2_GTIN.[Long Description] > > > > > > FROM tbl_tr2_GTIN, tbl_tr2_GTIN AS tbl_tr2_GTIN_1 > > > > > > WHERE (Left([tbl_tr2_GTIN].[EANUCC Code],11)= Left > ([tbl_tr2_GTIN_1]. > > > [EANUCC Code],11)) and (Left([tbl_tr2_GTIN_1].[Global Trade Item > > > Number],2)="10"); > > > > > > gives me the repetetive 10* number in the "UPC" and Item GTIN > Code" > > > columns, all columns and bogus numbers in the "Case GTIN Code" > > > column. > > > > > > I guess I'm trying to figure out how to apply the WHERE statement > > to > > > only the "Case GTIN Code" column without affecting the others. > > > > > > Any ideas? > > > > > > Thanks, Ray. > > > > > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> > wrote: > > > > > > > > Oh. Join the table to itself: > > > > > > > > SELECT T1.UPC, T1.gtin As ItemCode, T2.gtin As CaseCode, > T1.Desc > > As > > > > Description > > > > FROM MyTable As T1 INNER JOIN MyTable As T2 > > > > ON T1.UPC = T2.UPC > > > > WHERE T1.gtin <> T2.gtin > > > > AND Left(T2.gtin, 2) = "10" > > > > > > > > John Viescas, author > > > > "Building Microsoft Access Applications" > > > > "Microsoft Office Access 2003 Inside Out" > > > > "Running Microsoft Access 2000" > > > > "SQL Queries for Mere Mortals" > > > > http://www.viescas.com/ > > > > > > > > > > > > -----Original Message----- > > > > From: [email protected] > > [mailto:[EMAIL PROTECTED] > > > On Behalf > > > > Of raysgoogle > > > > Sent: Friday, December 02, 2005 4:13 PM > > > > To: [email protected] > > > > Subject: [ms_access] Re: Something probably pretty basic? > > > > > > > > Sorry. Thanks for taking a look. The list (table) is vertical, > > and > > > > the end result (either table or spreadsheet) has to be > > horizontal. > > > > It's just something that I can't seem to figure out. > > > > > > > > UPC gtin Desc > > > > 05555 0005555 Hotdogs (item) > > > > 05555 1005555 Hotdogs (case) > > > > 02345 0002345 Hamburgers (item) > > > > 02345 1002345 Hamburgers (case) > > > > > > > > To: > > > > > > > > UPC Item Code Case Code Description > > > > 05555 0005555 1005555 Hot dogs > > > > 02345 0002345 1002345 Hamburgers > > > > > > > > > > > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> > > > wrote: > > > > > > > > > > Ray- > > > > > > > > > > Maybe I'm missing something, but the "From" data looks > exactly > > > > like the "To" > > > > > data, so I don't understand your problem. > > > > > > > > > > John Viescas, author > > > > > "Building Microsoft Access Applications" > > > > > "Microsoft Office Access 2003 Inside Out" > > > > > "Running Microsoft Access 2000" > > > > > "SQL Queries for Mere Mortals" > > > > > http://www.viescas.com/ > > > > > > > > > > > > > > > -----Original Message----- > > > > > From: [email protected] > > > [mailto:[EMAIL PROTECTED] > > > > On Behalf > > > > > Of raysgoogle > > > > > Sent: Friday, December 02, 2005 6:51 AM > > > > > To: [email protected] > > > > > Subject: [ms_access] Something probably pretty basic? > > > > > > > > > > I have a table with sequences of numbers that are essentially > > > the > > > > > same except for the leading digits. The idea is a "10" > > indicates > > > a > > > > > case and the "00" indicates an item number. I have to create > an > > > > XLS > > > > > file with the two numbers in individual columns instead of > rows > > > > and > > > > > tack on the descripiton. Essentially like this: > > > > > > > > > > UPC Item Case Desc > > > > > 05555 0005555 1005555 Hotdogs > > > > > 02345 0002345 1002345 Hamburgers > > > > > > > > > > To: > > > > > > > > > > UPC Item Code Case Code Description > > > > > 05555 0005555 1005555 Hot dogs > > > > > 02345 0002345 1002345 Hamburgers > > > > > > > > > > and so forth. > > > > > > > > > > For some reason this is just baffling me. I know it's > probably > > > > > really simple, but I just don't get it. > > > > > > > > > > Can anyone help? > > > > > > > > > > Thanks. > > > > > > > > > > Ray. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Yahoo! Groups Links > > > > > > Yahoo! Groups Links ------------------------ Yahoo! Groups Sponsor --------------------~--> Get Bzzzy! (real tools to help you find a job). Welcome to the Sweet Life. http://us.click.yahoo.com/KIlPFB/vlQLAA/TtwFAA/q7folB/TM --------------------------------------------------------------------~-> Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
