 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / March 2007Missing Operator
Trying to follow a suggestion from someone where I want to show just the first record for an ITem ID using the first Start Date and I keep getting an operator error message.......can you please tell me what might be wrong? This comes from an old thread in here back in 2003....... SELECT DISTINCTROW [Item ID], [Item Description], [MFG Order #], [Order Status], [Order Qty], [Balance Due], [Opern Description], [Work Center], [Start Date], [Due Date], [Fiscal Year], [Fiscal Week] FROM [18 - Machining Sort] T1 WHERE [Start Date] = (SELECT MAX(Start Date) FROM [18 - Machining Sort] T2 WHERE T2.[Item ID] = T1.[Item ID] AND T2.[Due Date] = T1.[Due Date]) ORDER BY Item ID, Due Date Thanks, Lori Sorry - I'm getting a Syntax error............. >Trying to follow a suggestion from someone where I want to show just the >first record for an ITem ID using the first Start Date and I keep getting an [quoted text clipped - 13 lines] >Thanks, >Lori Try this -- SELECT DISTINCTROW T1.[Item ID], T1.[Item Description], T1.[MFG Order #], T1.[Order Status], T1.[Order Qty], T1.[Balance Due], T1.[Opern Description], T1.[Work Center], T1.[Start Date], T1.[Due Date], T1.[Fiscal Year], T1.[Fiscal Week] FROM [18 - Machining Sort] AS T1 WHERE (((T1.[Start Date])=(SELECT MAX([Start Date]) FROM [18 - Machining Sort] T2 WHERE T2.[Item ID] = T1.[Item ID] AND T2.[Due Date] = T1.[Due Date]))) ORDER BY T1.[Item ID], T1.[Due Date];  SignatureKARL DEWEY Build a little - Test a little > Sorry - I'm getting a Syntax error............. > [quoted text clipped - 15 lines] > >Thanks, > >Lori Thanks Karl...........still getting a syntax error........it doesn't like this section SELECT MAX([Start Date]) FROM [18 - Machining Sort] T2 WHERE T2.[Item ID] = T1.[Item ID] AND T2.[Due Date] = T1.[Due Date]) Anymore ideas? >Try this -- >SELECT DISTINCTROW T1.[Item ID], T1.[Item Description], T1.[MFG Order #], [quoted text clipped - 12 lines] >> >Thanks, >> >Lori Try taking out the hard return in - FROM [18 - Machining Sort] SignatureKARL DEWEY Build a little - Test a little > Thanks Karl...........still getting a syntax error........it doesn't like > this section [quoted text clipped - 21 lines] > >> >Thanks, > >> >Lori It worked! Thanks........but it didn't do what it was supposed to. Just give me the first Item ID record of many..........I'm looking to have the query return only the first Item ID with the earliest start date and/or due date......I may have 4 of the same Item ID with different dates........only interested in seeing 1 of the 4 records.......... >Try taking out the hard return in - >FROM [18 - Machining [quoted text clipped - 5 lines] >> >> >Thanks, >> >> >Lori Post sample data and what you expect from the query of that data. SignatureKARL DEWEY Build a little - Test a little > It worked! Thanks........but it didn't do what it was supposed to. Just > give me the first Item ID record of many..........I'm looking to have the [quoted text clipped - 11 lines] > >> >> >Thanks, > >> >> >Lori Item ID Item Desc Mfg # Start Date Due Date 8221043 LHB06EPG SUB ASSY 0635210 5/8/2007 5/10/2007 8221043 LHB06EPG SUB ASSY 0635210 5/10/2007 5/12/2007 8221043 LHB06EPG SUB ASSY 0635210 6/15/2007 6/18/2007 This is the type of data my query is returning, but all they want to see is the very first set of records, because as long as they see the one, the others have no meaning. I just can't seem to filter out all but the one record. Does this make sense? >Post sample data and what you expect from the query of that data. >> It worked! Thanks........but it didn't do what it was supposed to. Just >> give me the first Item ID record of many..........I'm looking to have the >[quoted text clipped - 11 lines] >> >> >> >Thanks, >> >> >> >Lori Try this -- SELECT DISTINCTROW T1.[Item ID], T1.[Item Description], T1.[MFG Order #], T1.[Order Status], T1.[Order Qty], T1.[Balance Due], T1.[Opern Description], T1.[Work Center], T1.[Start Date], T1.[Due Date], T1.[Fiscal Year], T1.[Fiscal Week] FROM [18 - Machining Sort] AS T1 WHERE ((T1.[Start Date])=(SELECT Min([Start Date]) FROM [18 - Machining Sort] T2 WHERE T2.[Item ID] = T1.[Item ID] )) ORDER BY T1.[Item ID], T1.[Due Date]; SignatureKARL DEWEY Build a little - Test a little > Item ID Item Desc Mfg # > Start Date Due Date [quoted text clipped - 16 lines] > >> >> >> >Thanks, > >> >> >> >Lori |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.