Group By dates

[Laura1 via AccessMonster.com]

Using MSACCESS

I know this has to be simple and I am just missing it.

In my database I have:

Customer number 1234
GL Account rg2345
Date Changed 2/1/2005 11:08pm

Customer number 1234
GL Account rg2333
Date Changed 2/1/2007 1:08pm

Customer number 1234
GL Account bvb4565
Date Changed 2/11/2006 1:00pm

The most recent GL number for this customer (which would be the 2007 date)
When I do group by and last on the date I get all three bc they are all
unique. How do I just get the latest?

 

[John Spencer]

Step one:

Create a query that groups by Customer Number and gets the MAX of Date
Changed.

Step Two:
Create a query using the original table and the query above.
Join on Customer Number and the Date fields to return the most recent

Or use a correlated subquery in the WHERE clause.

If your Field and Table names consist of only Letters, Numbers, and the
underscore character, you can build the whole thing as one query with a
subquery in the FROM clause. If you don't understand what I said, but
your field and table names meet the criteria = post back with the names
and someone can build a sample query for you that you can cut and paste
into the query SQL window.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Laura1 via AccessMonster.com]

I built two queries and it worked great!! Thank you

Step one:

Create a query that groups by Customer Number and gets the MAX of Date
Changed.

Step Two:
Create a query using the original table and the query above.
Join on Customer Number and the Date fields to return the most recent

Or use a correlated subquery in the WHERE clause.

If your Field and Table names consist of only Letters, Numbers, and the
underscore character, you can build the whole thing as one query with a
subquery in the FROM clause. If you don't understand what I said, but
your field and table names meet the criteria = post back with the names
and someone can build a sample query for you that you can cut and paste
into the query SQL window.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Using MSACCESS

[quoted text clipped - 17 lines]

When I do group by and last on the date I get all three bc they are all
unique. How do I just get the latest?