Just the latest record 1

[nuVBer]

I have a table that has multiple records for each product. Each product has identical fields except for a sales field and the date of that sale. I just want the latest record.
The table looks like this:
ProductID date num_sold
1 7/21/01 7
1 7/31/01 10
2 8/10/01 6
2 8/21/01 5


I only want the records that have the latest date for each month. How do I design the query for this?

 

[tlbroadbent]

If you want to get the latest dated record per product ID and month, the following query should help you get started.

Select ProductID, [Date], Num_Sold
From table
Where [Date]=(Select max([Date]) From table where ProductID=a.ProductID And a.Month([Date])=Month([Date])) Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[nuVBer]

I didn't ask the question correctly. I need the latest record for each month. So in the original question, I would want to return the records 1;7/31/01/;10 and 2;8/21/01;5. I don't want to specify the date that I want the records for, because I want all of the records for each month for each year in the table. But, I just want the latest for each month.

 

[tlbroadbent]

The query I proposed would do what you want. It doesn't require you to enter any dates. It uses the data from the table to make the selection.

Clarification: [Date] represents the date column on your table. I enclosed 'date' in brackets because it is a keyword. Make sure you use the name of the column in your table. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[nuVBer]

Excuse my ignorance again, but doesn't the 'a' represent an alias for the table? Why am I getting a syntax error using the query you suggested. I get "incorrect syntax near '(' " on the a.Month([Date]) part of the query. I suspect there is a deeper problem here since the syntax is correct.

 

[tlbroadbent]

Sorry. Not your ignorance, my mistake! I caught the error when testing but still posted the incorrect syntax. I apologize for the inconvenience.

Select ProductID, [Date], Num_Sold
From table
Where [Date]=(Select max([Date]) From table where ProductID=a.ProductID And Month(a.[Date])=Month([Date]))

And yes, 'a' is an alias for the table. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[nuVBer]

That works!!!
Thank you so much!!