query criteria

[smiley0q0]

ok, i have a table that has several dates in it for several different months, what do i need to put in my criteria to pull the largest date for each month of the year? for example...

1/28/01
1/31/01
2/15/01
2/25/01
2/28/01
3/02/01
3/31/01
4/05/01
4/28/01

i want to pull...
1/31/01
2/28/01
3/31/01
4/28/01

does that make sense? any help would be appreciated.

Thanks,
Smiley :

 

[LonnieJohnson]

Hi smiley0q0,

In your query go to the toolbar and click the totals button. It's a Greek symbol that looks like an E. In the first column of your grid, instead of putting a field there, put this expression:

DatePart("m",[YourDate])

Where YourDate is the name of the date field in question.
Be sure "Group By" is on the total line for this column. This will group by months.

Drag the same date field from your table into the second column on the grid. On the total line for this column select "Max" from the dropdown list. This will give you the maximum date for each month that is grouped.

Lastly drag any other fields you may need to view into the query after this.

Good luck, ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[smiley0q0]

ok, that works great for the dates, but when i try to put my other data in it brings up all the dates again. in my table i have...

Date Client BeginBal EndBal

and i only want the data for the max date of every month, i don't want to sum or average the begin or end bal, i just want the data that is in the same row as the date. any ideas?

Thanks,
Smiley

 

[LonnieJohnson]

Sorry about that. I did not try added the additional fields. You are correct. The best I could come up with right now is to create a second query using the first query (with only the first two columns for the date) and the table. Join the tables by Max date from the first query and the date in question from the table. Then you will be able to select the other fields.

I apologize again for not stepping completely through the query before passing that information. ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[smiley0q0]

that's ok. your advice helped me a ton. and your second suggestion works great!!! Thank you for your help.

Smiley.