Unmatched Query

[Guest]

I have a table that contains fields for four players fldPlayer1, fldPlayer2, fldPlayer3, fldPlayer4. Through a main form a guest may be entered into any one of the four player fields. When a guest is entered a record is created in a separate guests table. However, if the guest is subsequently deleted from the main table I have an orphaned guest record in the guests table.

How can I best handle this problem. What seems to be problematic is that the guest's name may be in any one of the four fields in the main table. This seems to make an Unmatched Records Query fail to accurately identify which guest records are in fact orphaned (ie: do not appear in fldPlayer1, fldPlayer2, fldPlayer3 or fldPlayer4). If I could get the unmatched query to work properly I could then run a delete query of those records in the guests table.

Any help or alternate approaches would be appreciated.

 

[Jeff Boyce]

Bob

If your table has repeating fields (Player1, Player2, ...), you have
committed spreadsheet on Access. In Excel, this may be the only way to show
more than one possible player ... but Access is a relational database, and
you don't organize data the same way in Access.

To resolve your problem, consider reviewing "normalization" and relational
database design. If you insist on using the structure you now have, it may
be possible to do what you want, but it would be a little like trying to
figure out how to use a chainsaw to drive nails...

While it might work, it just isn't the correct tool, you risk damage to the
nail, and you could hurt yourself!