Thanks for the reply. In SQL, using a subquery as a derived table is a great solution to addressing the problem of aggregate functions. When building an aggregate query, every field (column) must participate in the aggregate function in some way (either as a GROUP BY column, or an aggregate function, SUM(), COUNT(), etc.). This makes returning additional information tricky.
The fix is to perform the aggregate function(s) in a subquery and pass the rows returned (as a derived table), to the outer query. The outer query can then return any fields desired.
For example, two tables in a database, one called Contact and one called ContactHistory. Contact contains all the static information about a person: ContactID, InsertDate, First Name, Last Name, Gender, Date of Birth. ContactHistory contains ContactID (foreign key to Contact table), InsertDate, Address, City, State, Zip, Phone, etc. There are many rows in ContactHistory for each row in Contact, as one person may have lived at many different addresses over time.
To return a person's name, gender, date of birth, and CURRENT address, city, state, etc only; use the MAX(InsertDate) in a subquery as a derived table, join this to the outer query. The outer query SELECTs the additional fields necessary.
The subquery to give the derived table:
SELECT Contact.ContactID, MAX(Contact.InsertDate) AS InsertDate FROM Contact GROUP BY Contact.ContactID
The whole query to return the current address for each contact in Contact table:
JOIN Racers c ON c.ContactID = ContactHistory.ContactID
JOIN ( --here's the subquery
SELECT h.ContactID, MAX(h.InsertDate) AS InsertDate FROM ContactHistory h GROUP BY h.ContactID
) AS DERIVED ON DERIVED.InsertDate = ContactHistory.InsertDate
Does MS Access's query engine support this derived table concept? If so, can you point out where I can learn the syntax?
Thanks in advance for your time and attention.
Last edited by mtbchef; 01-03-2005 at 11:00 PM.