Summing payment records in a query but restricting it to variable dates in another table

[annysjunkmail]

I have a complicated query I need to run and am not sure how to
approach it...looking for some guidance.
I have 2 tables:

1) tblPayments (contains a list of payments made to an Applicant)
2) tblAuditCheck contains a list of Applicants with a variable 'cut-
off' audit date)

tblPlayments has fields: ApplicantID, DateOfPayment, AmtofPayment
tblAuditCheck has fields: ApplicantID, DateofAuditCheck

Example data from tblPlayments is as follows:
ApplicantID DateOfPayment AmtofPayment
1 17/07/2006 £10,000
1 29/07/2006 £2,500
1 19/09/2006 £1,000
2 15/09/2006 £1,200
2 19/09/2006 £2,200
2 30/09/2006 £3,100
3 03/10/2006 £4,100
3 05/10/2006 £5,800
3 04/11/2006 £7,00

I am trying, via a query, to sum the payments in tblPayments BUT
restricting it to the audit date contained in field DateofAuditCheck
in tblAuditCheck so, based on the above data, it should like the
following.

ApplicantID DateofAuditCheck TotalPaymentReceived
1 01/08/2006 £12,250
2 20/09/2006 £3,400
3 05/10/2006 £9,900

There are approx 10000 records to be analysed hence the need for a
speedy query.
I really hope this can be done.

Grateful for any advice
Chris

 

[Roger Carlson]

*Create a new query in the Query Builder.
*Add both tables to the query and Join them on ApplicationID<->ApplicationID
and DateOfPayment<->Dateof AuditCheck.
*Drag the ApplicantID and DateofAuditCheck field from tblAuditCheck and the
Payment field from tblPayments.
*This will give you a list of payments that have been auditchecked.
*Now, hit the Totals button (Greek E) which will convert your query to a
Totals (or aggregate) Query.
*In the Totals line of the fields, leave both ApplicantID and
DateofAuditCheck as Group By, but select Sum in the Totals line of Payments.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have a complicated query I need to run and am not sure how to
approach it...looking for some guidance.
I have 2 tables:

1) tblPayments (contains a list of payments made to an Applicant)
2) tblAuditCheck contains a list of Applicants with a variable 'cut-
off' audit date)

tblPlayments has fields: ApplicantID, DateOfPayment, AmtofPayment
tblAuditCheck has fields: ApplicantID, DateofAuditCheck

Example data from tblPlayments is as follows:
ApplicantID DateOfPayment AmtofPayment
1 17/07/2006 £10,000
1 29/07/2006 £2,500
1 19/09/2006 £1,000
2 15/09/2006 £1,200
2 19/09/2006 £2,200
2 30/09/2006 £3,100
3 03/10/2006 £4,100
3 05/10/2006 £5,800
3 04/11/2006 £7,00

I am trying, via a query, to sum the payments in tblPayments BUT
restricting it to the audit date contained in field DateofAuditCheck
in tblAuditCheck so, based on the above data, it should like the
following.

ApplicantID DateofAuditCheck TotalPaymentReceived
1 01/08/2006 £12,250
2 20/09/2006 £3,400
3 05/10/2006 £9,900

There are approx 10000 records to be analysed hence the need for a
speedy query.
I really hope this can be done.

Grateful for any advice
Chris

 

[Jerry Whittle]

I'm assuming by "restricting" you mean less than or equal to the audit date.

SELECT TblPayments.ApplicantID,
DMax("[DateofAuditCheck]","tblAuditCheck","[TblAuditCheck.ApplicantID] = "
& [TblPayments].[ApplicantID]) AS DateofAuditCheck,
Sum(TblPayments.AmtofPayment) AS TotalPaymentReceived
FROM TblPayments
WHERE TblPayments.DateOfPayment<= (SELECT TblAuditCheck.DateofAuditCheck
FROM TblAuditCheck WHERE TblAuditCheck.ApplicantID = TblPayments.ApplicantID
) +0.9999
GROUP BY TblPayments.ApplicantID,
DMax("[DateofAuditCheck]","tblAuditCheck","[TblAuditCheck.ApplicantID] = "
& [TblPayments].[ApplicantID]);

Watch out for word-wrapping in the above. It won't work if the date fields
or payments are not text or number/currency fields respectively. Also the
formatting of the dates in DD/MM/YYYY might cause problems with the SQL.

 

[annysjunkmail]

I'm assuming by "restricting" you mean less than or equal to the audit date.

SELECT TblPayments.ApplicantID,
DMax("[DateofAuditCheck]","tblAuditCheck","[TblAuditCheck.ApplicantID] = "
& [TblPayments].[ApplicantID]) AS DateofAuditCheck,
Sum(TblPayments.AmtofPayment) AS TotalPaymentReceived
FROM TblPayments
WHERE TblPayments.DateOfPayment<= (SELECT TblAuditCheck.DateofAuditCheck
FROM TblAuditCheck WHERE TblAuditCheck.ApplicantID = TblPayments.ApplicantID
) +0.9999
GROUP BY TblPayments.ApplicantID,
DMax("[DateofAuditCheck]","tblAuditCheck","[TblAuditCheck.ApplicantID] = "
& [TblPayments].[ApplicantID]);

Watch out for word-wrapping in the above. It won't work if the date fields
or payments are not text or number/currency fields respectively. Also the
formatting of the dates in DD/MM/YYYY might cause problems with the SQL.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a complicated query I need to run and am not sure how to
approach it...looking for some guidance.
I have 2 tables:

1) tblPayments (contains a list of payments made to an Applicant)
2) tblAuditCheck contains a list of Applicants with a variable 'cut-
off' audit date)

tblPlayments has fields: ApplicantID, DateOfPayment, AmtofPayment
tblAuditCheck has fields: ApplicantID, DateofAuditCheck

Example data from tblPlayments is as follows:
ApplicantID DateOfPayment AmtofPayment
1 17/07/2006 £10,000
1 29/07/2006 £2,500
1 19/09/2006 £1,000
2 15/09/2006 £1,200
2 19/09/2006 £2,200
2 30/09/2006 £3,100
3 03/10/2006 £4,100
3 05/10/2006 £5,800
3 04/11/2006 £7,00

I am trying, via a query, to sum the payments in tblPayments BUT
restricting it to the audit date contained in field DateofAuditCheck
in tblAuditCheck so, based on the above data, it should like the
following.

ApplicantID DateofAuditCheck TotalPaymentReceived
1 01/08/2006 £12,250
2 20/09/2006 £3,400
3 05/10/2006 £9,900

There are approx 10000 records to be analysed hence the need for a
speedy query.
I really hope this can be done.

Grateful for any advice
Chris

Thank you for both your replies.
I am reading and writing this from home. I will try the SQL approach
in work tomorrow - looks good though and seems to be what I need ie <=
audit date.
I will reply tomorrow again to let you know how I get on

Many thanks
Chris

 

[annysjunkmail]

I'm assuming by "restricting" you mean less than or equal to the audit date.

SELECT TblPayments.ApplicantID,
DMax("[DateofAuditCheck]","tblAuditCheck","[TblAuditCheck.ApplicantID] = "
& [TblPayments].[ApplicantID]) AS DateofAuditCheck,
Sum(TblPayments.AmtofPayment) AS TotalPaymentReceived
FROM TblPayments
WHERE TblPayments.DateOfPayment<= (SELECT TblAuditCheck.DateofAuditCheck
FROM TblAuditCheck WHERE TblAuditCheck.ApplicantID = TblPayments.ApplicantID
) +0.9999
GROUP BY TblPayments.ApplicantID,
DMax("[DateofAuditCheck]","tblAuditCheck","[TblAuditCheck.ApplicantID] = "
& [TblPayments].[ApplicantID]);

Watch out for word-wrapping in the above. It won't work if the date fields
or payments are not text or number/currency fields respectively. Also the
formatting of the dates in DD/MM/YYYY might cause problems with the SQL.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a complicated query I need to run and am not sure how to
approach it...looking for some guidance.
I have 2 tables:

1) tblPayments (contains a list of payments made to an Applicant)
2) tblAuditCheck contains a list of Applicants with a variable 'cut-
off' audit date)

tblPlayments has fields: ApplicantID, DateOfPayment, AmtofPayment
tblAuditCheck has fields: ApplicantID, DateofAuditCheck

Example data from tblPlayments is as follows:
ApplicantID DateOfPayment AmtofPayment
1 17/07/2006 £10,000
1 29/07/2006 £2,500
1 19/09/2006 £1,000
2 15/09/2006 £1,200
2 19/09/2006 £2,200
2 30/09/2006 £3,100
3 03/10/2006 £4,100
3 05/10/2006 £5,800
3 04/11/2006 £7,00

I am trying, via a query, to sum the payments in tblPayments BUT
restricting it to the audit date contained in field DateofAuditCheck
in tblAuditCheck so, based on the above data, it should like the
following.

ApplicantID DateofAuditCheck TotalPaymentReceived
1 01/08/2006 £12,250
2 20/09/2006 £3,400
3 05/10/2006 £9,900

There are approx 10000 records to be analysed hence the need for a
speedy query.
I really hope this can be done.

Grateful for any advice
Chris- Hide quoted text -

- Show quoted text -

Very clever approach Jerry and worked a treat
Much appreciated
Tony