Google Answers Logo
View Question
Q: for hammer-ga only please ( Answered 5 out of 5 stars,   0 Comments )
Subject: for hammer-ga only please
Category: Computers > Programming
Asked by: apcs_uk-ga
List Price: $20.00
Posted: 07 Mar 2004 08:07 PST
Expires: 06 Apr 2004 09:07 PDT
Question ID: 314240
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


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)


customerID   (PK)


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


id           (pk)
jobid        (foreign key to tbljobs)
pricecharged (per item)
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

Invoice 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 (
Status (have they paid or not - this is a date format)

If you need clarification, just ask.

Many thanks
Subject: Re: for hammer-ga only please
Answered By: hammer-ga on 07 Mar 2004 08:59 PST
Rated:5 out of 5 stars
Give this a try. Please let me know if I missed or misunderstood any
of your requirements.

SELECT tblInvoices.invoiceID,,
(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].[terms], tblInvoices.paid;

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?

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,,
AS TotalAmount, Max(IIf((IsNull([tblInvoices].[invname])) Or
AS CustomerName, [tblInvoices].[date]+[tblInvoices].[terms] AS
DateDue, tblInvoices.paid AS Status
FROM ((tblInvoices INNER JOIN tblJobs ON tblInvoices.jobid = INNER JOIN tblParts ON = tblParts.jobid) LEFT
JOIN tblcustomers ON tblJobs.customerID =
GROUP BY tblInvoices.invoiceID,,
[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


GROUP BY tblInvoices.invoiceID,,
[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
apcs_uk-ga rated this answer:5 out of 5 stars and gave an additional tip of: $10.00
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!

There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy