By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,039 Members | 1,831 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,039 IT Pros & Developers. It's quick & easy.

Query Problem - Help!

P: 19
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……
Expand|Select|Wrap|Line Numbers
  1. 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]
  2. 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]
  3. GROUP BY Tbl_Supplier.[Supplier Name]
  4. 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
Feb 5 '08 #1
Share this Question
Share on Google+
15 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Have you got a supplierID = 0 set to Not Known?
Feb 5 '08 #2

P: 19
Have you got a supplierID = 0 set to Not Known?
Yep.

Thanks
JD.
Feb 5 '08 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Feb 5 '08 #4

P: 19
Can anyone else shed any light on this problem?

Thanks

JD
Feb 6 '08 #5

FishVal
Expert 2.5K+
P: 2,653

Expand|Select|Wrap|Line Numbers
  1. 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]
  2. 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]
  3. GROUP BY Tbl_Supplier.[Supplier Name]
  4. ORDER BY Sum(nz((Tbl_Contract_Maintenance.[Value of Contract]))+nz(Tbl_Contract_General_Head.[Value of Contract])) DESC;
  5.  
[
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.
Feb 6 '08 #6

P: 19
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
Feb 6 '08 #7

NeoPa
Expert Mod 15k+
P: 31,494
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 :
Expand|Select|Wrap|Line Numbers
  1. S1, M1, G1
  2. S1, M1, G2
  3. S1, M2, G1
  4. 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
Feb 6 '08 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Good catch guys!

I missed the logic on that one.
Feb 6 '08 #9

Rabbit
Expert Mod 10K+
P: 12,366
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 :
Expand|Select|Wrap|Line Numbers
  1. S1, M1, G1
  2. S1, M1, G2
  3. S1, M2, G1
  4. 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.
Feb 6 '08 #10

P: 19
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
Feb 7 '08 #11

Rabbit
Expert Mod 10K+
P: 12,366
Yes, you would do a separate query for each type of contract and then union the results together.
Feb 7 '08 #12

FishVal
Expert 2.5K+
P: 2,653
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
Feb 7 '08 #13

NeoPa
Expert Mod 15k+
P: 31,494
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.
Feb 7 '08 #14

P: 19
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....
Expand|Select|Wrap|Line Numbers
  1. 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]
  2. FROM (Tbl_Supplier LEFT JOIN Tbl_Contract_Maintenance ON Tbl_Supplier.[Supplier ID] = Tbl_Contract_Maintenance.[Supplier ID])
  3. GROUP BY Tbl_Supplier.[Supplier Name]
  4. union
  5. 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]
  6. FROM (Tbl_Supplier LEFT JOIN Tbl_Contract_General_Head ON Tbl_Supplier.[Supplier ID] = Tbl_Contract_General_Head.[Supplier ID]) 
  7. GROUP BY Tbl_Supplier.[Supplier Name]
  8. union
  9. 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]
  10. 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]
  11. GROUP BY Tbl_Supplier.[Supplier Name];
Can anyone please help me try to make sense of this?

Thanks
Feb 11 '08 #15

NeoPa
Expert Mod 15k+
P: 31,494
As I said before, I think using UNION for this is a restrictive way to proceed. However, if that's the way you intend to proceed you need to develop the individual queries first independently. When they are all working as expected, and in the same way as each other, you can then join them in a UNION query.
Feb 11 '08 #16

Post your reply

Sign in to post your reply or Sign up for a free account.