Show the last record based on the last recorded date - JCW

[JohnW]

I have a report that we need to run when we send a invoice. The report shows
monthly total due plus late fee and then balance due plus things like gymnast
name and billing address etc. The report runs from a query which includes
all these fields.

I need to show the last payment date that was made for each gymnasts on this
invoice report along with the payment method and check #. Our table has these
fields for this...
Paydate
lumethod
check#
paymenttotal

Each payment for the gymnasts are recorded as a new record with info inputed
for the above fields.

I am not familiar with SQL so please be "gentle" with your responses.
Thanks

 

[KenSheridan via AccessMonster.com]

Its difficult to avoid SQL for this sort of thing, but its quite simple so
you should have no difficulty with it. I'd suggest that you do it by
creating a separate query and base a simple one-line report on this, and then
embed this report as a subreport into your current report in whatever section
is appropriate, e.g. the detail section if the report has one detail per
gymnast, or in the gymnast group footer or header if its grouped by gymnast.

Lets assume your table is called Payments and in addition to the columns
you've mentioned includes a GymnastID column as a foreign key referencing the
primary key of a Gymnasts table. The query to return the last payment per
gymnast would be as follows:

SELECT *
FROM Payments AS P1
WHERE Paydate =
(SELECT MAX(Paydate)
FROM Payments AS P2
WHERE P2.GymnastID = P1.GymnastID);

The way this works is that the outer query and subquery are correlated on the
GymnastID column, the two instances of the Payments table being
differentiated by means of the aliases P1 and P2. The subquery returns the
latest (MAX) paydate for each current gymnast, and this therefore restricts
the outer query to those rows where the paydate is the latest one per gymnast.


Once you've created the query open it I datasheet view and check that it does
return the correct rows. If you are happy that it does then save the query
and create a simple report based on it, including the relevant columns from
the query in this report in whatever layout you want in the main report.

Then add the new report as a subreport to the main report and in design view
of the main report set the LinkMasterFields and LinkChildFields properties of
the subform control to GymnastID or whatever is the key column which uniquely
identifies each gymnast.

When you open the main report, for each gymnast the subreport should show one
row from the query you created, that with the data for the gymnast's latest
payment.

Ken Sheridan
Stafford, England