Is there a better way?

[Leslie Coover]

I have Query1 with the following relationships:

tblA has 1 to many relationships with tblB and tblC based on Field XA

This is a Total query with several By Group and Sum fields

I need the sum of XZ in tblC. The only wasy I could figure out how to do
this is create a separate query (Query2)

SELECT tblC.XA, Sum(tblC.XZ) AS SumOfXZ
FROM tblC
GROUP BY tblC.XA

then include it in Query1 as:

Field = SumOfXA
Table = Query2
Total = Group By

To make a long story short there are many XAs in tblC that have to be summed
but Query1 is allready set up by Group By XA in tblA

Is there a better way?

 

[Gary Walter]

I have Query1 with the following relationships:

tblA has 1 to many relationships with tblB and tblC based on Field XA

This is a Total query with several By Group and Sum fields

I need the sum of XZ in tblC. The only wasy I could figure out how to do
this is create a separate query (Query2)

SELECT tblC.XA, Sum(tblC.XZ) AS SumOfXZ
FROM tblC
GROUP BY tblC.XA

then include it in Query1 as:

Field = SumOfXA
Table = Query2
Total = Group By

To make a long story short there are many XAs in tblC that have to be
summed but Query1 is allready set up by Group By XA in tblA

Is there a better way?

Hi Leslie,

For me, the "best" way is always the way
that allows me to understand what I was
doing when I have to tinker with it 6 months
down the road.

I like your "divide-and-conquer" method
for that very reason, especially if the prelim
query is well named.

Another method (if I understand correctly)
is to use a correlated subquery
in Query1 to provide the sums from tblC...

SELECT
tblA.XA,
(SELECT
Sum(C.XZ)
FROM
tblC As C
WHERE
C.XA = tblA.XA) As CXZSum
FROM
tblA
GROUP BY
tblA.XA;

given any XA (say XA = 1), you could probably
create a totals query from tblC to sum XZ.

SELECT
Sum(C.XZ)
FROM
tblC As C
WHERE
C.XA =1

this would be your "uncorrelated query"

well, each time Access is looking at
an "XA group" in the main query, we'd
like to use that tblA.XA value in our
subquery (instead of "1").

So, we wrap the subquery in parenthesis,
stick it in the main query SELECT clause
(like a "field"), and *correlate* it back to
the main query by replacing "1" with "tblA.XA"