combining queries that display totals

P

papa jonah

I have three queries that are similar and I want to combine them if
possible.
The first provides a list of organizations and a total of the number of
"subgroups" assigned to each one.
SELECT [Division group cross table].Division, Count(Subgroup.Subgroup)
AS [Num Subgroups]
FROM ([Data] LEFT JOIN (Division LEFT JOIN [Division group cross table]
ON Division.Div = [Division group cross table].[Group/Org]) ON
[Data].[ORPS Designator] = Division.[Designator]) INNER JOIN Subgroup
ON [Data].[Designator] = Subgroup.[Designator]
GROUP BY [Division group cross table].Division;

The result looks like
Org Num
B 4
C 3
LB 7
MX 1

The other two are similar except one limits the subgroups to those that
equal "10A2", and the other limits them to those equal to "10A3".
These are subsets of the total number displayed in the first query

What I would like is an output resembling
Org Total 10A2 10A3
B 4 1 1
C 3 0 1
LB 7 4 1
MX 1 0 0
 
P

papa jonah

Actually, I got this to work. Except where zeros should result, I get
blank spaces.
 
M

Marshall Barton

papa said:
I have three queries that are similar and I want to combine them if
possible.
The first provides a list of organizations and a total of the number of
"subgroups" assigned to each one.
SELECT [Division group cross table].Division, Count(Subgroup.Subgroup)
AS [Num Subgroups]
FROM ([Data] LEFT JOIN (Division LEFT JOIN [Division group cross table]
ON Division.Div = [Division group cross table].[Group/Org]) ON
[Data].[ORPS Designator] = Division.[Designator]) INNER JOIN Subgroup
ON [Data].[Designator] = Subgroup.[Designator]
GROUP BY [Division group cross table].Division;

The result looks like
Org Num
B 4
C 3
LB 7
MX 1

The other two are similar except one limits the subgroups to those that
equal "10A2", and the other limits them to those equal to "10A3".
These are subsets of the total number displayed in the first query

What I would like is an output resembling
Org Total 10A2 10A3
B 4 1 1
C 3 0 1
LB 7 4 1
MX 1 0 0
Click to expand...


Without see ing the other queries, I can't be sure, but I
think you can get those values in this query. Try adding
fields like:
Count(IIf(Subgroup.Subgroup = "10A2", 1, Null))

(the 1 can be any non-Null value)
 
P

papa jonah

Marsh,
What I understand your suggestion will do (I think you are suggesting
putting this in the criteria?) is counting the times that 10A2 is used
in the subgroup. My queries to that. The problem comes when I do the
left/right join queries and records that are blank in one table/query
show up as blank in the new query results. I need them to show up as
"0" instead of null for the sake of calculations (as demonstrated below
in qryProactive).
Most of them work. However, it appears that if a division shows up in
qry D and not in the others, none of the calculations happen for that
division.
Very odd it seems to me.

QryA
SELECT [Division group cross table].Division, Count(Subgroup.Subgroup)
AS [Num Subgroups]
FROM (([ORPS Data] LEFT JOIN Division ON [ORPS Data].[ORPS Designator]
= Division.[ORPS Designator]) INNER JOIN Subgroup ON [ORPS Data].[ORPS
Designator] = Subgroup.[ORPS Designator]) LEFT JOIN [Division group
cross table] ON Division.Div = [Division group cross table].[Group/Org]
GROUP BY [Division group cross table].Division;

QryB
SELECT [Division group cross table].Division, Count(Subgroup.Subgroup)
AS [Num 10A3s]
FROM ([ORPS Data] LEFT JOIN (Division LEFT JOIN [Division group cross
table] ON Division.Div = [Division group cross table].[Group/Org]) ON
[ORPS Data].[ORPS Designator] = Division.[ORPS Designator]) INNER JOIN
Subgroup ON [ORPS Data].[ORPS Designator] = Subgroup.[ORPS Designator]
WHERE (((Subgroup.Subgroup)="10a3"))
GROUP BY [Division group cross table].Division;

QryC
SELECT [Division group cross table].Division, Count(Subgroup.Subgroup)
AS [Num 10A2s]
FROM ([ORPS Data] LEFT JOIN (Division LEFT JOIN [Division group cross
table] ON Division.Div = [Division group cross table].[Group/Org]) ON
[ORPS Data].[ORPS Designator] = Division.[ORPS Designator]) INNER JOIN
Subgroup ON [ORPS Data].[ORPS Designator] = Subgroup.[ORPS Designator]
WHERE (((Subgroup.Subgroup)="10a2"))
GROUP BY [Division group cross table].Division;

QryD
SELECT [Division group cross table].Division, Count(Division.[ORPS
Designator]) AS num
FROM [ORPS Data] LEFT JOIN (Division LEFT JOIN [Division group cross
table] ON Division.Div = [Division group cross table].[Group/Org]) ON
[ORPS Data].[ORPS Designator] = Division.[ORPS Designator]
WHERE ((([ORPS Data].Wt)<>"1") AND (([ORPS
Data].Discovery_Date)>#10/1/2003#) AND (([ORPS Data].Status)<>"can"))
GROUP BY [Division group cross table].Division;

qryProactive
SELECT qryD.Division, qryD.num AS [Total Events],
Sum([qryd].[num]-[qryb].[Num 10a3s]-[qryc].[num 10a2s]) AS Events,
Sum(([qryd].[num]-[qryb].[Num 10a3s]-[qryc].[num 10a2s])/[qryd].[num])
AS [Events %], qryB.[Num 10A3s], Sum([qryb].[Num 10a3s]/[qryd].[num])
AS [Near Miss %], qryC.[Num 10A2s], Sum([qryc].[Num
10a2s]/[qryd].[num]) AS [Management Concern %]
FROM (qryD LEFT JOIN qryB ON qryD.Division = qryB.Division) LEFT JOIN
qryC ON qryD.Division = qryC.Division
GROUP BY qryD.Division, qryD.num, qryB.[Num 10A3s], qryC.[Num 10A2s]
ORDER BY qryD.num DESC , Sum(([qryd].[num]-[qryb].[Num
10a3s]-[qryc].[num 10a2s])/[qryd].[num]) DESC , Sum([qryb].[Num
10a3s]/[qryd].[num]) DESC , Sum([qryc].[Num 10a2s]/[qryd].[num]);
 
M

Marshall Barton

No,not in the criteria, Those were expressions for
calculated fields in the one query. I was hoping you could
get rid of all the confusion of separate queries joined in
that complicated way, but you have so much going on that I
can't determine if I was right or not.

The Null values that are supplied when a left join doesn't
have a matching record in the child table can be changed to
zero by using the NZ function.
Sum(Nz([qryd].[num], 0) - Nz([qryb].[Num 10a3s],0) - ...


papa said:
What I understand your suggestion will do (I think you are suggesting
putting this in the criteria?) is counting the times that 10A2 is used
in the subgroup. My queries to that. The problem comes when I do the
left/right join queries and records that are blank in one table/query
show up as blank in the new query results. I need them to show up as
"0" instead of null for the sake of calculations (as demonstrated below
in qryProactive).
Most of them work. However, it appears that if a division shows up in
qry D and not in the others, none of the calculations happen for that
division.
Very odd it seems to me.

QryA
SELECT [Division group cross table].Division, Count(Subgroup.Subgroup)
AS [Num Subgroups]
FROM (([ORPS Data] LEFT JOIN Division ON [ORPS Data].[ORPS Designator]
= Division.[ORPS Designator]) INNER JOIN Subgroup ON [ORPS Data].[ORPS
Designator] = Subgroup.[ORPS Designator]) LEFT JOIN [Division group
cross table] ON Division.Div = [Division group cross table].[Group/Org]
GROUP BY [Division group cross table].Division;

QryB
SELECT [Division group cross table].Division, Count(Subgroup.Subgroup)
AS [Num 10A3s]
FROM ([ORPS Data] LEFT JOIN (Division LEFT JOIN [Division group cross
table] ON Division.Div = [Division group cross table].[Group/Org]) ON
[ORPS Data].[ORPS Designator] = Division.[ORPS Designator]) INNER JOIN
Subgroup ON [ORPS Data].[ORPS Designator] = Subgroup.[ORPS Designator]
WHERE (((Subgroup.Subgroup)="10a3"))
GROUP BY [Division group cross table].Division;

QryC
SELECT [Division group cross table].Division, Count(Subgroup.Subgroup)
AS [Num 10A2s]
FROM ([ORPS Data] LEFT JOIN (Division LEFT JOIN [Division group cross
table] ON Division.Div = [Division group cross table].[Group/Org]) ON
[ORPS Data].[ORPS Designator] = Division.[ORPS Designator]) INNER JOIN
Subgroup ON [ORPS Data].[ORPS Designator] = Subgroup.[ORPS Designator]
WHERE (((Subgroup.Subgroup)="10a2"))
GROUP BY [Division group cross table].Division;

QryD
SELECT [Division group cross table].Division, Count(Division.[ORPS
Designator]) AS num
FROM [ORPS Data] LEFT JOIN (Division LEFT JOIN [Division group cross
table] ON Division.Div = [Division group cross table].[Group/Org]) ON
[ORPS Data].[ORPS Designator] = Division.[ORPS Designator]
WHERE ((([ORPS Data].Wt)<>"1") AND (([ORPS
Data].Discovery_Date)>#10/1/2003#) AND (([ORPS Data].Status)<>"can"))
GROUP BY [Division group cross table].Division;

qryProactive
SELECT qryD.Division, qryD.num AS [Total Events],
Sum([qryd].[num]-[qryb].[Num 10a3s]-[qryc].[num 10a2s]) AS Events,
Sum(([qryd].[num]-[qryb].[Num 10a3s]-[qryc].[num 10a2s])/[qryd].[num])
AS [Events %], qryB.[Num 10A3s], Sum([qryb].[Num 10a3s]/[qryd].[num])
AS [Near Miss %], qryC.[Num 10A2s], Sum([qryc].[Num
10a2s]/[qryd].[num]) AS [Management Concern %]
FROM (qryD LEFT JOIN qryB ON qryD.Division = qryB.Division) LEFT JOIN
qryC ON qryD.Division = qryC.Division
GROUP BY qryD.Division, qryD.num, qryB.[Num 10A3s], qryC.[Num 10A2s]
ORDER BY qryD.num DESC , Sum(([qryd].[num]-[qryb].[Num
10a3s]-[qryc].[num 10a2s])/[qryd].[num]) DESC , Sum([qryb].[Num
10a3s]/[qryd].[num]) DESC , Sum([qryc].[Num 10a2s]/[qryd].[num]);
Click to expand...
 
P

papa jonah

I attempted inserting the "NZ", the parentheses, and the ",0" in the
expressions of the actual query that displays the final table. In some
places, I get an overflow error such as when I created the following:
Near Miss %: Sum(nz([qryb].[Num 10a3s],0)/[qryd].[num])

However, the following worked:
Events: Sum(nz([qryd].[num],0)-nz([qryb].[Num 10a3s],0)-nz([qryc].[num
10a2s],0))

This provides the proper numbers (except zeros only appear in the
events column now.

So what I have done is I changed one of the columns (query design view)
from a "group by" to a "expression" and I changed the field to a
summation based on your input. That works good. Now I want to do a
calculation based on that to calculate a percentage in the next column.
How do I do a calculation referencing another field?
 
M

Marshall Barton

papa said:
I attempted inserting the "NZ", the parentheses, and the ",0" in the
expressions of the actual query that displays the final table. In some
places, I get an overflow error such as when I created the following:
Near Miss %: Sum(nz([qryb].[Num 10a3s],0)/[qryd].[num])

However, the following worked:
Events: Sum(nz([qryd].[num],0)-nz([qryb].[Num 10a3s],0)-nz([qryc].[num
10a2s],0))

This provides the proper numbers (except zeros only appear in the
events column now.

So what I have done is I changed one of the columns (query design view)
from a "group by" to a "expression" and I changed the field to a
summation based on your input. That works good. Now I want to do a
calculation based on that to calculate a percentage in the next column.
How do I do a calculation referencing another field?
Click to expand...


I think I'm getting lost here, but I'll try. You can
usually(?) just refer to the other column by it's name.
E.g. SELECT A, B, 2*A+B As X, C + X As Y . . .

But you can not refer to a calculated field's name in the
GROUP BY or ORDER BY clauses. If it doesn't like using a
name, just use the entire expression instead.

You got an Overflow error in your Near Miss expression
because the denominator had a value of zero. If there might
be records with where num is zero, your expression should
check for that case. Maybe this is an acceptable way to
deal with it:

Near Miss %: Sum(nz(qryb.[Num 10a3s],0)/Nz[qryd.num,1))

If not please explain what you do want to happen when Num is
zero.
 
P

papa jonah

Marsh,
In order to minimize confusion in my limited brain, lets firs address
teh overflow eror. As qryD counts the number of events and groups by
divisions, I don't think I should (and at least don't currently) have
any division appearing with zero counts. Therefore, I should not have
any division by zero errors.
Does that make sense, or do I not understand? Like I said, whether it
is by design or not, I do not have any zeros in the qryD results.

qryD
SELECT [Division group cross table].Division, Count(Division.[ORPS
Designator]) AS num
FROM [ORPS Data] LEFT JOIN (Division LEFT JOIN [Division group cross
table] ON Division.Div = [Division group cross table].[Group/Org]) ON
[ORPS Data].[ORPS Designator] = Division.[ORPS Designator]
WHERE ((([ORPS Data].Wt)<>"1") AND (([ORPS
Data].Discovery_Date)>#10/1/2003#) AND (([ORPS Data].Status)<>"can"))
GROUP BY [Division group cross table].Division;
 
M

Marshall Barton

papa said:
In order to minimize confusion in my limited brain, lets firs address
teh overflow eror. As qryD counts the number of events and groups by
divisions, I don't think I should (and at least don't currently) have
any division appearing with zero counts. Therefore, I should not have
any division by zero errors.
Does that make sense, or do I not understand? Like I said, whether it
is by design or not, I do not have any zeros in the qryD results.

qryD
SELECT [Division group cross table].Division, Count(Division.[ORPS
Designator]) AS num
FROM [ORPS Data] LEFT JOIN (Division LEFT JOIN [Division group cross
table] ON Division.Div = [Division group cross table].[Group/Org]) ON
[ORPS Data].[ORPS Designator] = Division.[ORPS Designator]
WHERE ((([ORPS Data].Wt)<>"1") AND (([ORPS
Data].Discovery_Date)>#10/1/2003#) AND (([ORPS Data].Status)<>"can"))
GROUP BY [Division group cross table].Division;
Click to expand...


Are you sure? Try running qryD by itself and look for a 0
somewhere in the Num column.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

number of events 2
Combining 3 queries 2
combining queries into 1 8
Bug in Access Execution plan ? 1
Combine Related Queries ??? 2
Heavy data munching - is there a more efficient way of doing this? 8
Report totals off by a couple cents. 4
MS Access Join Query 2

Top