Re: Can't get query to work...

From: Dale Fye (dale.fye_at_nospam.com)
Date: 12/14/04 

Date: Mon, 13 Dec 2004 20:34:23 -0500

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
<crispywafers@yahoo.com> wrote in message
news:1102959191.242904.185350@z14g2000cwz.googlegroups.com...
> 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;
>

Relevant Pages

  • Re: Help with query
    ... Ive managed to write the query to summ the payments.... ... FROM tblSalesOrder SO INNER JOIN (tblDespatch D INNER JOIN (tblTransaction T ...
    (microsoft.public.access.queries)
  • Re: What wrong with these two query statements converted to SQL
    ... > I had a very large number of query statements that I had converted to SQL ... > Query].[Total Payments] ... > FROM Attendees INNER JOIN (Registration LEFT JOIN [Sum Of Payments Query] ...
    (microsoft.public.access.formscoding)
  • Re: Help with query
    ... Ive managed to write the query to summ the payments.... ... FROM tblSalesOrder SO INNER JOIN (tblDespatch D INNER JOIN (tblTransaction T ... Why I am saying sort of is because a payment can have many invoices, ...
    (microsoft.public.access.queries)
  • Re: Two Report Issues: Please Help!
    ... You probably have a join between the table invoices and payments ... i.e. there must be a value in both tables for the query to ... I have made a report based on a query (Invoices ...
    (microsoft.public.access.reports)
  • Re: Business objects, subset of collection
    ... The application is for scheduling of payments of invoices and ... for the selected vendor ... subsets of the vendors / invoices: ... I have a Setfilter(DateTime filterDate) methods defined in the ...
    (comp.object)
Loading