Full / outer join in access

[HS Hartkamp]

Hi all,

I have a table with analysis-results for various months. An item can -for a
particular month- have the result 'list1', 'list2' or 'list3' depending on
the result of the analysis.

Next, I want to get an insight in how the various items change over time.
For that, I compare the results of one month with the results of another
month. The form in which I want this is a cross-tab-query with one month as
a row-header and another month as the column-header. This results in a 3x3
table with 'list 1/2/3' as both column-headings and row-headings. The
'value' is the count of items that were in those lists in those particular
months.

To do this,
I created two queries for each of the months I want compared, and based the
cross-tab table on those two queries.
If the join in the two queries is an inner join, I get the table with all
items that occured in both months (3x3 table).
If I use a left join, I also get the items that occured in one month, but
not the next (4x3 table with row-label null; i.e. items that disappeared
from the analysis also show)
If I use a right join, I also get all items that occured in the next month,
but not in the first (3x4 table with column-label null; i.e. items that
appeared into the analysis also show)

Is there a way to have _both_ the appearing and disappearing items as a
result included in the cross-tab query (resulting in a 4x4 table) ?

Thanks in advance,

Bas Hartkamp.

[Bob Quintal]

"HS Hartkamp" <hs*********@hccnet.nl> wrote in
news:41***********************@news.wanadoo.nl:

Hi all,

I have a table with analysis-results for various months. An
item can -for a particular month- have the result 'list1',
'list2' or 'list3' depending on the result of the analysis.

Next, I want to get an insight in how the various items change
over time. For that, I compare the results of one month with
the results of another month. The form in which I want this is
a cross-tab-query with one month as a row-header and another
month as the column-header. This results in a 3x3 table with
'list 1/2/3' as both column-headings and row-headings. The
'value' is the count of items that were in those lists in
those particular months.

To do this,
I created two queries for each of the months I want compared,
and based the cross-tab table on those two queries.
If the join in the two queries is an inner join, I get the
table with all items that occured in both months (3x3 table).
If I use a left join, I also get the items that occured in one
month, but not the next (4x3 table with row-label null; i.e.
items that disappeared from the analysis also show)
If I use a right join, I also get all items that occured in
the next month, but not in the first (3x4 table with
column-label null; i.e. items that appeared into the analysis
also show)

Is there a way to have _both_ the appearing and disappearing
items as a result included in the cross-tab query (resulting
in a 4x4 table) ?

Thanks in advance,

Bas Hartkamp.

My technique to get this is to create a union query of the field
(s) that is the key into the two tables. Then I join this to the
two other queries using two left joins.

--
Bob Quintal

PA is y I've altered my email address.

[HS Hartkamp]

"Bob Quintal" <rq******@sPAmpatico.ca> wrote in message
news:1097333430.6R9MF9hUZl5yW66p4Aov2g@teranews...

"HS Hartkamp" <hs*********@hccnet.nl> wrote in
news:41***********************@news.wanadoo.nl:

Hi all,

My technique to get this is to create a union query of the field
(s) that is the key into the two tables. Then I join this to the
two other queries using two left joins.

Thanks ! Allways nice to be able to do something that the designers did not
put into the program.

It works excellent!

Bas.