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;
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;