Stll Have One-To-Many Query Problem 1

[gimmyd]

The following works but only returns to me the DateReading field part of the record.

SELECT tblCustomers.CustAcctNum, tblReadings.AccountNum, Max(tblReadings.DateReading) AS MaxOfDateReading
FROM tblCustomers LEFT JOIN tblReadings ON tblCustomers.CustAcctNum = tblReadings.AccountNum
GROUP BY tblCustomers.CustAcctNum, tblReadings.AccountNum
ORDER BY tblReadings.AccountNum DESC;

When I add another field to query from the many table I get multiple dates for the same AccountNum.

SELECT tblCustomers.CustAcctNum, tblReadings.AccountNum, Max(tblReadings.DateReading) AS MaxOfDateReading, tblReadings.Amount
FROM tblCustomers LEFT JOIN tblReadings ON tblCustomers.CustAcctNum = tblReadings.AccountNum
GROUP BY tblCustomers.CustAcctNum, tblReadings.AccountNum, tblReadings.Amount
ORDER BY tblReadings.AccountNum DESC;

How do I return the entire record in the Many table based on the record selected by Max DateReading?

 

[dalchri]

The only way that I know how to do this is to use a subquery. First, it appears that you don't actually need tblCustomers. I would try the following...

This query will give you a list of the most recent readings by account number:
[tt]
SELECT AccountNum, MAX(DateReading) AS MaxOfDateReading
FROM tblReadings
GROUP BY AccountNum
[/tt]
Now, you can join this subquery back onto the original table to obtain the most recent record by account number:
[tt]
SELECT tblReadings.AccountNum, MaxOfDateReading, Amount, ... {as many fields as you want)
FROM tblReadings INNER JOIN (
SELECT AccountNum, MAX(DateReading) AS MaxOfDateReading
FROM tblReadings
GROUP BY AccountNum
) AS MostRecent ON
tblReadings.AccountNum=MostRecent.AccountNum And
tblReadings.DateReading=MostRecent.MaxOfDateReading
[/tt]