queries in related tables not returning all records

[aphelps]

Hi--

I have a yes/no field in a table. In this field, 43 entries are checked 'yes.' When I query this table for 'yes', only 29 are returned.

The table I am querying is involved in a 1:1 relationship with another table. If I remove the related table from the query, then all 43 come up. Furthermore, the related table needs only be present to cause this problem (that is, it still occurs even if no fields from the related table are included in the query).

I am self-taught and maybe this is Access 101, but I cannot sort this out. It seems that there should be no factors from the related table that would limit the returned fields, but there must be. Any help is much appreciated.

Adam

 

[Geoff]

Hi,

It looks as though your related table only has 29 matching entries to those in your yes/no table.

A 0ne-to-one relationship means that only those records which match in both tables are returned.

hth

Geoff

 

[aphelps]

Yeah, you'd think that ( I did), but it's not true--all the fields that aren't turning up in the query are in fact in both tables.

Here's more detail:

Table 1: Species Number, Prediction (yes/no)
Table 2: Species Number, Common Name
Table 3: Species Number, Observed (number)

Tables 1 and 3 are 1:1 with table 2 on Species Number. All spp in tables 1 & 3 are in fact in table 2. Table 1 is not directly related to table 3, and the same species numbers are not necessarily in both (though there is much overlap).

My query:

Table2:Common Name, Table1rediction = "yes"

This should return the common name and "yes" checkbox for all records for which prediction = yes.

Again, this returns 13 fewer records than actually exist in the table, even if I leave out Common Name and only query Prediction.

My next thought was that perhaps for some reason it's only selecting birds that occur in Table 3 as well, even though 1 and 3 are not directly related. After checking, all the Species Numbers not returned by the query are missing from Table 3 (in Table 1, not in 3)--but several of the returned records are missing from Table 3 too! So that cannot be the sole reason.

I'm stuck.

Adam

 

[Geoff]

Have you tried splitting the query up?

Use 2 of the tables in Query1 and see what you get in the output, then build another query which links Query1 with Table3 say.

Might be worth a try


Geoff

 

[aphelps]

The problem is that these relationships are defined globally, not just in the query, so even removing the table not being used from the query view (in this case, the Observed table) doesn't fix the problem.

I see what you're saying, but if I have to redefine the relationships every time I do a query, I may as well just count them myself instead of using a query. Or perhaps I missed your meaning.

I don't understand why tables not involved in the query are affecting the query. I'm wondering if I turn off the referential integrity in the relationships if that'll help.

You've given me food for thought, and I appreciate your help.

Adam

 

[aphelps]

Thanks, Pat. I didn't even know that "join type" was there, it fixed my problem (and most likely a similar problem I've been having with two other databases). I've just got to figure out which direction all those relationships should be pointing....

Thanks again--Adam