435,039 Members | 1,831 Online
+ 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 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 Feb 5 '08 #1
15 Replies

 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

 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

 Expert 2.5K+ P: 2,653 Expand|Select|Wrap|Line Numbers 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;   [ 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

 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 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 Feb 6 '08 #8

 Expert Mod 10K+ P: 14,534 Good catch guys! I missed the logic on that one. Feb 6 '08 #9

 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 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. 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

 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

 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

 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