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