Missing Operator

[Lori2836 via AccessMonster.com]

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

 

[Lori2836 via AccessMonster.com]

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

 

[Guest]

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];


--
KARL DEWEY
Build a little - Test a little

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

 

[Lori2836 via AccessMonster.com]

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 #],
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];

Sorry - I'm getting a Syntax error.............

[quoted text clipped - 15 lines]

 

[Guest]

Try taking out the hard return in -
FROM [18 - Machining
Sort]


--
KARL DEWEY
Build a little - Test a little

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 #],
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];

Sorry - I'm getting a Syntax error.............

[quoted text clipped - 15 lines]

Thanks,
Lori

 

[Lori2836 via AccessMonster.com]

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
Sort]

Thanks Karl...........still getting a syntax error........it doesn't like
this section

[quoted text clipped - 21 lines]

 

[Guest]

Post sample data and what you expect from the query of that data.
--
KARL 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
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
Sort]

Thanks Karl...........still getting a syntax error........it doesn't like
this section

[quoted text clipped - 21 lines]

Thanks,
Lori

 

[Lori2836 via AccessMonster.com]

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]

 

[Guest]

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];

--
KARL DEWEY
Build a little - Test a little

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