Sum Query is Multiplying End Results

[Jennifer Cali]

Hi - I have a strange one! My query below isn't working correctly. I have a
main events table and the event number is joined to one of three other
subtables (table AV, tblScenic, and tblLighting). The query does run, but if
I have more than one entry for an event in a given table, it is multiplying
the end result by 6, 8, etc. (it appears to be random).


For example, tblScenic has two entries tied to event number 4:
Palm Trees $300
Music $120

The footer holds the total of $420.

I then created the SQL below to pull values from these tables based on the
event number and sum the values so that I could see, by event, the total
costs for AV, Scenic, and Lighting. The problem is that the SUM is only
correct if there is ONE value in the Scenic table...when I have more than one
(like in the above example) it is multiplying it by 8 (for some reason!)
because the result of the query showing the total cost of Scenic items for
event number four comes out as $3360 instead of $420. Any help would be
greatly appreciated - I have no idea what's wrong with my SQL. You'll see two
different queries below...both yield the same result.

OPTION #1:
SELECT
tblEventSetup.atnEventSetup,
Sum(tblAV.curAVEstimateBudget) AS [Sum Of curAVEstimateBudget],
Sum(tblAV.curAVActualCost) AS [Sum Of curAVActualCost],
Sum(tblScenic.curScenicEstimateBudget) AS [Sum Of curScenicEstimateBudget],
Sum(tblScenic.curScenicActualCost) AS [Sum Of curScenicActualCost],
Sum(tblLighting.curLightEstimateBudget) AS [Sum Of curLightEstimateBudget],
Sum(tblLighting.curLightActualCost) AS [Sum Of curLightActualCost]
FROM tblEventSetup, tblAV, tblLighting, tblScenic
where tblEventSetup.atnEventSetup = tblAV.ingzEventAV and
tblEventSetup.atnEventSetup = tblLighting.ingzEventLight and
tblEventSetup.atnEventSetup = tblScenic.ingzEventScenic
GROUP BY tblEventSetup.atnEventSetup;



OPTION #2:
SELECT DISTINCTROW tblEventSetup.atnEventSetup,
Sum(tblAV.curAVEstimateBudget) AS [Sum Of curAVEstimateBudget],
Sum(tblAV.curAVActualCost) AS [Sum Of curAVActualCost],
Sum(tblScenic.curScenicEstimateBudget) AS [Sum Of curScenicEstimateBudget],
Sum(tblScenic.curScenicActualCost) AS [Sum Of curScenicActualCost],
Sum(tblLighting.curLightEstimateBudget) AS [Sum Of curLightEstimateBudget],
Sum(tblLighting.curLightActualCost) AS [Sum Of curLightActualCost]
FROM ((tblEventSetup INNER JOIN tblAV ON tblEventSetup.atnEventSetup =
tblAV.ingzEventAV) INNER JOIN tblLighting ON tblEventSetup.atnEventSetup =
tblLighting.ingzEventLight) INNER JOIN tblScenic ON
tblEventSetup.atnEventSetup = tblScenic.ingzEventScenic
GROUP BY tblEventSetup.atnEventSetup;

 

[John Vinson]

Hi - I have a strange one! My query below isn't working correctly. I have a
main events table and the event number is joined to one of three other
subtables (table AV, tblScenic, and tblLighting). The query does run, but if
I have more than one entry for an event in a given table, it is multiplying
the end result by 6, 8, etc. (it appears to be random).

It's not at all random. Count records in the related tables. If you
have two records in AV, two in tblScenic, and three in tblLighting,
you'll get ALL 12 (2 * 2 * 3) possible combinations, since there is no
relationship between the "child" tables.

I'd use three Subforms and sum the costs separately for each, and then
get a grand total. You can't do it all in one query because you don't
(and can't!) have a relationship.

John W. Vinson[MVP]

 

[Jennifer Cali]

Hi John,
That sounds great...but how can I get the total values from three separate
subforms to roll into a grand total? I'm not clear on how that is going to be
done.

 

[John Vinson]

Hi John,
That sounds great...but how can I get the total values from three separate
subforms to roll into a grand total? I'm not clear on how that is going to be
done.

You need two steps. On each Subform's Footer put a textbox names (say)
txtSumAV, with a control source

=Sum([fieldname])

to get the subtotal for that subform's data.

Then on the mainform put another textbox with a control source like

=subAV.Form!txtSumAV + subScenic.Form!txtSumScenic +
subLighting.Form!txtSumLighting

to sum up the individual values.

Alternatively, you can go to the tables directly, using DSum() to sum
up selected records' data.

John W. Vinson[MVP]

 

[Jennifer Cali]

Thank you, John, this is wonderful. I used your first suggestion.

I've done the DLookup before, but not a DSum. I'm anxious to try this one,
too.
--
Thank you! - Jennifer

Hi John,
That sounds great...but how can I get the total values from three separate
subforms to roll into a grand total? I'm not clear on how that is going to be
done.

You need two steps. On each Subform's Footer put a textbox names (say)
txtSumAV, with a control source

=Sum([fieldname])

to get the subtotal for that subform's data.

Then on the mainform put another textbox with a control source like

=subAV.Form!txtSumAV + subScenic.Form!txtSumScenic +
subLighting.Form!txtSumLighting

to sum up the individual values.

Alternatively, you can go to the tables directly, using DSum() to sum
up selected records' data.

John W. Vinson[MVP]

 

[çï]

àúí îåëðéí ìéìåç áòéáøéú?