database design question (cant find right forum)

[lovejaeeun]

I need help with this database design question. The database is MS Access 2000 and it will have no more than 1500 entries per year.

I have two entities with a many to many relationship. HOST FAMILY and DELEGATE. A HOST FAMILY can host many delegates (but only one a year) and a DELEGATE can have many HOST FAMILY (but only one a year).

I call my intersection table EXCHANGE and it has info on the exchange like the year, organization who found the host family, etc.

The PK of EXCHANGE is: hostFamilyID + delegateID + year

The problem:

The HOST FAMILY entity has info like address, phone number, number of children etc. The problem is we don't know the HOST FAMILY info at the time of the exchange. We only have the most current info.

I suppose I could set the PK of HOST FAMILY to auto? and that way I would know what the HOST FAMILY was like at the time of the exchange. But then we wouldn't be able to query the database with stuff like: how many times has host family 'xxx' hosted, etc.

Is there a way to set up the database to get both? (host family info at the time of the exchange & be able to do queries?)

thanks,

lovejaeeun