Complex Query Question

[DeutchBose]

I have raw data in the following format


FieldA, Field B, Date

X, 1, 12/10/04
X, 1, 12/11/04
x, 2, 11/1/04
x, 2, 11/5/04
y, 3, 11/1/04
y, 3, 11/5/04

The combination of Field A and Field B identify a unique 'account number'...I need to query out the account numbers with only the most recent dates...how would I go about doing this...this should be simple, but I can't seem to figure it out...

 

[Joe4]

See the first part of my reply in this thread:
http://www.mrexcel.com/board2/viewtopic.php?t=126409

Basically, you want to Group on your first two fields, and take the Max of the Date field, similar to what I did in Query1 of the thread referenced above.

 

[Norie]

Can you not use a totals query with the date field as Max?

The SQL might look something like this:

SELECT [FieldA] & [FieldB] AS Fields, Max(Table1.Date) AS MaxOfDate
FROM Table1
GROUP BY [FieldA] & [FieldB];

 

[Joe4]

Can you not use a totals query with the date field as Max?

The SQL might look something like this:

SELECT [FieldA] & [FieldB] AS Fields, Max(Table1.Date) AS MaxOfDate
FROM Table1
GROUP BY [FieldA] & [FieldB];

That's pretty much what my thread tells them how to do, only with the steps on how to do it through the Query builder instead of using SQL directly.

 

[DeutchBose]

Thank you....works like a charm...so simple too...