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 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/
