Query help

[Matt Dawson]

I am currently trying to design a query which brings data out of three
tables. I want it to show a list of all the records in Table A and then ones
that match those in table B and table C. However, I also want to show those
that dont appear in either so that i have a total number of records.
I thought of using something like this IIf(IsNull([Posted].[Contract
ID]),'N','Y'). But it only brings up those in the other tables.

Any ideas,
Thanks
Matt

 

[kingston via AccessMonster.com]

Check the join properties in your query. You can show all of the records
from Table A and only the corresponding records in Tables B and C.

I am currently trying to design a query which brings data out of three
tables. I want it to show a list of all the records in Table A and then ones
that match those in table B and table C. However, I also want to show those
that dont appear in either so that i have a total number of records.
I thought of using something like this IIf(IsNull([Posted].[Contract
ID]),'N','Y'). But it only brings up those in the other tables.

Any ideas,
Thanks
Matt

 

[Matt Dawson]

I understand that but I need to show every single record from A, then some
will not be in B or C and need those to be shown too not just corresponding
records from B and C.

Check the join properties in your query. You can show all of the records
from Table A and only the corresponding records in Tables B and C.

I am currently trying to design a query which brings data out of three
tables. I want it to show a list of all the records in Table A and then ones
that match those in table B and table C. However, I also want to show those
that dont appear in either so that i have a total number of records.
I thought of using something like this IIf(IsNull([Posted].[Contract
ID]),'N','Y'). But it only brings up those in the other tables.

Any ideas,
Thanks
Matt

 

[kingston via AccessMonster.com]

If you specify that the join type should return all records from A, every
single record in A will be shown even if there are no corresponding records
in B or C. Are you doing this visually or with an SQL string? In a visual
query, the join line should show an arrowhead at one end of the line. In SQL,
use LEFT JOIN or RIGHT JOIN. There is no support for full OUTER JOIN in
Access. You'll have to create that functionality with what's available.

I understand that but I need to show every single record from A, then some
will not be in B or C and need those to be shown too not just corresponding
records from B and C.

Check the join properties in your query. You can show all of the records
from Table A and only the corresponding records in Tables B and C.

[quoted text clipped - 9 lines]

 

[Matt Dawson]

Well if i use the join saying pull all from A and only those matchign from B
and C it gievs me over 100000 records when there aer only 8900 in table A.

How do i create a full outer join?

Matt

If you specify that the join type should return all records from A, every
single record in A will be shown even if there are no corresponding records
in B or C. Are you doing this visually or with an SQL string? In a visual
query, the join line should show an arrowhead at one end of the line. In SQL,
use LEFT JOIN or RIGHT JOIN. There is no support for full OUTER JOIN in
Access. You'll have to create that functionality with what's available.

I understand that but I need to show every single record from A, then some
will not be in B or C and need those to be shown too not just corresponding
records from B and C.

Check the join properties in your query. You can show all of the records
from Table A and only the corresponding records in Tables B and C.

[quoted text clipped - 9 lines]

Thanks
Matt

 

[kingston via AccessMonster.com]

A full outer join will give you all the records in A, B, and C. So if 100000
records already is too much, a full outer join is not for you. Please
explain the structure and number of records of your tables and your desired
result. How are you formulating the query?

First, to show all the records in A and only corresponding records in B,
create a query with only one join (AKA LEFT OUTER JOIN - look up "join type"
in Access Help). The resulting record count should equal the record count in
A if B contains only one or no matching record for every record in A and one
of the output fields is the key field from A. If you get a record count of
100000, you have approximately 11 records in B for every record in A or you
did not create the query properly. Make sure you get the correct result with
A and B, and then do the same thing with A and C.

In order to get all the records in A and only the corresponding records in B
and C, create only two joins in your query. Both joins will be from A. The
first one will be a LEFT OUTER JOIN from A to B. The second one will be a
LEFT OUTER JOIN form A to C. Do not create any more joins. If both B and C
have one or no matching records for every record in A and one of the outputs
is the key from A, the query should return exactly the same number of records
as in A.

Well if i use the join saying pull all from A and only those matchign from B
and C it gievs me over 100000 records when there aer only 8900 in table A.

How do i create a full outer join?

Matt

If you specify that the join type should return all records from A, every
single record in A will be shown even if there are no corresponding records

[quoted text clipped - 12 lines]