Show maximum date in a query/report - JCW

J

JohnW

I am having trouble with a query/report that I run that should show the last
payment date that a tuition payment was made. It works for most records but
for the ones where the tuition payment is more than it was the previous month
then it it showing the payment date and tuition payment from that previous
month.
For example the records are
PayDate of 2/3/2010 and TuitionPayment of $45.00
PayDate of 3/2/2010 and TuitionPayment of $80.00
I would expect the query/report to show the 3/2/2010 payment but it is
showing the 2/3/2010.
Like I said most of the accounts are showing the correct last pay date but I
have 6 or 7 that will not and they all have higher tuition payments that then
previous month. I have also noticed that when I run the query itself, on the
acounts that it works on it will only show 1 record. On the accounts that is
is NOT working on it shows multiple records.
I am not sure what to try next. The fields names in question are:
PayDate - Date/Time format
TuitionPayment - Currency format

I am using a Total Query and using MAX for the PayDate field.

The SQL view of the query that the reports run from looks like this

SELECT Max(ECG.PayDate) AS MaxOfPayDate, ECG.TuitionPayment,
Last(ECG.luPayMethod) AS LastOfluPayMethod, Last(ECG.[Check#]) AS
[LastOfCheck#], ECG.Gymnasts, quBilling.BillingName,
quBilling.BillingAddress, quBilling.luBillingCity, quBilling.BillingState,
quBilling.luBillingZip, quBilling.InvComments, quBilling.MonthlyTotal,
quBilling.[Late Fee], quBilling.BalanceDue
FROM ECG INNER JOIN quBilling ON ECG.Gymnasts = quBilling.Gymnasts
GROUP BY ECG.TuitionPayment, ECG.Gymnasts, quBilling.BillingName,
quBilling.BillingAddress, quBilling.luBillingCity, quBilling.BillingState,
quBilling.luBillingZip, quBilling.InvComments, quBilling.MonthlyTotal,
quBilling.[Late Fee], quBilling.BalanceDue
HAVING (((ECG.TuitionPayment)>0) AND ((ECG.Gymnasts)=[Forms]![Gymnasts
List]![cboGymnasts]));

I hope someone can make sense of this. Thanks
 
J

John Spencer

Getting a related field from a GroupBy (total) query
Author: Michel Walsh

See:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

One method would be to use a query with a sub-query in the FROM clause. That
might look like the following query

SELECT ECG.PayDate
, ECG.TuitionPayment
, ECG.luPayMethod
, ECG.[Check#]
, ECG.Gymnasts
, quBilling.BillingName
, quBilling.BillingAddress
, quBilling.luBillingCity
, quBilling.BillingState,
quBilling.luBillingZip,
quBilling.InvComments
, quBilling.MonthlyTotal,
quBilling.[Late Fee]
, quBilling.BalanceDue
FROM (ECG INNER JOIN quBilling
ON ECG.Gymnasts = quBilling.Gymnasts)
INNER JOIN

(SELECT ECG.Gymnasts, MAX(ECG.Paydate]) As LastPaymentDate
FROM ECG
WHERE ECG.TuitionPayment>0
GROUP BY ECG.Gymnasts) as LastPay

ON ECG.Gymnasts = LastPay.Gymnasts
AND ECG.PayDate = LastPay.LastPaymentDate
AND ECG.Gymnasts=[Forms]![Gymnasts List]![cboGymnasts]


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I am having trouble with a query/report that I run that should show the last
payment date that a tuition payment was made. It works for most records but
for the ones where the tuition payment is more than it was the previous month
then it it showing the payment date and tuition payment from that previous
month.
For example the records are
PayDate of 2/3/2010 and TuitionPayment of $45.00
PayDate of 3/2/2010 and TuitionPayment of $80.00
I would expect the query/report to show the 3/2/2010 payment but it is
showing the 2/3/2010.
Like I said most of the accounts are showing the correct last pay date but I
have 6 or 7 that will not and they all have higher tuition payments that then
previous month. I have also noticed that when I run the query itself, on the
acounts that it works on it will only show 1 record. On the accounts that is
is NOT working on it shows multiple records.
I am not sure what to try next. The fields names in question are:
PayDate - Date/Time format
TuitionPayment - Currency format

I am using a Total Query and using MAX for the PayDate field.

The SQL view of the query that the reports run from looks like this

SELECT Max(ECG.PayDate) AS MaxOfPayDate, ECG.TuitionPayment,
Last(ECG.luPayMethod) AS LastOfluPayMethod, Last(ECG.[Check#]) AS
[LastOfCheck#], ECG.Gymnasts, quBilling.BillingName,
quBilling.BillingAddress, quBilling.luBillingCity, quBilling.BillingState,
quBilling.luBillingZip, quBilling.InvComments, quBilling.MonthlyTotal,
quBilling.[Late Fee], quBilling.BalanceDue
FROM ECG INNER JOIN quBilling ON ECG.Gymnasts = quBilling.Gymnasts
GROUP BY ECG.TuitionPayment, ECG.Gymnasts, quBilling.BillingName,
quBilling.BillingAddress, quBilling.luBillingCity, quBilling.BillingState,
quBilling.luBillingZip, quBilling.InvComments, quBilling.MonthlyTotal,
quBilling.[Late Fee], quBilling.BalanceDue
HAVING (((ECG.TuitionPayment)>0) AND ((ECG.Gymnasts)=[Forms]![Gymnasts
List]![cboGymnasts]));

I hope someone can make sense of this. Thanks
 
J

JohnW

John.....thanks for this. But when I copy this over what I have in the SQL
view I get "join expression not supported" error.
--
JCW


John Spencer said:
Getting a related field from a GroupBy (total) query
Author: Michel Walsh

See:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

One method would be to use a query with a sub-query in the FROM clause. That
might look like the following query

SELECT ECG.PayDate
, ECG.TuitionPayment
, ECG.luPayMethod
, ECG.[Check#]
, ECG.Gymnasts
, quBilling.BillingName
, quBilling.BillingAddress
, quBilling.luBillingCity
, quBilling.BillingState,
quBilling.luBillingZip,
quBilling.InvComments
, quBilling.MonthlyTotal,
quBilling.[Late Fee]
, quBilling.BalanceDue
FROM (ECG INNER JOIN quBilling
ON ECG.Gymnasts = quBilling.Gymnasts)
INNER JOIN

(SELECT ECG.Gymnasts, MAX(ECG.Paydate]) As LastPaymentDate
FROM ECG
WHERE ECG.TuitionPayment>0
GROUP BY ECG.Gymnasts) as LastPay

ON ECG.Gymnasts = LastPay.Gymnasts
AND ECG.PayDate = LastPay.LastPaymentDate
AND ECG.Gymnasts=[Forms]![Gymnasts List]![cboGymnasts]


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I am having trouble with a query/report that I run that should show the last
payment date that a tuition payment was made. It works for most records but
for the ones where the tuition payment is more than it was the previous month
then it it showing the payment date and tuition payment from that previous
month.
For example the records are
PayDate of 2/3/2010 and TuitionPayment of $45.00
PayDate of 3/2/2010 and TuitionPayment of $80.00
I would expect the query/report to show the 3/2/2010 payment but it is
showing the 2/3/2010.
Like I said most of the accounts are showing the correct last pay date but I
have 6 or 7 that will not and they all have higher tuition payments that then
previous month. I have also noticed that when I run the query itself, on the
acounts that it works on it will only show 1 record. On the accounts that is
is NOT working on it shows multiple records.
I am not sure what to try next. The fields names in question are:
PayDate - Date/Time format
TuitionPayment - Currency format

I am using a Total Query and using MAX for the PayDate field.

The SQL view of the query that the reports run from looks like this

SELECT Max(ECG.PayDate) AS MaxOfPayDate, ECG.TuitionPayment,
Last(ECG.luPayMethod) AS LastOfluPayMethod, Last(ECG.[Check#]) AS
[LastOfCheck#], ECG.Gymnasts, quBilling.BillingName,
quBilling.BillingAddress, quBilling.luBillingCity, quBilling.BillingState,
quBilling.luBillingZip, quBilling.InvComments, quBilling.MonthlyTotal,
quBilling.[Late Fee], quBilling.BalanceDue
FROM ECG INNER JOIN quBilling ON ECG.Gymnasts = quBilling.Gymnasts
GROUP BY ECG.TuitionPayment, ECG.Gymnasts, quBilling.BillingName,
quBilling.BillingAddress, quBilling.luBillingCity, quBilling.BillingState,
quBilling.luBillingZip, quBilling.InvComments, quBilling.MonthlyTotal,
quBilling.[Late Fee], quBilling.BalanceDue
HAVING (((ECG.TuitionPayment)>0) AND ((ECG.Gymnasts)=[Forms]![Gymnasts
List]![cboGymnasts]));

I hope someone can make sense of this. Thanks
.
 
J

John Spencer

Whoops. One part of the ON clause in the join should have been in a WHERE
clause. Hope this one works for you.

SELECT ECG.PayDate
, ECG.TuitionPayment
, ECG.luPayMethod
, ECG.[Check#]
, ECG.Gymnasts
, quBilling.BillingName
, quBilling.BillingAddress
, quBilling.luBillingCity
, quBilling.BillingState,
quBilling.luBillingZip,
quBilling.InvComments
, quBilling.MonthlyTotal,
quBilling.[Late Fee]
, quBilling.BalanceDue
FROM (ECG INNER JOIN quBilling
ON ECG.Gymnasts = quBilling.Gymnasts)

INNER JOIN

(SELECT ECG.Gymnasts, MAX(ECG.Paydate]) As LastPaymentDate
FROM ECG
WHERE ECG.TuitionPayment>0
GROUP BY ECG.Gymnasts) as LastPay

ON ECG.Gymnasts = LastPay.Gymnasts
AND ECG.PayDate = LastPay.LastPaymentDate

WHERE ECG.Gymnasts=[Forms]![Gymnasts List]![cboGymnasts]

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
J

JohnW

John thanks so much for your help. This one worked; I am good to go again.

--
JCW


John Spencer said:
Whoops. One part of the ON clause in the join should have been in a WHERE
clause. Hope this one works for you.

SELECT ECG.PayDate
, ECG.TuitionPayment
, ECG.luPayMethod
, ECG.[Check#]
, ECG.Gymnasts
, quBilling.BillingName
, quBilling.BillingAddress
, quBilling.luBillingCity
, quBilling.BillingState,
quBilling.luBillingZip,
quBilling.InvComments
, quBilling.MonthlyTotal,
quBilling.[Late Fee]
, quBilling.BalanceDue
FROM (ECG INNER JOIN quBilling
ON ECG.Gymnasts = quBilling.Gymnasts)

INNER JOIN

(SELECT ECG.Gymnasts, MAX(ECG.Paydate]) As LastPaymentDate
FROM ECG
WHERE ECG.TuitionPayment>0
GROUP BY ECG.Gymnasts) as LastPay

ON ECG.Gymnasts = LastPay.Gymnasts
AND ECG.PayDate = LastPay.LastPaymentDate

WHERE ECG.Gymnasts=[Forms]![Gymnasts List]![cboGymnasts]

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
John.....thanks for this. But when I copy this over what I have in the SQL
view I get "join expression not supported" error.
.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top