Totals Query Question

[cascot]

In Access 2000, if I have a Totals type query of the form

SELECT Table1.ObjectId, Max(Table2.FinishDate) AS PrevFinishDate
FROM Table1 INNER JOIN Table2 ON Table1.ObjectId = Table2.ObjectId
GROUP BY Table1.ObjectId;

Let's say the above query outputs the following results:

ObjectId PrevFinishDate
99 17/Jan/2003
105 24/Jan/2003
106 02/Feb/2003
247 09/Feb/2003

If Table2 (from which PrevFinishDate is obtained) also contains a field called "StartDate". How can I obtain the StartDate for each of the instances returned by the query above?

Can it be achieved within the same query?

 

[psemianonymous]

This works assuming you have the same startDate for every FinishDate -- because if you don't, trying to display only one startDate would be meaningless.

SELECT Table2.StartDate, Table1.ObjectId, Max(Table2.FinishDate) AS PrevFinishDate
FROM Table1 INNER JOIN Table2 ON Table1.ObjectId = Table2.ObjectId
GROUP BY Table1.ObjectId, Table2.StartDate ; --
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

 

[cascot]

foolio12,

I'm not trying to disply oinly one start date. What I want is to effectively say, show me the most recent FinishDate for each ObjectId, and from the same record (ie. the one with the highest FinishDate for theObjectId), also display the StartDate from that record.

So if the result of my original query is:

ObjectId PrevFinishDate Table2Id
99 17/Jan/2003 67
105 24/Jan/2003 45
106 02/Feb/2003 39
247 09/Feb/2003 78

I want to know what the StartDate is of the same records from Table2, namely those with Table2Id's of 67, 45, 39 and 78. Each of which will very likely be different.

 

[cascot]

I should have explained, that I know Table2Id doesn't form part of the results of the query, but I was meaning that, for example, the record from Table2 that contained the maximum FinishDate for ObjectId 99 was the record in table2 with an Id of 67.