Newbie Question

[Bob Guidice]

I'm currently designing a database and have two separate tables that utilize
social security number (but not as a primary key) because both tables will
have multiple entries of the same social.

1). Is there a way to validate the data being entered into the second
table's social security number field based upon entries in the first table?
In other words, a user should not be able to enter a social security number
in the second table, that doesn't exist in the first table.

I'm not sure if referential integrity is used for this, but if it is, I
can't use it in this database because the Social Security Numbers are being
entered multiple times. Is there another solution?

Thanks for your time,
Bob Guidice

 

[Peter]

One way to do this is to use a Form for data entry with
the second table as the source. Make the the entry of the
SSN in a combo box, and make the source of the combo box
the first table.

 

[Jim]

Another way is to build a query (call it Query21) of the
first table, using other fields that ahould be linked from
the second table, and the SSN in the second table's form
as the criteria. A macro can open a form (call it Form21)
that uses Query21 as its source. If validated, the
entries in the Form21 will not be errored, if IsError() on
any of the Form21 contents, you can reject the entry.

-Jim