Ah. Your little bit of sample data didn't show that a particular UPC has multiple GTINs. Try this:
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" AND Right(T1.GTIN, 12) = Right(T2.GTIN, 12) 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:01 PM To: [email protected] Subject: [ms_access] Re: Something probably pretty basic? 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/
