By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,806 Members | 1,457 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,806 IT Pros & Developers. It's quick & easy.

Full / outer join in access

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"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.
Nov 13 '05 #2

P: n/a

"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.

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.