Invoiceing / Invoice Payment Add Initial Payment Record?

[Brian Bastl]

Yes, these newsgroups are wonderful. Everything I know about Access, I've
learned in these newsgroups. Glad I could help out.

Brian

Brian...

Everything is working great... I just implimented your changes and it
works great... I have been trying to figure this out on my own for 2 weeks
now... I always do my best to pick my own brain to figure things out... but
this forum has always been such a great support factor! thank you for your
support and help!

Brook

Hi Brook,

beyond the errors we've just corrected, I wouldn't know since I don't have
your DB. I would suggest you do this in the query's Design View. After you
have your two tables in the grid, double click the join line between the two
tables, and select the option to show all records from 'tblinvoicedetails'
and any matching records from 'tblinvoicepayments' where the joining fields
are equal. That will create the LEFT JOIN.

If you can't get it to work, you can always send me a compacted and zipped
copy of your db. I wouldn't be able to look at it until sometime tomorrow.

HTH,
Brian
bastelER<at>alltelLER<dot>net
remove all capitalized letters

Actually,

I did a DB Documenter and pasted that info into the forum, so all the
fields are named as is... so I will have to figure out where the erro is
coming from..

If you have any other ideas, please let me know..

Brook

:

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.

 

[Guest]

Good Day Brian,

I wanted to let you know that everything is working great and I created
my payment due report, but there is one invoice that is giving me trouble and
its really strange...

This one invoice for each payment that is made, in the new payment query
it is duplicated? I cannot figure it out..

Any ideas?

Brook

Yes, these newsgroups are wonderful. Everything I know about Access, I've
learned in these newsgroups. Glad I could help out.

Brian

Brian...

Everything is working great... I just implimented your changes and it
works great... I have been trying to figure this out on my own for 2 weeks
now... I always do my best to pick my own brain to figure things out... but
this forum has always been such a great support factor! thank you for your
support and help!

Brook

Hi Brook,

beyond the errors we've just corrected, I wouldn't know since I don't have
your DB. I would suggest you do this in the query's Design View. After you
have your two tables in the grid, double click the join line between the two
tables, and select the option to show all records from 'tblinvoicedetails'
and any matching records from 'tblinvoicepayments' where the joining fields
are equal. That will create the LEFT JOIN.

If you can't get it to work, you can always send me a compacted and zipped
copy of your db. I wouldn't be able to look at it until sometime tomorrow.

HTH,
Brian
bastelER<at>alltelLER<dot>net
remove all capitalized letters

Actually,

I did a DB Documenter and pasted that info into the forum, so all the
fields are named as is... so I will have to figure out where the erro is
coming from..

If you have any other ideas, please let me know..

Brook

:

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

 

[Brian Bastl]

Hi Brook,

Can you post your current SQL?

Brian

Good Day Brian,

I wanted to let you know that everything is working great and I created
my payment due report, but there is one invoice that is giving me trouble and
its really strange...

This one invoice for each payment that is made, in the new payment query
it is duplicated? I cannot figure it out..

Any ideas?

Brook

Yes, these newsgroups are wonderful. Everything I know about Access, I've
learned in these newsgroups. Glad I could help out.

Brian

Brian...

Everything is working great... I just implimented your changes and it
works great... I have been trying to figure this out on my own for 2 weeks
now... I always do my best to pick my own brain to figure things

out...
but

this forum has always been such a great support factor! thank you for your
support and help!

Brook

:

Hi Brook,

beyond the errors we've just corrected, I wouldn't know since I

don't
have

your DB. I would suggest you do this in the query's Design View.

After
you

have your two tables in the grid, double click the join line between

the
two

tables, and select the option to show all records from 'tblinvoicedetails'
and any matching records from 'tblinvoicepayments' where the joining fields
are equal. That will create the LEFT JOIN.

If you can't get it to work, you can always send me a compacted and zipped
copy of your db. I wouldn't be able to look at it until sometime tomorrow.

HTH,
Brian
bastelER<at>alltelLER<dot>net
remove all capitalized letters

Actually,

I did a DB Documenter and pasted that info into the forum, so all the
fields are named as is... so I will have to figure out where the

erro
is

coming from..

If you have any other ideas, please let me know..

Brook

:

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

 

[Guest]

Here you go:
SELECT qryinvoicepayments.invoicedate, qryinvoicedetails.serialnumber,
qryinvoicedetails.invoicetype, qryinvoicedetails.invoiceid,
Sum(nz(tblinvoicepayments.paymentamount,0)) AS [Total Payments],
qryinvoicepayments.paymentnumber, qryinvoicepayments.paymentdate,
qryinvoicepayments.paymentamount, qryinvoicepayments.invoicetotal
FROM qryinvoicedetails INNER JOIN qryinvoicepayments ON
qryinvoicedetails.invoiceid = qryinvoicepayments.invoiceid
GROUP BY qryinvoicepayments.invoicedate, qryinvoicedetails.serialnumber,
qryinvoicedetails.invoicetype, qryinvoicedetails.invoiceid,
qryinvoicepayments.paymentnumber, qryinvoicepayments.paymentdate,
qryinvoicepayments.paymentamount, qryinvoicepayments.invoicetotal
HAVING (((qryinvoicedetails.invoicetype)="Sold"));


thanks,
Brook

Hi Brook,

Can you post your current SQL?

Brian

Good Day Brian,

I wanted to let you know that everything is working great and I created
my payment due report, but there is one invoice that is giving me trouble and
its really strange...

This one invoice for each payment that is made, in the new payment query
it is duplicated? I cannot figure it out..

Any ideas?

Brook

Yes, these newsgroups are wonderful. Everything I know about Access, I've
learned in these newsgroups. Glad I could help out.

Brian


Brian...

Everything is working great... I just implimented your changes and it
works great... I have been trying to figure this out on my own for 2 weeks
now... I always do my best to pick my own brain to figure things out...
but
this forum has always been such a great support factor! thank you for your
support and help!

Brook

:

Hi Brook,

beyond the errors we've just corrected, I wouldn't know since I don't
have
your DB. I would suggest you do this in the query's Design View. After
you
have your two tables in the grid, double click the join line between the
two
tables, and select the option to show all records from
'tblinvoicedetails'
and any matching records from 'tblinvoicepayments' where the joining
fields
are equal. That will create the LEFT JOIN.

If you can't get it to work, you can always send me a compacted and
zipped
copy of your db. I wouldn't be able to look at it until sometime
tomorrow.

HTH,
Brian
bastelER<at>alltelLER<dot>net
remove all capitalized letters

Actually,

I did a DB Documenter and pasted that info into the forum, so all the
fields are named as is... so I will have to figure out where the erro
is
coming from..

If you have any other ideas, please let me know..

Brook

:

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.

 

[Guest]

good day Brian..

hope all is well with you.

I have been looking and looking and don't know where to look anymore, but
there is one invoice in which the payments are being duplicated, all others
are fine...

Any ideas?

Brook

Yes, these newsgroups are wonderful. Everything I know about Access, I've
learned in these newsgroups. Glad I could help out.

Brian

Brian...

Everything is working great... I just implimented your changes and it
works great... I have been trying to figure this out on my own for 2 weeks
now... I always do my best to pick my own brain to figure things out... but
this forum has always been such a great support factor! thank you for your
support and help!

Brook

Hi Brook,

beyond the errors we've just corrected, I wouldn't know since I don't have
your DB. I would suggest you do this in the query's Design View. After you
have your two tables in the grid, double click the join line between the two
tables, and select the option to show all records from 'tblinvoicedetails'
and any matching records from 'tblinvoicepayments' where the joining fields
are equal. That will create the LEFT JOIN.

If you can't get it to work, you can always send me a compacted and zipped
copy of your db. I wouldn't be able to look at it until sometime tomorrow.

HTH,
Brian
bastelER<at>alltelLER<dot>net
remove all capitalized letters

Actually,

I did a DB Documenter and pasted that info into the forum, so all the
fields are named as is... so I will have to figure out where the erro is
coming from..

If you have any other ideas, please let me know..

Brook

:

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

 

[Brian Bastl]

Hi Brook,

Honestly, I can't tell without seeing what you have. If you'd like to send
me a stripped-down version of your db, including only the tables, queries,
forms, and report pertinent to your problem, I'll be happy to take a look at
it. Just make sure to compact, change the file extension from .mdb to .xxx,
and zip it up. Oh, and make sure that it is in A2000 or earlier file format.
I'm running A2000.

Brian

bastelER<at>SMalltel<dot>net
remove capitalizations and change <> appropriately

good day Brian..

hope all is well with you.

I have been looking and looking and don't know where to look anymore, but
there is one invoice in which the payments are being duplicated, all others
are fine...

Any ideas?

Brook

Yes, these newsgroups are wonderful. Everything I know about Access, I've
learned in these newsgroups. Glad I could help out.

Brian

Brian...

Everything is working great... I just implimented your changes and it
works great... I have been trying to figure this out on my own for 2 weeks
now... I always do my best to pick my own brain to figure things

out...
but

this forum has always been such a great support factor! thank you for your
support and help!

Brook

:

Hi Brook,

beyond the errors we've just corrected, I wouldn't know since I

don't
have

your DB. I would suggest you do this in the query's Design View.

After
you

have your two tables in the grid, double click the join line between

the
two

tables, and select the option to show all records from 'tblinvoicedetails'
and any matching records from 'tblinvoicepayments' where the joining fields
are equal. That will create the LEFT JOIN.

If you can't get it to work, you can always send me a compacted and zipped
copy of your db. I wouldn't be able to look at it until sometime tomorrow.

HTH,
Brian
bastelER<at>alltelLER<dot>net
remove all capitalized letters

Actually,

I did a DB Documenter and pasted that info into the forum, so all the
fields are named as is... so I will have to figure out where the

erro
is

coming from..

If you have any other ideas, please let me know..

Brook

:

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



"Brook" <[email protected]> wrote in

message

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

 

[Guest]

Brian,

After some thought process and walking away and coming back... I was able
to figure this out.. thanks so much!

Brook

Hi Brook,

Honestly, I can't tell without seeing what you have. If you'd like to send
me a stripped-down version of your db, including only the tables, queries,
forms, and report pertinent to your problem, I'll be happy to take a look at
it. Just make sure to compact, change the file extension from .mdb to .xxx,
and zip it up. Oh, and make sure that it is in A2000 or earlier file format.
I'm running A2000.

Brian

bastelER<at>SMalltel<dot>net
remove capitalizations and change <> appropriately

good day Brian..

hope all is well with you.

I have been looking and looking and don't know where to look anymore, but
there is one invoice in which the payments are being duplicated, all others
are fine...

Any ideas?

Brook

Yes, these newsgroups are wonderful. Everything I know about Access, I've
learned in these newsgroups. Glad I could help out.

Brian


Brian...

Everything is working great... I just implimented your changes and it
works great... I have been trying to figure this out on my own for 2 weeks
now... I always do my best to pick my own brain to figure things out...
but
this forum has always been such a great support factor! thank you for your
support and help!

Brook

:

Hi Brook,

beyond the errors we've just corrected, I wouldn't know since I don't
have
your DB. I would suggest you do this in the query's Design View. After
you
have your two tables in the grid, double click the join line between the
two
tables, and select the option to show all records from
'tblinvoicedetails'
and any matching records from 'tblinvoicepayments' where the joining
fields
are equal. That will create the LEFT JOIN.

If you can't get it to work, you can always send me a compacted and
zipped
copy of your db. I wouldn't be able to look at it until sometime
tomorrow.

HTH,
Brian
bastelER<at>alltelLER<dot>net
remove all capitalized letters

Actually,

I did a DB Documenter and pasted that info into the forum, so all the
fields are named as is... so I will have to figure out where the erro
is
coming from..

If you have any other ideas, please let me know..

Brook

:

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

 

[Brian Bastl]

Glad you got it taken care of.

Brian

Brian,

After some thought process and walking away and coming back... I was able
to figure this out.. thanks so much!

Brook

Hi Brook,

Honestly, I can't tell without seeing what you have. If you'd like to send
me a stripped-down version of your db, including only the tables, queries,
forms, and report pertinent to your problem, I'll be happy to take a look at
it. Just make sure to compact, change the file extension from .mdb to ..xxx,
and zip it up. Oh, and make sure that it is in A2000 or earlier file format.
I'm running A2000.

Brian

bastelER<at>SMalltel<dot>net
remove capitalizations and change <> appropriately

good day Brian..

hope all is well with you.

I have been looking and looking and don't know where to look anymore, but
there is one invoice in which the payments are being duplicated, all others
are fine...

Any ideas?

Brook

:

Yes, these newsgroups are wonderful. Everything I know about Access, I've
learned in these newsgroups. Glad I could help out.

Brian


Brian...

Everything is working great... I just implimented your changes

and
it

works great... I have been trying to figure this out on my own for

2
weeks

now... I always do my best to pick my own brain to figure things out...
but
this forum has always been such a great support factor! thank you

for
your

support and help!

Brook

:

Hi Brook,

beyond the errors we've just corrected, I wouldn't know since I don't
have
your DB. I would suggest you do this in the query's Design View. After
you
have your two tables in the grid, double click the join line

between
the

two
tables, and select the option to show all records from
'tblinvoicedetails'
and any matching records from 'tblinvoicepayments' where the joining
fields
are equal. That will create the LEFT JOIN.

If you can't get it to work, you can always send me a compacted and
zipped
copy of your db. I wouldn't be able to look at it until sometime
tomorrow.

HTH,
Brian
bastelER<at>alltelLER<dot>net
remove all capitalized letters

Actually,

I did a DB Documenter and pasted that info into the forum, so

all
the

fields are named as is... so I will have to figure out where

the
erro

is
coming from..

If you have any other ideas, please let me know..

Brook

:

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