Qry:Grouping by dates

[BCre8iv]

Hi there,

Below is a query that I have in place. Works great except that I need it to only select the record for the last date of entry in the DateDepositMade. Any help would be great! Thx


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

 

[MrBillSC]

Bcre8iv,

The only way I know of to select the records with the highest date value is with two passes. In the first pass create a new field called something like "LastDate" and use the Dmax function to set its value:

LastDate: DMax("[Entry_Date]","TblName&quot

In the second Pass select the records where Entry_Date = LastDate.

Hope this helps.

bill

 

[BCre8iv]

Thx Bill that worked out great however it drew it in on the first pass...