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!

Qry:Grouping by dates

Status
Not open for further replies.

BCre8iv

Programmer
May 21, 2002
28
CA
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;
 
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")

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

Hope this helps.

bill
 
Thx Bill that worked out great however it drew it in on the first pass...
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top