The following query works ok:

SELECT Categories.CategoryID, Categories.Category, Sum(InvoiceDetails.Amount) AS SumOfAmount
FROM Categories LEFT JOIN InvoiceDetails ON Categories.CategoryID = InvoiceDetails.CategoryID
WHERE (((Categories.ResaleOhdTakings)="R"))
GROUP BY Categories.CategoryID, Categories.Category, InvoiceDetails.CategoryID;

But as soon as I add the following Invoices table (in order that I can use ReturnID in the criteria) I get the dreaded "ambiguous joins" error message. I really do want a single SQL statement and not a statement joined to a query as suggested in Microsoft KB. I've been playing with this for weeks without success. Any clues please?

SELECT Categories.CategoryID, Categories.Category, Sum(InvoiceDetails.Amount) AS SumOfAmount
FROM Invoices INNER JOIN (Categories LEFT JOIN InvoiceDetails ON Categories.CategoryID = InvoiceDetails.CategoryID) ON Invoices.InvoiceID = InvoiceDetails.InvoiceID
WHERE (((Categories.ResaleOhdTakings)="R") AND ((Invoices.ReturnID)="4090604"))
GROUP BY Categories.CategoryID, Categories.Category, InvoiceDetails.CategoryID;

Many thanks.


Clive Astley

__________________
Clive Astley

I have not checked the following in Access but the following should work with SQL Server, but try changing the FROM part of your SQL statement to:

FROM Categories
INNER JOIN InvoiceDetails ON Categories.CategoryID = InvoiceDetails.CategoryID
LEFT JOIN Invoices ON Invoices.InvoiceID = InvoiceDetails.InvoiceID

leaving the SELECT, WHERE and GROUP BY parts intact. (I find it easier to read and hence debug in this form!)

If you desire to know the total invoice amount for all categories, irrespective of whether there is an invoice for the category you should change "LEFT JOIN" and "INNER JOIN" to "LEFT INNER JOIN", otherwise you should change the code to:

FROM Categories, InvoiceDetails, Invoices
WHERE Categories.CategoryID = InvoiceDetails.CategoryID
     AND Invoices.InvoiceID = InvoiceDetails.InvoiceID
     AND <other WHERE conditions>

Incidentally, if you have used the query builder you may have to switch to SQL mode before applying the changes. You *may* not be able to change your query using the query builder once you have made your changes, so I suggest that you backup your query and/or database *before* making the changes.

Best wishes
Marcus.

--
All suggestions given are just that, we accept no liability for or guarantee any
suggestions made. Any changes to your system or business decisions are taken entirely at your own risk.

Many thanks for your suggestion Marcus. I tried:

SELECT Categories.CategoryID, Categories.Category, Sum(InvoiceDetails.Amount) AS SumOfAmount
FROM Categories
LEFT JOIN InvoiceDetails ON Categories.CategoryID = InvoiceDetails.CategoryID
LEFT JOIN Invoices ON Invoices.InvoiceID = InvoiceDetails.InvoiceID
WHERE (((Categories.ResaleOhdTakings)="R") AND ((Invoices.ReturnID)="4090604"))
GROUP BY Categories.CategoryID, Categories.Category, InvoiceDetails.CategoryID;

but get the error:

Syntax error (missing operator) in query expression 'Categories.CategoryID = InvoiceDetails.CategoryID LEFT JOIN Invoices ON Invoices.InvoiceID = InvoicesDetails.InvoiceID'

Yes, I have been using the SQL window rather than the QBE grid. But this is really bugging me because I've been trying it for a couple of weeks. I can do it by linking a table to a query but I want to do it in one single SQL statement.

Best wishes,,

Clive Astley

Hi Clive

I know that there is a slight difference between Access SQL and SQL Server SQL and unfortunately, I was giving you the latter. After creating a test database in Access 2000 the query I thnk that you are after is:

SELECT Categories.CategoryID, Categories.Category, Sum(InvoiceDetails.Amount) AS SumOfAmount
FROM (Categories LEFT JOIN InvoiceDetails ON Categories.CategoryID = InvoiceDetails.CategoryID) LEFT JOIN Invoices ON InvoiceDetails.InvoiceID = Invoices.InvoiceID
WHERE (((Categories.ResaleOhdTakings)="R") AND ((Invoices.ReturnID)="4090604"))
GROUP BY Categories.CategoryID, Categories.Category;

This should display in design view.

The trick is to click on the links between the tables, select "join properties" and ensure that the 1,2,3 options are as you require it. For invoice details it is logical to have a total for all categories irrespective of whether there is an invoice for the category. So you should select the option saying roughly:

"Include ALL records from 'Categories' and only those records from 'InvoiceDetails' where the joined fields are equal."

In your case, this will include records from InvoiceDetails only when you have invoice details for the category, which I imagine is what you are after.

As a cross check you could sum all of the invoice totals in your query (without the conditions on ResaleOhdTakings and ReturnID) and sum the Amount field in InvoiceDetails. Both figures should match.

I hope that this helps, best wishes
Marcus.

--
All suggestions given are just that, we accept no liability for or guarantee any
suggestions made. Any changes to your system or business decisions are taken entirely at your own risk.

Many thanks Marcus.

This gives the same result as I had before. The figures are correct but there is no line for the Categories for which there are no associated records in InvoiceDetails. It is as if the LEFT JOIN isn't having any effect on Categories.

Best wishes,

Clive Astley