Outer Join?

[mr-tom]

Hello,

Got a bit of a problem.

I've got two tables which hold results of tests (one current, one historic
and yes it was too much to hope for that they'd move from one to the other at
year end).

You can probably see where this is going.

I have queries of the old and new tables which aggregate the data to give
"compatible" results.

Name, year and month (time month key), then the number of cases and the sum
of the score, e.g.

Old Table Query Output:
Who When Cases Sum of Score
Tom 200901 5 10
Tom 200902 5 10
Tom 200903 3 6
Kim 200901 5 10
Kim 200902 5 10
Kim 200903 3 6

New Table Query Output:
Kim 200903 2 4
Kim 200904 5 10
Kim 200905 5 10
Tom 200903 2 4
Tom 200904 5 10
Tom 200905 5 10

I want to join these in such a way that I get all records for both
regardless of whether there's a match in the corresponding table (for the
purposes of this example we'll join on name)

This would give a final query output of:
Tom 200901 5 10
Tom 200902 5 10
Tom 200903 3 6
Kim 200901 5 10
Kim 200902 5 10
Kim 200903 3 6
Kim 200903 2 4
Kim 200904 5 10
Kim 200905 5 10
Tom 200903 2 4
Tom 200904 5 10
Tom 200905 5 10


Which I would then group and sum to:

Tom 200901 5 10
Tom 200902 5 10
Kim 200901 5 10
Kim 200902 5 10
Kim 200903 5 10
Kim 200904 5 10
Kim 200905 5 10
Tom 200903 5 10
Tom 200904 5 10
Tom 200905 5 10

The problem is that I can't work out how to join the queries in such a way
that I can see and return all records.

Wondering how I go about doing this - how would I structure the query / SQL
and do I need an outer join?

Cheers,

Tom.

 

[KARL DEWEY]

No join, use an union query.

 

[mr-tom]

Thanks Karl, I'll give that a try.

Tom.

No join, use an union query.

 

[John Spencer]

Look up UNION query to combine the two tabls into one. Union queries can only
be built in SQL view. The SQL would look something like this

SELECT Who, When, Cases, [Sum of Score]
FROM [OldTable]
UNION ALL
SELECT Who, When, Cases, [Sum of Score]
FROM [NewTable]

Or you can use your two existing queries. Just replace OldTable and NewTable
with the names of the queries.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County