Querying one to many tables

[Bert666]

Hi there people,

I have this querry based on two tables that have a one to many relationship - sometimes the many side will have values and at other times it won't - when i run the query i only get results of the records that have corresponding records in the many - any suggestions???

Bert.

 

[pdx_man]

You need to change your JOIN type to a LEFT join.

Look up joins, overview in Access Help.

 

[dcx693]

The type of join you created between the two tables is an equi-join which, as you've discovered, only displays records where the two tables have the same value.

What you want is a join that shows you all the values from one table, whether or not they exist in the other table. That's called an outer join.

Creating it is easy. In the query design view, double-click the join line between the two tables. You'll see three choices. The first corresponds to the equi-join the other two are the ones you're interested in. Pick the one you want and click OK, then run the query.

 

[dcx693]

I just wanted to write back to clarify the terminology (though you might not care).

pdx_man is right, it's called a LEFT join because it takes all the values from the table on the "one" side of the join and only includes the values from the "many" table if they exist. It's a type of outer join (the other type being a RIGHT join).

All these types of joins are collectively equi-joins because they find records based on whether or not some field is equal to another field. I was not entirely accurate in calling your original join an equi-join, it's more accurately called an INNER join.

The other major types of joins are called non-equi-joins because they're based on some field being >, >=, <, <=, <>, or Between some other field.

Just in case you were losing sleep over it.