Joined tables(one-to-many) query problem 1

[gimmyd]

I have a table with Customer information keyed by CustAcctNum field.The Many table has account activity associated to CustAcctNum. I want to query just the latest by date(Max)from the many table and retrieve all fields for billing purposes and I want the customer information associated with that account. I'm okay until I use more than one field from the many table.

Jim

 

[Bry12345]

It's a little tough without seeing your code, but here is a generic query that retrieves all records from the 'one' table and its' matching 'latest' 'many' table record:

SELECT tblOne.KeyField, tblMany.KeyField, Max(tblMany.DateField) AS MaxOfDateField
FROM tblOne LEFT JOIN tblMany ON tblOne.KeyField=tblMany.KeyField
GROUP BY tblOne.KeyField, tblMany.KeyField
ORDER BY tblMany.KeyField DESC;
- - - -

Bry

 

[gimmyd]

This works fine until I include the Reading field from the Many table. I want just those readings associated with the Max date but I get all readings regardless of date. It's like the Max Date is being overridden.

Jim D.

 

[gimmyd]

Here is the code that returns too many records:

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

Appreciate the help,
Jim D.

 

[Bry12345]

Check your grouping . . in my example I only grouped on one field . .this forces the query to take the max of each 'group', which in this case is each record. This may be causing the problem.

You may wish to try using the QBE grid to hack out the SQL . . 'Group By' should be in the Total row for the primary and foreign key . . 'Max' should be in the Total row for DateReading.

Also, make sure you have the appropriate one-to-many join between the two tables. - - - -

Bry

 

[gimmyd]

Thanks for your help Bry. I just now "hacked it out. I used your query (Query1) and joined the AccountNum and MaxOfDateField to the tblCustomers/tblReadings JOIN's respective fields. I now have access to all info needed for those records with Max DateReading.

SELECT tblCustomers.LastName1, tblCustomers.FirstName1, tblCustomers.LastName2, tblCustomers.FirstName2, tblCustomers.Address, tblCustomers.City, tblCustomers.State, tblCustomers.Zip, tblCustomers.Telephone1, tblCustomers.Telephone2, tblReadings.ReadingID, tblReadings.AccountNum, tblReadings.Reading, tblReadings.Amount, tblReadings.AmountPaid, tblReadings.DatePaid, tblReadings.Description, tblReadings.ExtraAmount, tblReadings.ExtraAmountPaid, tblReadings.ExtraDescription, tblReadings.DateReading
FROM (tblCustomers INNER JOIN tblReadings ON tblCustomers.CustAcctNum = tblReadings.AccountNum) INNER JOIN Query3 ON (tblReadings.DateReading = Query3.MaxOfDateField) AND (tblReadings.AccountNum = Query3.AccountNum);