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
15  1556 
Have you got a supplierID = 0 set to Not Known?
Have you got a supplierID = 0 set to Not Known?
Yep.
Thanks
JD.
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.
Can anyone else shed any light on this problem?
Thanks
JD
-
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.
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 32,556
Expert Mod 16PB
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
Good catch guys!
I missed the logic on that one.
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.
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
Yes, you would do a separate query for each type of contract and then union the results together.
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 32,556
Expert Mod 16PB
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.
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
NeoPa 32,556
Expert Mod 16PB
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: netpurpose |
last post by:
I need to extract data from this table to find the lowest prices of
each product as of today. The product will be listed/grouped by the
name only, discarding the product code - I use...
|
by: Simon Bailey |
last post by:
How do you created a query in VB?
I have a button on a form that signifies a certain computer in a
computer suite. On clicking on this button i would like to create a
query searching for all...
|
by: d.p. |
last post by:
Hi all,
I'm using MS Access 2003.
Bare with me on this description....here's the situation: Imagine insurance,
and working out premiums for different insured properties. The rates for
calculating...
|
by: Alan Lane |
last post by:
Hello world:
I'm including both code and examples of query output. I appologize if
that makes this message longer than it should be.
Anyway, I need to change the query below into a pivot table...
|
by: Liam.M |
last post by:
hey guys,
I have one last problem to fix, and then my database is essentially
done...I would therefore very much appreciate any assistance anyone
would be able to provide me with.
Currently I...
|
by: elitecodex |
last post by:
Hey everyone. I have this query
select * from `TableName` where `SomeIDField` 0
I can open a mysql command prompt and execute this command with no
issues. However, Im trying to issue the...
|
by: aaronrm |
last post by:
I have a real simple cross-tab query that I am trying to sum on as the
action but I am getting the "data type mismatch criteria expression"
error. About three queries up the food chain from this...
|
by: funky |
last post by:
hello,
I've got a big problem ad i'm not able to resolve it. We have a server
running oracle 10g version 10.1.0. We usually use access as front end
and connect database tables for data extraction....
|
by: Doris |
last post by:
It does not look like my message is posting....if this is a 2nd or 3rd
message, please forgive me as I really don't know how this site works.
I want to apologize ahead of time for being a novice...
|
by: jsacrey |
last post by:
Hey everybody, got a secnario for ya that I need a bit of help with.
Access 97 using linked tables from an SQL Server 2000 machine.
I've created a simple query using two tables joined by one...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |
