Query on multiple child tables

D

DLW

Hi, all.
First time here. Am trying to learn Access 2007, and I have been stuck
on something for days.

Parent table: MarketingObjective
Two similar but unrelated child tables: Tasks, Events. Each of these
two has a number field for "weight", which is the presumed importance
of each to reaching the objective. I am trying to total all weighting
points to give a picture of relative importance.
I can query Tasks okay, and Events okay. But I cannot seem to figure
out how to combine the two and get a grand total of ALL weighting
points across ALL tasks and events for a given MarketingObjective.
This must be something simple, but...
Any help is much appreciated. Thank you.
DLW
 
J

John W. Vinson

Two similar but unrelated child tables: Tasks, Events. Each of these
two has a number field for "weight", which is the presumed importance
of each to reaching the objective. I am trying to total all weighting
points to give a picture of relative importance.
I can query Tasks okay, and Events okay. But I cannot seem to figure
out how to combine the two and get a grand total of ALL weighting
points across ALL tasks and events for a given MarketingObjective.
This must be something simple, but...
Click to expand...

You'll probably need to use a UNION query to prevent "cross joins":

SELECT Weight FROM Tasks WHERE MarketingObjective = <whatever>
UNION ALL
SELECT Weight FROM Events WHERE MarketingObjective = <whatever>;

Save this query and then base a Totals query on it.

John W. Vinson [MVP]
 
Top