Show maximum date in a query/report - JCW

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 a
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

--
JCW

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.TuitionPayment0
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

JohnW wrote:
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 a
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

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" wrote:

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.TuitionPayment0
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

JohnW wrote:
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 a
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

.

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.TuitionPayment0
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

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

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

--
JCW


"John Spencer" wrote:

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.TuitionPayment0
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

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