Querying duplicate fields

[Jeepem]

Please can anybody help.
I wanted to have a form containing a list of members forenames and surnames in one record. I wanted to be able to input the members reference number and then the forename and surname would be automatically filled in.
I have achieved this through a query based on a table with ten foreign keys for the members. The query uses the members table ten times to join the ten foreign keys.
This works fine and the form runs with space for ten members and I can enter a members reference number in each field and their forenames and surnames are filled in.
The problem is that I wish to run queries with members surnames as criteria to bring out all records containing those members. How can this be done without having to set the criteria in the query in each of the members surname fields.

I know I can do this if I have a separate record for each member in the form
but I really need ten members in each record.
I hope this makes sense and somebody can help.

Thanks in advance
John

 

[mresann]

I know you said you "really need ten members in each record." But I don't see any way of solving your problem without some normalization. Can you zip your db and attach it to a message? I would bet dollars to donuts that you have a table normalization problem.

 

[Jeepem]

I am attaching the dbase.
The members I refer to are called alleged offenders in this database.