Hi again.

This is a follow on from Question ID: 314053.

Your query works perfectly, however i want to get some more
information out of the tables and because i dont understand how the
LEFT JOIN works, i am getting errors such as 'join not supported' etc
and dont have a clue where to start (and i need this doing in a hurry
so dont have time to brush up my sql).

Here is what i am trying to achieve.

A page (asp) which displays a list of all the invoices.

InvoiceID    Invoice Date    Total Amount    Customer    Date Due    Status

The only difference is that i need the customer details, taken from
the tblcustomers table-firstname and surname and the due date. The
duedate takes the 'dateinvsent' field and adds on the number of days
in the 'term' field (the amount of days they have to pay varies from 7
to 90). And i need all this from 1 query

Here are the tables


tblinvoices

invoiceID    (PK)
JobID        (foreign key to the tbljobs table)
terms        (invoice terms eg, 7, 30 - in days)
Inv_name     (if the invoice is to go to someone other than the person
in the tblcustomer table, this box is filled in. If not, the name is
taken from tblcustomers)
date         (date the invoice was generated and sent)
paid         (date the invoice was paid, unpaid invoices remain blank)

tblcustomers

customerID   (PK)
firstname
surname

tbljobs

JobID        (PK)
customerID   (foreign key to tblcustomers)
charge       (labour charge for this job)

tblparts

id           (pk)
jobid        (foreign key to tbljobs)
pricecharged (per item)
description
quantity     (number of items)

these are not full field lists, just the bits i need.

So, i need a single query to populate these columns on my asp page

InvoiceID
Invoice Date (tblinvoices.date)
Total Amount (labour charge + price for all of the parts for that job)
Customer (tblcustomer.firstname, tblcustomer.surname relating to that job)
Date Due (tblinvoices.date+tblinvoices.term)
Status (have they paid or not - this is a date format)

If you need clarification, just ask.

Many thanks

Give this a try. Please let me know if I missed or misunderstood any
of your requirements.

SELECT tblInvoices.invoiceID, tblInvoices.date,
(Sum(tblParts.quantity*tblParts.price)+Max(tblJobs.[charge])) AS
TotalAmount, Max(IIf(IsNull([tblInvoices].[Inv_Name]),[tblcustomers].[firstname]+"
"+[tblcustomers].[lastname],[tblInvoices].[Inv_Name])) AS [Customer
Name], [tblInvoices].[date]+[tblInvoices].[terms] AS DateDue,
tblInvoices.paid AS Status
FROM ((tblInvoices INNER JOIN tblJobs ON tblInvoices.jobID =
tblJobs.jobID) INNER JOIN tblParts ON tblJobs.jobID = tblParts.jobID)
LEFT JOIN tblcustomers ON tblJobs.customerID = tblcustomers.customerID
GROUP BY tblInvoices.invoiceID, tblInvoices.date,
[tblInvoices].[date]+[tblInvoices].[terms], tblInvoices.paid;

---------------
Notes:
1. We are doing a trick for Customer Name. We're using an IIf to
decide whether to use the Inv_Name or to go get the customer info from
tblcustomers. If Inv_Name is filled in, then it will be used,
otherwise, we go to tblcustomer for the firstname and lastname.
2. The LEFT JOIN on tblcustomers ensures that all the Invoices will
appear, even if there is no matching Customer record.

Again, it would help if I knew which version of which database product
you are using. They all have their little quirks.

- Hammer

---

Clarification of Answer by hammer-ga on 07 Mar 2004 09:09 PST

BTW, having field called "date" can create subtle problems. You should
consider renaming that field to inv_date or something.

- Hammer

---

Request for Answer Clarification by apcs_uk-ga on 07 Mar 2004 09:56 PST

i feel less stupid this time, thats quite a long query!

im having a few problems though.

Here, can you have a look at the db and see if you can get it working?

http://www.apcs-uk.co.uk/ga.mdb

Sorry to be a pain!

---

Clarification of Answer by hammer-ga on 07 Mar 2004 10:17 PST

Some of your field names changed. This one works for me using your sample database.

SELECT tblInvoices.invoiceID, tblInvoices.date,
(Sum(tblParts.quantity*tblParts.pricecharged)+Max(tblJobs.[charge]))
AS TotalAmount, Max(IIf((IsNull([tblInvoices].[invname])) Or
([tblInvoices].[invname]=""),([tblcustomers].[firstname]+Chr(32)+[tblcustomers].[surname]),[tblInvoices].[invname]))
AS CustomerName, [tblInvoices].[date]+[tblInvoices].[terms] AS
DateDue, tblInvoices.paid AS Status
FROM ((tblInvoices INNER JOIN tblJobs ON tblInvoices.jobid =
tblJobs.id) INNER JOIN tblParts ON tblJobs.id = tblParts.jobid) LEFT
JOIN tblcustomers ON tblJobs.customerID = tblcustomers.id
GROUP BY tblInvoices.invoiceID, tblInvoices.date,
[tblInvoices].[date]+[tblInvoices].[terms], tblInvoices.paid;


- Hammer

---

Request for Answer Clarification by apcs_uk-ga on 07 Mar 2004 10:27 PST

this is just out of curiosity, why do some of the table/fieldnames
have [] surrounding them and some not

eg.

GROUP BY tblInvoices.invoiceID, tblInvoices.date,
[tblInvoices].[date]+[tblInvoices].[terms], tblInvoices.paid;

tis not important, just got me wondering

---

Clarification of Answer by hammer-ga on 07 Mar 2004 10:52 PST

Access adds in the square braces. You only need them if the field name
or table name has a space in it.

BTW, please don't feel stupid. I've been writing these queries for
over ten years. You do get a little faster after that much time. :)

- Hammer

It makes me sick that you can write that so quickly! Ive been sweating
over it for days! I will definatly be coming back for more.

Perfect answer, communicates well, VERY fast response (damn it!). Cant
recommend enough!