Hi Brook,
I assumed that you couldn't just copy and paste what I wrote unless the
fields and tables names you posted were spelled exactly the same as in your
DB, which I suspected they weren't.
What you should do is create the query in the grid. That way there won't be
any mis-spellings. Capitalization and spelling are crucial, so perhaps your
invoicedetails table might actually be tblInvoiceDetails, as opposed to
tblinvoicedetails.
Brian
Brian...
Thank you for the quick post... however when I add the code, I am getting
a
syntax error... any ideas? I'm taking your code and pasting it into my SQL
code?
BRook
:
SELECT invoiceid, sum(nz([paymentamount],0) As [Total Payments]
FROM tblinvoicedetails LEFT JOIN tblinvoicepayments ON
tblinvoicedetails.invoiceid = tblinvoicepayments.invoiceid
GROUP BY tblinvoicedetails.invoiceid;
Hi Brook,
using the fieldnames and tablenames just as you have posted them:
SELECT invoiceid, sum(nz([paymentamount],0) As [Total Payments]
FROM tblinvoicedetails LEFT JOIN tblinvoicepayments ON
tblinvoicedetail.invoiceid = tblinvoicepayments.invoiceid
GROUP BY tblinvoicedetails.invoiceid;
you'll need to revise this to accommodate Caps in your names.
Brian
ok... I am working on trying to duplicate your SQL example with my
data,
but
so far i'm unsuccessful...
Here are my tbl names and fields... can you help me? give me a
little
more
guidance?
Table: tblinvoicedetails
invoiceid
orderid
invoicenumber
serialnumber
DesignNumber
DesignName
Quality
Size
SqFt
PricePerSqFoot
TotalPrice
shippingcost
DiscountAmt
invoicetype
clientname
Table: tblinvoicepayments
paymentid
invoiceid
invoicedate
invoicenumber
paymentnumber
paymentdate
paymenttype
paymentamount
invoicetotal
checknumber
amountdue
Thanks,
Brook
:
No, there are no payment entries in my payment table if none are
made.
HTH,
Brian
So, in your case, if no payment has been made, are there any
entries
in
your
invoicepayment table? That is my case...
Brook
:
Hi Brook,
Unless I've completely misunderstood your intent, I
respectfully
beg
to
differ. Have you even tried creating a query as I suggested?
This
technique
works quite well. Below is the SQL I use in my invoicing
system.
It
returns
the sum of payments if any are made, and returns $0.00 if none
were
made. I
don't add dummy records to my tables.
SELECT tblJobs.JobID, Sum(nz([Pmt],0)) AS [Total Payments]
FROM tblJobs LEFT JOIN tblPayments ON tblJobs.JobID =
tblPayments.JobID
GROUP BY tblJobs.JobID;
The above was posted only to show you the basic structure.
You'd
have to
change your table and field names appropriately.
HTH,
Brian
Well, the issue is, is that if the client has not made a
payment
and
the
invoice is past due, the invoice payments table wouldn't
work in
this
situation b/c it would contain no data/payments so therefore
I
couldn't
create the report.
Do you understand?
Brook
:
Hi Brook,
You shouldn't need to add a non-payment at all. What you'd
do
for
the
report
is join your invoice table to the invoice payments table,
and
then
change
the join properties to show all invoices and any invoice
payments
(aka
LEFT
JOIN).
Post back if you need help getting this right.
Brian
godo day all,
I have an frminvoices, frminvoicedetails subform,
and
frminvoicepayments subform.
When I initially create an invoice, I add the
records
through
the
frminvoicedetails subform, and don't enter a payment
into
the
frminvoicepayments until an the first payment is made.
What I am running into is this, I want to be able to
create a
report
that will allow for me to show invoices that have a
balance
due
with
or
without a payment being made.