SQL Syntax Error

[NoiCe]

Hi,

I'm trying to create a query that selects the most recent record from a table containing duplicate records based on Item Code.

I keep getting a syntax error:

SELECT DUPES.id, DUPES.[Item Code], DUPES.[Date Delivered], DUPES.[Item Description], DUPES.[Received Qty], DUPES.Vendor, DUPES.Comments, DUPES.[Doc #], DUPES.Weight, DUPES.PO, DUPES.[Date Posted to Inventory], DUPES.[Time of Posting], DUPES.[Date Delivered 2], DUPES.[Time of Delivery], DUPES.[0-4 Hours Posting Delay], DUPES.[4-8 Hours Posting Delay], DUPES.[8-24 Hours Posting Delay], DUPES.[Over 24 Hours Posting Delay]
FROM DUPES
WHERE ((DUPES.[Date Delivered]<=#3/31/2011#))
(SELECT MAX(DUPES.[Date Delivered])
FROM DUPES AS X
WHERE X.[Item Code] = DUPES.[Item Code]);

The table is something like this:

ITEM CODE|Date Received|Info1|Qty
Item A|03/01/2011|This is item A|300
Item A|03/28/2011|This is item A|200
Item A|02/27/2010|This is item A|3
Item B|03/03/2011|This is item B|200


Item B|03/27/2011|This is item B|2

The results should be:

Item A|03/28/2011|This is item A|200
Item B|03/27/2011|This is item B|2

I appreciate anyone's help!!! Thank you

[ssanfu]

In the WHERE clause, shouldn't there be an operator between the conditions??

WHERE ((DUPES.[Date Delivered]<=#3/31/2011#)) AND
(SELECT MAX(DUPES.[Date Delivered])
FROM DUPES AS X
WHERE X.[Item Code] = DUPES.[Item Code]);

or maybe

WHERE (SELECT MAX(DUPES.[Date Delivered])
FROM DUPES AS X
WHERE X.[Item Code] = DUPES.[Item Code] AND X.[Date Delivered]<=#3/31/2011#);

[NoiCe]

Both ways do not provide me the end result that I'm looking for. They basically still give me all of my original records back. I'm looking to obtain only the records that have the closest date to today without duplicates. De-duping on Item Code.

Any thoughts?

[Rixxe]

I'd look into Group By/Distinct, and something like:

WHERE [YourDateField] between Today() and [AnotherDateYouWant]

Or obviously you can use MAX, and TOP to get the top 50 records say with the max date.

Good Luck

[ssanfu]

Both ways do not provide me the end result that I'm looking for. <snip>
Any thoughts?

But you don't have the syntax error any more........


I tried to make a table to use in a query, but the sub query has [Date Delivered] that is not in the example data.

(Also, having spaces and special characters in the field names doesn't help)

[ajetrumpet]

you should start here:

select code, max(received)

from table

then a second could be this:

select all fields from table

where code in (select qry1.code from qry1) and 

received in (select qry1.received from qry1)

there's nothing wrong with using stacked queries.