Problem with query and table joins

[Guest]

I have been asked to help with some changes to a pre-existing Access DB and
am experiencing some problems.

I have done a query to base a report on that takes data from 3 different
tables. Each table has a common member ID number in it. By default the tables
joined in a one to one relationship in the query based on this common field.
If I allow the query to run with all 3 joined it produces only 16 records in
a particular category which is incorrect. If I remove table C and run the
query with only the data being pulled from A & B it correctly produces 44
records in this same category. With table C included I am only pulling the
display of 3 Yes/No fields from table C into the query and cant see why it is
limiting the number of records being displayed. I have tried changing the
type of joins, but it doesnt seem to have any positive effect on the results
of the query. I think the fact that there may not be corresponding records in
Table C for each record in Tables A & B may be a factor along with the joins
but I cant seem to pin down the problem. Can anyone steer me in the right
direction on this?

 

[Brian]

I have been asked to help with some changes to a pre-existing Access DB and
am experiencing some problems.

I have done a query to base a report on that takes data from 3 different
tables. Each table has a common member ID number in it. By default the tables
joined in a one to one relationship in the query based on this common field.
If I allow the query to run with all 3 joined it produces only 16 records in
a particular category which is incorrect. If I remove table C and run the
query with only the data being pulled from A & B it correctly produces 44
records in this same category. With table C included I am only pulling the
display of 3 Yes/No fields from table C into the query and cant see why it is
limiting the number of records being displayed. I have tried changing the
type of joins, but it doesnt seem to have any positive effect on the results
of the query. I think the fact that there may not be corresponding records in
Table C for each record in Tables A & B may be a factor along with the joins
but I cant seem to pin down the problem. Can anyone steer me in the right
direction on this?

Change the join to table C to be an outer join. You can do this in the
query designer by right-clicking the join line, choosing "Properties", and
selecting the appropriate one of the three options presented.