Can't get query to work...

[crispywafers]

Hi,

I have a query I made to show student's that have "paid in full" on
their invoice balance. My problem arises if the person/parent insteaad
of making one full payment to the invoice balance with one check-- ends
up making two or more payments that add up to a full payment. My
current query only works if the person makes 1 payment that is for the
full amount.

I would like the query to also show if, for that invoice, the payments
associated with it "add" up to the amount due. If they do, list the
student's name.

Help?

Current Query:

PARAMETERS [Specify School Year In Format 9/1/2004] DateTime,
[Trimester Term By Letter "J, D, or M"] Text ( 255 );
SELECT Student_Records.StudentID, Student_Records.SLastName,
Student_Records.SFirstName, Orders.TotalTuition, Payments.AmountPaid,
Orders.SchoolYear, Invoices.InvoiceID, Invoices.TriTerm,
Invoices.TriAmountDue
FROM Student_Records INNER JOIN ((Orders INNER JOIN Invoices ON
Orders.OrderID = Invoices.OrderID) LEFT JOIN Payments ON
Invoices.InvoiceID = Payments.InvoiceID) ON Student_Records.StudentID =
Orders.StudentID
WHERE (((Payments.AmountPaid)=[TriAmountDue]) AND
((Orders.SchoolYear)=[Specify School Year In Format 9/1/2004]) AND
((Invoices.TriTerm)=[Trimester Term By Letter "J, D, or M"]))
ORDER BY Student_Records.SLastName;

 

[Dale Fye]

Crispy,

I think I would start out with a query to sum payments against a specific
invoiceID:

qry_Payments:
SELECT P.InvoiceID, SUM(P.AmountPaid) as InvoiceAmountPaid
FROM Payments
GroupBY P.InvoiceID

Then replace the payments table in your query and make a few minor
adjustments:

PARAMETERS [Specify School Year In Format 9/1/2004] DateTime,
[Trimester Term By Letter "J, D, or M"] Text (
255 );
SELECT Student_Records.StudentID, Student_Records.SLastName,
Student_Records.SFirstName, Orders.TotalTuition,
Payments.InvoiceAmountPaid, Orders.SchoolYear,
Invoices.InvoiceID, Invoices.TriTerm, Invoices.TriAmountDue
FROM Student_Records
INNER JOIN ((Orders
INNER JOIN Invoices ON Orders.OrderID = Invoices.OrderID)
LEFT JOIN qry_Payments Payments ON Invoices.InvoiceID = Payments.InvoiceID)
ON Student_Records.StudentID = Orders.StudentID
WHERE Payments.InvoiceAmountPaid >= Invoices.[TriAmountDue])
AND Orders.SchoolYear=[Specify School Year In Format 9/1/2004]
AND Invoices.TriTerm = [Trimester Term By Letter "J, D, or M"]
ORDER BY Student_Records.SLastName;

However, this may not work the way you want if, for some reason, the student
has multiple invoices during a single semester. Since you don't give the
relationships between all the tables, I'm not going to try to guess on this
one. However, if a student can have multiple invoices during a given
semester, I would change the first query to include both the invoices and
payments tables, and would sum the invoice amounts, and payment amounts for
each student.

HTH
Dale

Hi,

I have a query I made to show student's that have "paid in full" on
their invoice balance. My problem arises if the person/parent insteaad
of making one full payment to the invoice balance with one check-- ends
up making two or more payments that add up to a full payment. My
current query only works if the person makes 1 payment that is for the
full amount.

I would like the query to also show if, for that invoice, the payments
associated with it "add" up to the amount due. If they do, list the
student's name.

Help?

Current Query:

PARAMETERS [Specify School Year In Format 9/1/2004] DateTime,
[Trimester Term By Letter "J, D, or M"] Text ( 255 );
SELECT Student_Records.StudentID, Student_Records.SLastName,
Student_Records.SFirstName, Orders.TotalTuition, Payments.AmountPaid,
Orders.SchoolYear, Invoices.InvoiceID, Invoices.TriTerm,
Invoices.TriAmountDue
FROM Student_Records INNER JOIN ((Orders INNER JOIN Invoices ON
Orders.OrderID = Invoices.OrderID) LEFT JOIN Payments ON
Invoices.InvoiceID = Payments.InvoiceID) ON Student_Records.StudentID =
Orders.StudentID
WHERE (((Payments.AmountPaid)=[TriAmountDue]) AND
((Orders.SchoolYear)=[Specify School Year In Format 9/1/2004]) AND
((Invoices.TriTerm)=[Trimester Term By Letter "J, D, or M"]))
ORDER BY Student_Records.SLastName;

 

[crispywafers]

Dale,

Thank you for your help. It worked like a charm-- and I now know how to
do similar things in the future.

Thank you again!
Abby

P.S Only one invoice per semester-- so this works beautifully.