Summing the results of 2 Queries with a third

[datas_brother]

HI,

I'm trying to find the sum of inspections per inspector and area. Each inspector works either alone or in a team of 2. I have a table with the fields Inspector_1 and Inspector_2. I've written 2 queries that give me the correct sums by inspector and area (one for each field above). The number of rows in the first query is higher since not all inspectors work in multiple areas and in teams. When I try to combine both queries into one I am only showing entries for inspectors that have worked as inspector_2 in a particular area. I. e. if an inspector in area A has never worked as Inspector_2 in that area he will not be returned.

Here is the code for the third query:

SELECT Item_Count_Insp1.Inspector, [Item_Count_Insp1]![SKU_Count]+[Item_Count_Insp2]![SKU_Count] AS SKU_Sum, Item_Count_Insp2.[QA Area]
FROM Item_Count_Insp1 INNER JOIN Item_Count_Insp2 ON (Item_Count_Insp1.[QA Bereich] = Item_Count_Insp2.[QA Bereich]) AND (Item_Count_Insp1.Inspector= Item_Count_Insp2.Inspector)
GROUP BY Item_Count_Insp1.Inspector, [Item_Count_Insp1]![SKU_Count]+[Item_Count_Insp2]![SKU_Count], Item_Count_Insp2.[QA Bereich]
ORDER BY Item_Count_Insp2.[QA Area];

I'm likely overlooking something really simple and would appreciate any help I can get.

Thanks:

DB

 

[namliam]

You should probably change your INNER JOIN to a LEFT OUTER JOIN (or RIGHT OUTER JOIN, depending on how you build it.

 

[datas_brother]

HI,

Thanks for the help. Left outer join is a step in the right direction. Now the missing inspectors are included but the QA Area and the sum of SKU_Count are empty.

Cheers
DB

 

[datas_brother]

Thanks to the help above I've managed to solve the first issue but now I've found a second problem. To get the total number of inspections per inspector I have a query which sums 2 other queries:

First the number of inspections for each inspector is counted where he is inspector #1 and there is an inspector #2

SELECT Item_Count.[Prüfername #1] AS Prüfer, Count(Item_Count![SKU]) AS SKU, Item_Count.[QA Bereich]
FROM Item_Count
WHERE (((Item_Count.[Prüfername #2])<>""))
GROUP BY Item_Count.[Prüfername #1], Item_Count.[QA Bereich];

The second query counts the number of inspections where either there is no Insp. #2 or he is Insp. #2

SELECT IIf(Item_Count![Prüfername #2]<>"",Item_Count![Prüfername #2],Item_Count![Prüfername #1]) AS Prüfer, Count(Item_Count!SKU) AS SKU, Item_Count.[QA Bereich], Item_Count.[Prüfername #2]
FROM Item_Count
GROUP BY IIf(Item_Count![Prüfername #2]<>"",Item_Count![Prüfername #2],Item_Count![Prüfername #1]), Item_Count.[QA Bereich], Item_Count.[Prüfername #2];

Both of these queries return correct values. The third only adds the values of the other 2 together.

SELECT Item_Count_Insp1.Prüfer, Item_Count_Insp1.[QA Bereich], Sum(Item_Count_Insp1!SKU+Item_Count_Insp2!SKU) AS SKU_Sum
FROM Item_Count_Insp1 LEFT JOIN Item_Count_Insp2 ON (Item_Count_Insp1.[QA Bereich] = Item_Count_Insp2.[QA Bereich]) AND (Item_Count_Insp1.Prüfer = Item_Count_Insp2.Prüfer)
GROUP BY Item_Count_Insp1.Prüfer, Item_Count_Insp1.[QA Bereich]
ORDER BY Item_Count_Insp1.[QA Bereich];

This query returns an odd value. It counts each of the entires from the first query twice. The results look like this: Q1 = 500, Q2 = 25 and Q3 = 1025.

Any ideas what I'm doing wrong?

Thanks
DB

 

[namliam]

This is one of the reasons why if you have 2 inspector's you should have it in a related table not in 2 columns.... If it were in a seperate table ... this would be easy.

Your second query is totaly flawed, but going into that is mute... because I am going to suggest a totaly different solution
1) You should consider redesigning this....
Failing that...

2) Use a union query:

SELECT Item_Count.[Prüfername #1] AS Prüfer, Item_Count![SKU], Item_Count.[QA Bereich] FROM Item_Count WHERE Item_Count.[Prüfername #1] IS NOT NULL
UNION ALL
SELECT Item_Count.[Prüfername #2] AS Prüfer, Item_Count![SKU], Item_Count.[QA Bereich] FROM Item_Count WHERE Item_Count.[Prüfername #2] IS NOT NULL

Save this union as a query object named qryAllInspectors
Now do your count on this query:

SELECT Prüfer, Count([SKU]) AS SKUCount, [QA Bereich]
FROM   qryAllInspectors
GROUP BY Prüfer, [QA Bereich];

3)
I will not even go into a rant about using special chars or spaces in any table/query/column names...

 

[datas_brother]

Thanks. That works perfectly. I've never worked with union queries before so this was new to me.

I know using spaces and German letters is wrong and a bad habit I should break. In this case the names come from a lotus DB export and I was too lazy to change them.

I can't separate the data into 2 tables because if I did I would have to double the size of am DB, which is already running at 25k lines per year.

Why is the second query flawed? it seems to work fine.

Thanks again for your help.

Cheers
DB

 

[namliam]

For one, you dont mean <> "" you mean IS NOT NULL
Or at the very least you inexplicitly mean that .... <> "" is another bad habit...

The reason query to is seriously flawed...
WHERE (((Item_Count.[Prüfername #2])<>""))
Is part of query 1 however in query 2 you do
IIf(Item_Count![Prüfername #2]<>"",Item_Count![Prüfername #2],Item_Count![Prüfername #1])
without any where clause this is the base reason your counts to off

If you would redesign your table/db you wouldnt double your size, most likely you would REDUCE the size...
Right now your table looks like

Key Description Inspector 1 Inspector 2 ...
1   Job 1       Namliam
2   Job 2       Namliam     Datas
3   Job 3       Namliam     Brother
4   Job 4                   Namliam
5   Job 5       Someone     Namliam
6   Job 6       Datas       Namliam
7   Job 7       nobody      Namliam

Instead it should look like:

Key Description ...
1   Job 1       
2   Job 2       
3   Job 3       
4   Job 4       
5   Job 5       
6   Job 6       
7   Job 7       

XID  JobID IID
1    1     1     
2    2     1
3    2     2
4    3     1 
5    3     3  
6    4     1  
7    5     4  
8    5     1  
9    6     2  
10   6     1
11   7     5
12   7     1     
           
IID  Inspector
1    Namliam
2    Datas
3    Brother
4    Someone
5    nobody

a result of which is much more dynamic and much more flexible and much more indicative to change...

Then again if your just running reports... I am sure that in the root system though it is being stored like that.

 

[datas_brother]

HI

First, thank you for your help. In my defense, although I've been working with Access for 10 years, now, almost everything I know I learned by pressing F1.

I've designed a couple of DB where I did actually follow that type of structure. In this case however, the raw data are coming from another system in the form of an Excel export. I'm just importing the file using the wizard and appending them with a query. It was the easy road that I'm now paying for. I should have structured it better and written an SQL routine to put the data where it belongs but now there is no return.

The reason for the IIF statement was that the third query was not showing the number of items for inspectors that never listed as Insp.2. It let me put the name of inspector 1 in if there was no inspector 2 and count the number of inspections. I know it's not the most eloquent way of doing it but it returned correct results. In fact both queries did but adding them didn't (and I still don't know why).

In any case, I very much appreciate your help. I'll have to go back now and read up on union queries.

Cheers
DB