I need some help on this: I want th

[nuVBer]

I need some help on this: I want the records that have the greatest dates per month regardless of the other data in the columns.

Table:

Comp_sk Prod_dt Ddown_rsn Ddown_hrs
1 2/2/2001 rr 15
2 1/5/2001 er 48
2 2/5/2001 ik 25
2 2/18/2001 ip 36
3 1/13/2001 rr 25
3 2/15/2001 ee 25
3 2/28/2001 ew 22
3 3/5/25/2001 ww 55
3 3/31/2001 ee 44

So, I need a query that would return:

1 2/2/2001 rr 15
2 1/5/2001 er 48
2 2/18/2001 ip 36
3 1/13/2001 rr 25
3 2/28/2001 ew 22
3 3/31/2001 ee 44

The results need to include the greatest date per month.

 

[Chance1234]

this might help, my date fieldd was called "Swat team date" i created one query with teh below SQL

SELECT DISTINCT DatePart("m",[Swat Team Date]) AS Monthp, Max(DatePart("d",[Swat Team Date])) AS dayp, [dayp] & "/" & [monthp] & "/2001" AS DAtetoFind
FROM [tblmasterdata(x)]
GROUP BY DatePart("m",[Swat Team Date]);

i then created another query with teh data i wanted to find and linked the datetofind field query to the date field in that query (note conversion function needed as well)
probably not making much sense!

take teh above SQL, rename Swat Team Date with wahteever date field is in your DB and change the tabel referecne. that will bring back the dates which are the greatest in each month.

create another query with your data and the above linked and hey presto there is your answer ( conversion for field type look up cdate() function)