suggestion on a relationship

[maxmangion]

I have a database for my stamp collection. I have created 3 tables:

1: to keep combo selections for the stamps form and be used as a lookup wizard
2: to keep the main details about each stamp
3: to keep personal details of friends with whom i exchange stamps with.

Now in the form where i have the stamp details i am creating a field for each friend collector (datatype yes/no) so that if i send him a stamp i will check the field so that i do not re-send the stamp another time. With this manner, my problem is that i have to keep on creating a new field, each time i make a new friend.

Is there any alternative how i should tackle this thing in another manner rather than having to create all this fields ?

Now that at present state i do not have any relationship between the personal details table and the stamp table.

Also i am planning that if i would be able to sort this out, i am intending to place the stampform as a subform in the personal details, displaying only the stamps only i had sent to that person.

Any ideas/suggestions are appreciated

Thx in advance

 

[FoFa]

Well any one stamp could be sent to many different people, and one friend could recive many different stamps. This would be a MANY to MANY relationship. When you see that , you have an issue. I would create another table that stores a stamp key and a friend key that basically says, this stamp was sent to this friend. Then you have a one to many and a one to many with this table in between. You can use this table in queries to handle who got what, or who hasn't got what.

 

[jeremie_ingram]

Since you can apparently send the same stamp multiple times, and you can send a single friend various stamps, you have the MANY-TO-MANY problem. You need to create a junction table that tracks the exchange fo these stamps. Create a junction table (tblSentStamps) and add fields identicle to the primary keys of both the friends and stamps tables.

tblFriends
FriendsID
FName
LName
.....

tblStamps
StampID
StampType
.....

tblSentStamps
SSID (autonum PKEY)
FriendsID
StampID
DateSent
.........

The junction table creates a unique identifier that can be refrenced for every transaction.

 

[maxmangion]

i think your suggestions are ideal to solve my problem. But now my problem is that i already have about 1000 records in the stamps table and about 20 records in the personal details table. Now regarding the Stamp ID, do i have to make a descriptive ID for each stamp (coz goind through 1000 records is a bit tedious i guess). Pls note that the only primary key i have in the stamps table is just an autonumber field.

Thx