Query Problem - 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

[MMcCarthy]

Have you got a supplierID = 0 set to Not Known?

[jadeverell]

Have you got a supplierID = 0 set to Not Known?

Yep.

Thanks
JD.

[MMcCarthy]

Yep.

Thanks
JD.

I honestly can't see any problem with the query. Most be something else going on with the data. Unless someone else can spot something.

Sorry.

[jadeverell]

Can anyone else shed any light on this problem?

Thanks

JD

[FishVal]

[code=sql]
SELECT Tbl_Supplier.[Supplier Name], Sum(Nz(Tbl_Cont ract_General_He ad.[Value of Contract],0)) AS [Value of General Contracts], Sum(Nz(Tbl_Cont ract_Maintenanc e.[Value of Contract],0)) AS [Value of Maintenance Contracts], Sum(nz((Tbl_Con tract_Maintenan ce.[Value of Contract]))+nz(Tbl_Contr act_General_Hea d.[Value of Contract])) AS [Total value of Contracts], Count(Tbl_Contr act_General_Hea d.[Supplier ID]) AS [No of General Contracts], Count(Tbl_Contr act_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_Ma intenance ON Tbl_Supplier.[Supplier ID] = Tbl_Contract_Ma intenance.[Supplier ID]) LEFT JOIN Tbl_Contract_Ge neral_Head ON Tbl_Supplier.[Supplier ID] = Tbl_Contract_Ge neral_Head.[Supplier ID]
GROUP BY Tbl_Supplier.[Supplier Name]
ORDER BY Sum(nz((Tbl_Con tract_Maintenan ce.[Value of Contract]))+nz(Tbl_Contr act_General_Hea d.[Value of Contract])) DESC;
[/code]
[

Hi, JD.

You first join should work fine, but the second one joins the result of first one (not suppliers table as you might expect) with general contracts table. I wonder how did you get right results at all.

Regards,
Fish.

[jadeverell]

Hi, JD.

You first join should work fine, but the second one joins the result of first one (not suppliers table as you might expect) with general contracts table. I wonder how did you get right results at all.

Regards,
Fish.

Hi fish,

I think you are right, i have noticed that a few of the other calculations are wrong. Do you know what the correct join should be?

Thanks

[NeoPa]

Let me be controversial here and say that I don't think the join is wrong at all. What is wrong is the design of the tables.
Really you should think of having the to contract tables merged together as one table. The contents can be flagged differently to indicate what type of contract each record represents. This can be done as it currently stands with a UNION query, but that is wrong in so many ways (tidyness; logic; performance; etc).

To explain about the JOINs.
If you have a single supplier (S1) with two maintenance contracts (M1 & M2) and two general contracts (G1 & G2) then the result set would have to be :

S1, M1, G1
S1, M1, G2
S1, M2, G1
S1, M2, G2

As you can see, this is exactly the effect you're getting, but don't want in your query.

PS. Your results will be unreliable for any supplier which has multiple General contracts AND multiple Maintenance contracts

[MMcCarthy]

Good catch guys!

I missed the logic on that one.

[Rabbit]

Let me be controversial here and say that I don't think the join is wrong at all. What is wrong is the design of the tables.
Really you should think of having the to contract tables merged together as one table. The contents can be flagged differently to indicate what type of contract each record represents. This can be done as it currently stands with a UNION query, but that is wrong in so many ways (tidyness; logic; performance; etc).

To explain about the JOINs.
If you have a single supplier (S1) with two maintenance contracts (M1 & M2) and two general contracts (G1 & G2) then the result set would have to be :

S1, M1, G1
S1, M1, G2
S1, M2, G1
S1, M2, G2

As you can see, this is exactly the effect you're getting, but don't want in your query.

PS. Your results will be unreliable for any supplier which has multiple General contracts AND multiple Maintenance contracts

It might not be correct to merge the two tables. What if the two types of contracts are distinct and require different information to be collected? Sure there will be some overlap in fields between the two types of contracts but if there are a lot of fields that one type of contract collects that the other doesn't, then merging them might not be the right answer.

In this case, you would have to separate the queries first and then bring the results together.

[jadeverell]

It might not be correct to merge the two tables. What if the two types of contracts are distinct and require different information to be collected? Sure there will be some overlap in fields between the two types of contracts but if there are a lot of fields that one type of contract collects that the other doesn't, then merging them might not be the right answer.

In this case, you would have to separate the queries first and then bring the results together.

Hi rabbit, This is actaully the case, even though the two types of contracts have similar fields, the majority of them are unique to the type of contract.

Therefore is it best to join a few quieries using UNION statements?

What would be the most logical way to do this? hummmm

Thanks all for your help.

JD

[Rabbit]

Yes, you would do a separate query for each type of contract and then union the results together.

[FishVal]

Hi, JD.

Alternatively you may design two separate queries for maintenance and general orders tables respectively summing orders by customer. Thus you obtain two datasets where each record has a unique customer and sum of relevant orders.
Then you may join them with customers table in an ordinary way.

Regards,
Fish

[NeoPa]

It might not be correct to merge the two tables. What if the two types of contracts are distinct and require different information to be collected? Sure there will be some overlap in fields between the two types of contracts but if there are a lot of fields that one type of contract collects that the other doesn't, then merging them might not be the right answer.

In this case, you would have to separate the queries first and then bring the results together.

If this is the case (clearly the OP feels it is) then you could do worse than have a contracts table with all the common fields and two extra tables with the specific data for each type.

This would be preferable to the UNION query as the optimisations tend to be lost (therefore the benefit of using Access) when they get brought into the mix. Often it's actually fine to have a whole bunch of unused fields for each type - but if you're not comfortable with that then this is a neat, clean solution. It leaves you with a unified recordset of the Contracts which you require for this task.

[jadeverell]

Hi again,

Thank you all for your help on this.
I have decided to take the UNION approach but have been having some problems over the weekend trying to create this as there are so many calculations involved.

I have been using the following code, but the results are not displaying as i would expected....

SELECT  Tbl_Supplier.[Supplier Name], Sum(Nz(Tbl_Contract_Maintenance.[Value of Contract],0)) AS [Value of Maintenance Contracts], Count(Tbl_Contract_Maintenance.[Supplier ID]) AS [No of Maintenance Contracts]
FROM (Tbl_Supplier LEFT JOIN Tbl_Contract_Maintenance ON Tbl_Supplier.[Supplier ID] = Tbl_Contract_Maintenance.[Supplier ID])
GROUP BY Tbl_Supplier.[Supplier Name]
union
SELECT Tbl_Supplier.[Supplier Name],  Sum(Nz(Tbl_Contract_General_Head.[Value of Contract],0)) AS [Value of General Contracts], Count(Tbl_Contract_General_Head.[Supplier ID]) AS [No of General Contracts]
FROM (Tbl_Supplier LEFT JOIN Tbl_Contract_General_Head ON Tbl_Supplier.[Supplier ID] = Tbl_Contract_General_Head.[Supplier ID]) 
GROUP BY Tbl_Supplier.[Supplier Name]
union
Select Tbl_Supplier.[Supplier Name], Sum(nz((Tbl_Contract_Maintenance.[Value of Contract]))+nz(Tbl_Contract_General_Head.[Value of Contract])) AS [Total value of Contracts], Count(Tbl_Contract_Maintenance.[Supplier ID])+Count(Tbl_Contract_General_Head.[Supplier ID]) 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];

Can anyone please help me try to make sense of this?

Thanks