Ok.. I have say director1, director2, director3 and member1, member2 and member3 (10 separate fields for each party i.e director1address, director1postcode etc etc)

I know i can get member1=director1 and so on, but is there a dynamic way of doing it so that the user defines which member=director

kind of like a spike where the information is held on a clipboard until the user identifies the target

i.e one time it might be....

director1=member3

{new party}=member1

director3=member2

next time it could be ...

director1=member1

director2=member5

director3=member2

and so on and so on.

I need a query to say a) is director1 a member? if a)=Yes b)what member number is the director? {user input - member2} ... copy information from director1 to member2

etc etc

Is this all in ONE file?

If not, it sounds like you need to first consider the structure of the database.

You might start off by going relational.

Party Database

Director Database

Member Database

Use the Party Database to assign the Directors and Members to. (not by using lookups to populate the party database members, but by assigning links to those.)

Since it's possible members and directors can also go to many parties, you might need a join file.

Sorry it's a poor response, but I'm quite confused as to what it is you're trying to do...

I'm leary of your design because you said: Copy information from director1 to Member2.

What if director or member information changes? You will have copies all over the place that will need to be updated!