Should I allow Nulls with referential integrity?

[bubai]

Joining tables in Access gives an option for Enforce Referential Integrity which is a great option to have where there is primary key - foreign key relation. But if I select the 'Required' option to NO in the Foreign key, that will allow for Null values to that field. Then if I need a query where that Foreign key field is involved the Null records will be excluded from the query result. Hence, is it advisable to have the 'Required' option to NO in the Foreign key and allow for Null value? And what will be the consequences or how to handle them? Sometimes I may need Nulls to that field at least for the time being.

[isladogs]

Setting referential integrity will still work with your scenario.
It will allow records in the parent table that aren't in the child table.
However it will prevent orphaned child records.

An inner join query will indeed exclude those child records with null FK records.
However, an outer join will show those records as well.
Or you can use an unmatched query to only show those records in the parent table not in the child table

In case it helps, have a look at my article on relationships and R.I. http://www.mendipdatasystems.co.uk/r...ps1/4594533224

[bubai]

Setting referential integrity will still work with your scenario.
It will allow records in the parent table that aren't in the child table.
However it will prevent orphaned child records.

An inner join query will indeed exclude those child records with null FK records.
However, an outer join will show those records as well.
Or you can use an unmatched query to only show those records in the parent table not in the child table

Dear isladogs,
Actually I was talking about Nulls in the child table. But my common sense is now telling me that there should not be null in the child table because access doesn't support more than two table joins very well. Many Thanks.

[CJ_London]

access doesn't support more than two table joins very well

can you clarify what that means?

[isladogs]

I was also referring to nulls in the child table. Obviously you can’t have null PK values in the parent table.
Like Ajax, I also would like to hear an explanation of your comment about ‘more than two joins’

[bubai]

can you clarify what that means?

Dear Ajax,
If you see the following picture whereby I tried to achieve same results using two types of joins. Top one failed to give properer answer, but bottom one worked fine. Here _sypplierLookup is the parent table and rest of the 3 are child. Although I didn't create join among the main tables, instead I joined them in the query. In all of the 3 tables Supplier_ID> fields are the foreign keys. I tried to bring all suppliers' ID and Name and their sum from the 3 parent tables. You can see the query grid fields in the bottom tables. But top one could not do the proper sum.

[bubai]

Like Ajax, I also would like to hear an explanation of your comment about ‘more than two joins’

Dear isladogs,
I have given my explanation regarding ‘more than two table joins’. If you please have a look.
Again the explanation that you have given in the first reply "However, an outer join will show those records as well." is not clear to me. If there is Null in the child table and I do outer join on Parent it should not fetch the nulls. And if the Nulls are in the parent outer join on parent should not fetch them as well.

[June7]

Of course these tables should not be chained in query. The 3 Pay tables are related to SupplierLookup and should link to SupplierLookup as shown in second query.

I don't understand your tables. Why are there fields named SumOfAmount? Summary data should be calculated from raw data transaction records.

[isladogs]

I've had a look.
Your example shows that Access CAN handle more than two table joins.
Whether the query gives the expected answer is a different issue and is nothing to do with Access itself.
I have many queries involving far more than three tables that work perfectly.

Your example appears to show three aggregate queries with Sum of Amount fields. As June said, those tables or queries shouldn't be linked as shown.

Perhaps it would help to look at some examples of queries with outer joins including a union query (full outer join).
See my article http://www.mendipdatasystems.co.uk/q...pes/4594517491
Examples b) & d) may be particularly relevant to you

[bubai]

I don't understand your tables. Why are there fields named SumOfAmount? Summary data should be calculated from raw data transaction records.

Dear June7,
Actually the 3 child table are not table. In fact they are queries which groups the Supplier_ID and Sum of the amounts from 3 child table. I just created an extra step in order to avoid any miscalculations because in the last field in the query grid I have put a calculation taking the 3 SumOftheAmount fields.

[CarlettoFed]

Maybe if you post a sample file, replacing any sensitive ones, it would be easier to help you.

[June7]

Still, don't chain the datasets. You have 3 datasets related to SupplierLookup and should link as in your second query.