Problem w/ # of records returned when joining 2 queries

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Greg G - 25 Jul 2006 00:43 GMT

Hello everyone, I am a first time submitter. I have a technical question for
you, which is as follows:

I have two queries that I need to connect in a new query. The first query
is called qry_RISK, and the other is called qry_CONTROL. The best primary
key candidate for each query is a field called PageID (a numeric field). In
order to break up the many-to-many relationship, a new table was created to
connect these two queries. This new table (called tbl_Master_Page_Links)
connects the PageID field in qry_RISK to the PageID field in the new table
tbl_Master_Page_Links. From there, the Linked_to_PageID field in
tbl_Master_Page_Links is connected to the PageID field in qry_CONTROL.
Everything is linked via a one-to-one relationship (Join Properties - option
# 1).

When, I run the master query that contains qry_RISK, tbl_Master_Page_Links
and qry_CONTROL, I notice a number of records drop off the radar.

If I create a new query with just qry_CONTROL in the query design window,
select the fields that I need in the results set, then run this test query,
it returns the correct number of records.

Any ideas what is going wrong?

Reply to this Message

Allen Browne - 25 Jul 2006 03:23 GMT

See:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

The article explains how to use outer joins, and watch the criteria under
fields that could be null.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hello everyone, I am a first time submitter. I have a technical question
> for
[quoted text clipped - 23 lines]
>
> Any ideas what is going wrong?

Reply to this Message

Greg G - 25 Jul 2006 17:49 GMT

Allen,

Thank you for your response. Although your response contains some very
useful and important information, it does not apply to my situation. It is
not possible for the primary key fields to contain null values b/c they are
system generated.

Do you have any other ideas? Thanks again.

> See:
> The Query Lost My Records! (Nulls)
[quoted text clipped - 31 lines]
> >
> > Any ideas what is going wrong?

Reply to this Message

Allen Browne - 26 Jul 2006 02:26 GMT

Hi Greg

You have 2 queries:
- qry_CONTROL returns all the records you want;
- a query containing 3 sources (qryRISK, tbl_Master_Page_Links and
qry_CONTROL) does not contain all the records you want.

The problem query uses inner joins.
Inner joins return only the results that match.
So if qryCONTROL or tbl_Master_Page_Links does not have all the records, the
result will be records "dropping off the radar."

The solution will be to use outer joins in the query.
Try with just sources, and once you have all the records returned, you can
add the 3rd one.

BTW, it is possible - in fact, normal - for a primary key field to contain
nulls in a query that uses outer joins. That's exactly what you can expect
once the query is returning all the records.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Allen,
>
[quoted text clipped - 51 lines]
>> >
>> > Any ideas what is going wrong?

Reply to this Message

Greg G - 26 Jul 2006 21:48 GMT

Hi Allen,

Thanks again for your reply and suggestions. When I tried to connect via
join property #3, the # of records that I was trying return for my specific
query were correct. However, about 47 records overall were still missing in
the overall data set. I can’t use this approach anyway because it violates a
business rule that is in place for two other tables.

In MS Access, I have never been able to make an Outer Join work. For
instance, you have 3 options in the “Join Properties” tool: the first creates
an Inner Join, and, depending on where the connection originated from, the
second creates a Left Join, and the third creates a Right Join.

If I go into the SQL view and manually change the 2 occurrences of Inner
Join to an Outer Join for the two tables in question, Access produces an
error message.

Am I doing something wrong, or have I overlooked something?

Thank you,

Greg

> Hi Greg
>
[quoted text clipped - 71 lines]
> >> >
> >> > Any ideas what is going wrong?

Reply to this Message

Allen Browne - 27 Jul 2006 02:28 GMT

That's right: double-click the line joining the 2 tables in the query design
window, and choose option 2 or 3 to get the left or right outer join.

You should have no problem with that when there are only 2 tables, and it
will update the SQL for you.

Once you have that working with 2 tables, you can add the 3rd, and make it
an outer join also. You do have to watch the direction of the joins, so you
don't end up with an "ambiguous outer join" error.

If your business rules preclude using outer joins, whoever made those rules
does not understand databases.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi Allen,
>
[quoted text clipped - 104 lines]
>> >> > query,
>> >> > it returns the correct number of records.

Reply to this Message