Using MS Access/Referencial Integrity

Advertisement


Question
QUESTION: Ho Scott,

I have 3 tables. One table is used to store data. 2 fields in this table are drop downs pulled from 2 other tables. Primary keys in these two tables are linked to the fields in the data storing table but referential integrity is not forced. The query is made out of these 3 tables. When, in query, I try to leave one of drop downs blank it does not allow me. However, the user may select or not select these fields. What can I do so that a user may leave one or both fields blank. I do not want to add a value "n/a" to these 2 "drop down" tables and make user select it all the time they want to leave it blank. It is not time efficient.

ANSWER: Could the field in the table be set as required or not allowing zero length strings?



---------- FOLLOW-UP ----------

QUESTION: I have removed the Primary Key from "drop down" tables (not the field itself but the designation of the field as a primary key) and this seems to fix the problem. Will it work? What am I losing but not having a  primary key assigned to a field in these drop downs?

Answer
OK, that indicates to me that the form isn't set up correctly. Your data storage table should have 2 fields as FOREIGN KEYS in them. The FKs should link to the Primary Keys in the "lookup" tables. In the query, you should be joining on PK to FK, but pulling the FK column into your grid.

Also, you should not be entering data into a query. Data interaction should be through forms only.  

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.