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