Help on a query based on the latest date

[Joe Cilinceon]

Hi

This is my first time posting here and need some help, please. I have a
table (Ledger) that stores payment information on units/tenants at a storage
facility. The table has a transaction number (autonumber) on every entry as
well as the unit number, tenant ID # (multiple possible per table) as well
as all the information on the payment made such as date, amount, method etc.
I also have a field for PaidThru (date) which gives the final date the rent
will cover. Hence, there will be several records for each unit as well as
different tenants for units time goes by (they move out and other move in
constantly).

Now here is the problem I want a query that will only show the last paidthru
date based on a given unit/tenant combination. Any help would be greatly
appreciated.

 

[PC Datasheet]

Create a query that includes only the fields Unit, Tenant and PaidThruDate.
While in query design view, click on the Sigma (looks like E) button on the
toolbar at the top of the screen. Then under PaidThruDate, change Group By
to Max. This query should give you what you want.

 

[Joe Cilinceon]

Thanks that worked.

--

Joe Cilinceon

Create a query that includes only the fields Unit, Tenant and PaidThruDate.
While in query design view, click on the Sigma (looks like E) button on the
toolbar at the top of the screen. Then under PaidThruDate, change Group By
to Max. This query should give you what you want.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

 

[Joe Cilinceon]

Well I may have spoke too soon. It works with the Unit, Tenant and PaidThru
fields but will not work when adding the rest of the record in the table.
For example if I add the DatePaid field to the query in now duplicates a
unit/tenant combo. I'm looking to get the whole record to show as I'll be
working with all that data once it is queried. I hope that was a clear
explanation as I'm starting to get confused myself. g

 

[PC Datasheet]

Add TenantID to the query. Create a second query based on this query and the
tables you need to get all the fields you want. Join TenantID in the
original query to TenantID in your tenant table.

Steve
PC Datasheet

 

[Joe Cilinceon]

Thanks that got it for sure. Much appreciated the assistance.