Join Multiple Queries and Collate Totals from Each

[naigy via AccessMonster.com]

Hi I have a few queries I want to join into one. My Queries are called plog1,
plog2 & plog3. All these queries have a column for servicedate & devicetype.
Each of the queries then has another column for totalclean, totalqa &
totalrepaired respectively. What I want to do is join these 3 queries into
one query which will list servicedate, devicetype, totalcleaned, totalqa &
totalrepaired. And where one of the devices has been qa'd but not repaired
the repaired column will show either 0 or nothing. Any advise how to achieve
this.

I have tried setting this up but the columns which don't have any values (in
theory) are displaying one. The fields totalcleaned, totalqa & totalrepaired
in the original queries are calculated using the count method. I suspect this
is what is causing my problem but can't seem to get around it.

I have tried using a crosstab query and this appears to work. But only for
one value (totalcleaned). I am unsure how I can add additional values.

Thanks in advance for any assistance.

 

[[MVP] S.Clark]

You could create a table with the following fields:

service date
device type
totalclean
totalqa
totalrepaired


After emptying the table, write all combinations of service date and device
type.

Next link this new table to Plog 1, and update the table field(clean) where
the date and type match.

Repeat with Plog2 and 3 with their respective fields.