Select Query not showing all records.

[JSimoncelli]

I have a table “tblProperty” with multiple fields; some text and some number fields along with number fields representing FK, the number fields are things like “pages” and “copies”.

I have created a query containing the table described above and the associated tables for each FK. When I drop in the tables associated with the FK’s the one-to-many relationships created in the relationship tool are shown, no problem so far.

I pull down into my query the PK from tblProperty along with other needed fields. For the fields containing FK’s, I pull the field from the respective table e.g. tblPropertyType. This will allow my query to show the actual data instead of the FK reference in the tblProperty, again no problem so far.

Here’s the problem, when I run the query if there are any “blank” FK fields in tblProerty, that record will not show up in the query. In other words only records with a number representing an FK reference will be displayed.

I don’t know if this is a feature or an issue.

John

 

[Bryan]

. . . Here’s the problem, when I run the query if there are any “blank” FK fields in tblProerty, . . .

Edited . . . I stated this terribly.
Do you really mean "FK"? tblProperty, is your primary table, I think? The foreign keys join with the primary key in tblProperty.

 

[JSimoncelli]

Yes I do (see attached Relationship).... If this not correct please let me know and could you provide a referance that i can read to obtain the correct process....

John

 

[jdraw]

If the FK is a "link" to a record in another table, and that link Is Null, then there is No associated record in the Other Table.

This seems normal.

If I have misunderstood your situation, please show an example using real data values.

 

[Bryan]

I edited my previous post because I really stepped on what I was trying to say.

The graphic helps, thanks.

A couple things look strange to me in your relationships.

- With tbl Property being your primary table, you relationships are normally one (tblProperty) to many tblOtherTables. For example, you may have multiple customers for a particular property. You link through the CustomerID in tblCustomer, which is good. I think, though, that the relationship should be reversed, with one (tblProperty) to many (tblCustomer)

- The tblPropertyType only has one lookup field. For simplicity, you might be better off just using PropertyType in tblProperty. That's not incorrect, though, and allow for future expandability should you need more fields within tblPropertyType

As to records not showing up in the query, it depends on how you constructed the query. I think that if you did a LEFT JOIN, you should still get all of the tblProperty records, but you won't have corresponding data from the other tables as there is nothing to match on for the blank fields.

 

[JSimoncelli]

Bryan,
Let me clarify the way I setup this database. Referencing the Relationship image from the post above, with the exception of the tblAudit table all of the others represent a list of option available to the user. And that selection is associated to the tblProperty table via the one-to-many relationship.

The attached image is a portion of the tblProperty Table. The QtyReceived, QtyIssued and QtyOnHand the number fields I mentioned in my original post. As you can see the fields ending with “ID” contain the numbers (PK’s) from their respective tables. For example the PropertyType field contains the PK from the tblPropertyType table, there by linking the individual record in the tblProperty table with the selection made by the user.

John

 

[JSimoncelli]

jdraw,

Yes your assumption is correct. For example, given record in the tblProperty table may not have a “Property Type” so the corresponding PropertyTypeID FK in the tblProperty table would be blank.

Having said this back to my original issue, when I created the query any record with any blank FK field does not show up…

John

 

[JSimoncelli]

I edited my previous post because I really stepped on what I was trying to say.

The graphic helps, thanks.

A couple things look strange to me in your relationships.

- With tbl Property being your primary table, you relationships are normally one (tblProperty) to many tblOtherTables. For example, you may have multiple customers for a particular property. You link through the CustomerID in tblCustomer, which is good. I think, though, that the relationship should be reversed, with one (tblProperty) to many (tblCustomer)

- The tblPropertyType only has one lookup field. For simplicity, you might be better off just using PropertyType in tblProperty. That's not incorrect, though, and allow for future expandability should you need more fields within tblPropertyType

As to records not showing up in the query, it depends on how you constructed the query. I think that if you did a LEFT JOIN, you should still get all of the tblProperty records, but you won't have corresponding data from the other tables as there is nothing to match on for the blank fields.

The database works the other way around as you have stated, one Customer can have/be associated with many Properties, or many Properties can fall under one Contract.
Does this help?
John

 

[Bryan]

Can you post the query? Someone might be able to spot the issue from that.

 

[Pat Hartman]

I think several of your relationships are backwards and some others are actually 1-1 and so should not be separate tables at all. For example, a property can only be one place at one time and so the address belongs in the property table rather than a separate table. Same with property detail. The property table contains a serial number. That means it can only be a single model number NOT many.

To answer your query question though - whenever a foreign key may be null, you must use a left join to that table. Inner joins only return rows when BOTH tables have matching entries. When the entry is in one table but not another, you need an outer join.

 

[JSimoncelli]

All,

Here is the query, it is find of long so there is two parts...

Also here is an example of the Contract filter:

Contract: tblContract.[ContractName] & " - " & [ContractNumber]

All of the filters are similar.....

John

 

[Bryan]

I think we need the SQL.

 

[jdraw]

John,
Please read Pat Hartman's post #10.
Her second paragraph states clearly what I was alluding to in my previous post.
You can not do an inner join for records that have a blank/null FK.

You will need to use an Outer Join.

Thanks Pat for clearly stating the issue.

 

[JSimoncelli]

All,

I wanted to thank everyone for their help on this issue. After talking with one of my engineers I have decided to simply use a default entry like ‘N/A” or “Unknown” instead of having to deal with the null values. I did do some reading on the subject of multiple left joins and access and it wasn’t pretty.

So once again thanks for all your help….

John

 

[Pat Hartman]

Not a good idea. FKs must be a valid value (N/A and Unknown are not valid values) or null.

 

[adyas]

I think several of your relationships are backwards and some others are actually 1-1 and so should not be separate tables at all. For example, a property can only be one place at one time and so the address belongs in the property table rather than a separate table. Same with property detail. The property table contains a serial number. That means it can only be a single model number NOT many.

To answer your query question though - whenever a foreign key may be null, you must use a left join to that table. Inner joins only return rows when BOTH tables have matching entries. When the entry is in one table but not another, you need an outer join.

This has saved me from a major headache. Thank you Pat! Easy when you know how.