Latest date qry....

[BCre8iv]

I am trying to get a query to return the record with the latest date: The user will input many records for one company but the only record that is needed in the report is the last date data is entered. I thought I had it figured out but I can't get it to give me just one record per customer. Any help with this would be great...sort of pressed for time. Below is where I am at.
thx in advanced. Tina


SELECT Customers.CompanyName, BottlePaymentsTable.BottleDeposit, BottlePaymentsTable.AmountofDeposit, BottlePaymentsSubformTable.DateDepositMade, BottlePaymentsSubformTable.Previous, BottlePaymentsSubformTable.Delivered, BottlePaymentsSubformTable.Returned, [Previous]+[Delivered]-[Returned] AS OnSite
FROM (Customers INNER JOIN BottlePaymentsTable ON Customers.CustomerID = BottlePaymentsTable.CustomerID) INNER JOIN BottlePaymentsSubformTable ON (BottlePaymentsTable.BottlePaymentsID = BottlePaymentsSubformTable.BottlePaymentsID) AND (Customers.CustomerID = BottlePaymentsSubformTable.CustomerID)
GROUP BY Customers.CompanyName, BottlePaymentsTable.BottleDeposit, BottlePaymentsTable.AmountofDeposit, BottlePaymentsSubformTable.DateDepositMade, BottlePaymentsSubformTable.Previous, BottlePaymentsSubformTable.Delivered, BottlePaymentsSubformTable.Returned;

 

[cmmrfrds]

Remove date from group by.

SELECT Customers.CompanyName, BottlePaymentsTable.BottleDeposit, BottlePaymentsTable.AmountofDeposit,
max(BottlePaymentsSubformTable.DateDepositMade),
BottlePaymentsSubformTable.Previous, BottlePaymentsSubformTable.Delivered, BottlePaymentsSubformTable.Returned, [Previous]+[Delivered]-[Returned] AS OnSite
FROM (Customers INNER JOIN BottlePaymentsTable ON Customers.CustomerID = BottlePaymentsTable.CustomerID) INNER JOIN BottlePaymentsSubformTable ON (BottlePaymentsTable.BottlePaymentsID = BottlePaymentsSubformTable.BottlePaymentsID) AND (Customers.CustomerID = BottlePaymentsSubformTable.CustomerID)
GROUP BY Customers.CompanyName, BottlePaymentsTable.BottleDeposit, BottlePaymentsTable.AmountofDeposit, BottlePaymentsSubformTable.Previous, BottlePaymentsSubformTable.Delivered, BottlePaymentsSubformTable.Returned;

 

[BCre8iv]

I am still getting two records for each company....showing both dates that data was inputted....

 

[BCre8iv]

It works if I sum total records in the OnSite field but I don't want that. I also tired DMax("[DateDepositMade]","BottlePaymentsSubform&quot which worked great as long as I was summing the records in the OnSite field. Unfortunately for me that isn't what is wanted. Any suggestions...Thx