Combination of fields

[nezos]

Greets, I have the following problem:
1 Table holds information (StreetID, StreetName, PostalCode, Area) StreetID=PK
Unique values only in StreetID

2 Table holds information (...., StreetID, PostalCode, Area,......)
AutoNumberID=PK

The 1st table has a one to many relation to the 2nd table.

The problem is how can i make sure that the combination of StreetID,
PostalCode, Area entered in the 2nd table will exist in the 1st.

I tried setting primary keys StreetName, PostalCode, Area but i cannot force
referential integrity because these fields are not uniquely indexed.

Thanx for your answers

 

[Lynn Trapp]

Why are you storing StreetID, PostalCode, and Area redundantly? You
shouldn't need them in 2 tables.

 

[nezos]

Good Point.

The whole thing started from the fact that the user must select in a form
first the Area and then the StreetName which lead to the unique StreetID.

You are right, i should only keep the StreetID. I think i got confused for
no reason.

Do you have any suggestion how i should make the user's selections in a form
(i.e. from two comboboxes (area and then street)) lead to 1 ID stored in the
database. I mean should i program this (and when i.e., on lose focus or
something?) or do a macro or a query? I am open to any suggestion...

Thanx again for reading this.