Subforms and unwanted duplicate records

[Del Coro]

Apologies if this has been asked before, I didn't see anything relevant.

The situation:



I have two tables, one listing personnel, and another listing keys they are issued. I have a form for data entry of personnel information, and a subform for entering what keys they are issued (info that would normally be entered at the same time).

The problem arises because multiple people can have copies of the same key (which for the user's purposes essentially ARE the same key). When inputting one person's info it creates the new key in the key table, but when another person is entered with the same key number, it creates another new key, when obviously I just want it to relate the new record to the previous key.

Any suggestions are appreciated.

[RuralGuy]

You have more that one copy of the key so why are you having a problem with more that one record referring to a key?

[Del Coro]

You have more that one copy of the key so why are you having a problem with more that one record referring to a key?

Because

A) The users want the keys tracked as one single record, and

B) This problem will be replicated later on when multiple records of other types will need to match ONE record in another table, and not copies of the same record.

[RuralGuy]

I would think the "users" would want to know who has keys to a room.

[Del Coro]

I would think the "users" would want to know who has keys to a room.

And later on, when they want to know who is IN what room, and instead of one room with four occupants, the DB spits out four copies of the same room with one occupant each, what then?

But hey, thanks for not being remotely helpful.

[RuralGuy]

You're right, that will certainy be a tough problem to resolve and way beyond my talents. Good luck with that.