Query on multiple child tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DLW - 13 Jun 2007 04:43 GMT

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

Reply to this Message

John W. Vinson - 13 Jun 2007 06:36 GMT

>Two similar but unrelated child tables: Tasks, Events. Each of these
>two has a number field for "weight", which is the presumed importance
[quoted text clipped - 4 lines]
>points across ALL tasks and events for a given MarketingObjective.
>This must be something simple, but...

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]

Reply to this Message