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 Sponsor --------------------~--> Most low income homes are not online. Make a difference this holiday season! http://us.click.yahoo.com/5UeCyC/BWHMAA/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/
