Hi, i have a problem that takes a bit of explaining and i'm not sure that what i want to do is possible so please bear with me

i have a table, in it is peoples names, e.g

names table

names
john
jack
alan
bob
dave

etc


this then relates to another table where there are two fields, both relating back to the same row in the first table

so

names1 names2
John Jack
Bob Dave
John Bob


and so on


i need to stop this second table from allowing the same relationship to be reversed

i.e.


names1 names2
John Jack
Jack John


obviously this will cause quite a few problems later on


anyone able to help with this???

Could you use a dlookup() to do this?

If you are trying to generate a match-up roster, I guess I can see why you would not want reversals. There are various ways to do this, what you need is to search the web on the subject of combinations and permutations. That said, I'll give you some tips.

Basically, with VBA code and a couple of articles on combinations, this is easy. With only queries, it is harder.

One way that comes to mind is to put a filter on the second name selection. Only allow the second name's ID to be LARGER than the first name's ID. If you do this, you must NEVER allow the first name to come from the last record of the membership table. If the first rule is in effect, the last person named in the membership table can never be person 1.

So if Kenny is member ID 13 and George is member ID 15, you can generate a match for Kenny (as name1) vs. George (as name2) but you can never generate the reverse match with George (as name1) 'cause Kenny, with a LOWER ID number, would be disqualified by the selection criteria.

Beyond that, I'm afraid you have some research to do on the web or textbooks on combinatorial algorithms.