Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Latest date qry....

Status
Not open for further replies.

BCre8iv

Programmer
May 21, 2002
28
CA
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;
 
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;
 
I am still getting two records for each company....showing both dates that data was inputted....
 
It works if I sum total records in the OnSite field but I don't want that. I also tired DMax("[DateDepositMade]","BottlePaymentsSubform") 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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top