Query Problem - please Help!

[jadeverell]

Hi,

I am having a slight (but very annoying) problem with a query I am trying to create. I will try to explain what I am doing as best I can……

I have 3 tables, one is Maintenance contracts, one is for General Contracts and the other is a suppliers table.
The two contracts table link to supplier by Supplier ID. A supplier can supply both a general contract and a maintenance contract.

I am trying to produce a report that breaks down each supplier by the number of Maintenance contracts, the number of general contracts, the total value of the maintenance contracts, the total value of general contracts and then calculate a total for both (i.e. Total number of contracts and Total value of all contracts)

I am using the following code to achieve this……

SELECT Tbl_Supplier.[Supplier Name], Sum(Nz(Tbl_Contract_General_Head.[Value of Contract],0)) AS [Value of General Contracts], Sum(Nz(Tbl_Contract_Maintenance.[Value of Contract],0)) AS [Value of Maintenance Contracts], Sum(nz((Tbl_Contract_Maintenance.[Value of Contract]))+nz(Tbl_Contract_General_Head.[Value of Contract])) AS [Total value of Contracts], Count(Tbl_Contract_General_Head.[Supplier ID]) AS [No of General Contracts], Count(Tbl_Contract_Maintenance.[Supplier ID]) AS [No of Maintenance Contracts], [No of General Contracts]+[No of Maintenance Contracts] AS [Total Number of Contracts]
FROM (Tbl_Supplier LEFT JOIN Tbl_Contract_Maintenance ON Tbl_Supplier.[Supplier ID] = Tbl_Contract_Maintenance.[Supplier ID]) LEFT JOIN Tbl_Contract_General_Head ON Tbl_Supplier.[Supplier ID] = Tbl_Contract_General_Head.[Supplier ID]
GROUP BY Tbl_Supplier.[Supplier Name]
ORDER BY Sum(nz((Tbl_Contract_Maintenance.[Value of Contract]))+nz(Tbl_Contract_General_Head.[Value of Contract])) DESC;

The Problem
The above code seems to work fine, apart from where the supplier is Not Known (Supplier ID = 0). There are 16 General contracts and 3 Maintenance contracts with a ‘Not Known’ Supplier, but for some strange reason the query seems to be multiplying these two together (16*3) to give 48 Not known suppliers for each type of contract.

I have no idea what might be causing this and only for Not Known Suppliers?

Can anyone suggest any ideas?

Thanks

JD

 

[neileg]

This is known as a cartesian product and occurs because there is no link between the general and maintenance records. The simple answer is to add a 'Not known' supplier in the supplier table.

 

[jadeverell]

Hi Neileg,

There is already a 'Not Know' supplier (Supplier ID - 0) in the supplier table.

 

[neileg]

Ah, yes, my mistake.

When you create a query based on joined tables you will always get every valid combination of records. How does Access know how to match the 3 contracts with the 16? How many records do you expect to get? 3 or 16 or 19? There are 48 valid combinations so that's what you get.

You could exclude SupplierID 0 and you would get valid results from the rest of the records and handle the 'not known' another way.