Noob in way over his head, pls help

[custhasno]

hi for reasons i wont go in to i have 12hrs to compile a database for a horse society, who take memberships, and allow those members to register multiple horses to gain passports for them.

I have the tables set up, i think normalization is ok, but im pretty nob and have skilled myself up form nothing in the last week.

The problem i have is the relationship and i think foreign key.
I have two tables (one for members with contact info and unique Member Id PK)
The other with the horse information including a unique Horse Id number (society number PK)

A single member can register many horses but never vice versa, im trying to set up the relationship but can not seem to get anything but 1-1.

One other thing too is, each time we input a new horse to register on the system we have to record which member registered it, so i have to have a feild for membership Id on the form to gather record the horse registration info, does this mean i have to have membership Id in as a foreign key in the horse info table?



eg
Horseinfo tbl
Society Number PK
Memership Id (FK?)
Horse name
Sex
Colour
etc

Membersinfo
Membership Id PK
Name
Address
phone
etc

any help would be a life saver

PS ignore the forms i hevnt got them finished yet

[pdebaets]

Yes, you need a MembershipID field on the Horseinfo table to serve as a foreign key to the Membership table.

For simplicity, I recommend naming your table "Horses", with HorseID as the autonumber primary key. You can include SocietyNumber as a separate field. Also I recommend naming your other table "Members", with MemberID as the autonumber primary key. Then the foreign key in the Horses table would be MemberID.

[custhasno]

excellent so that way if i input an incorrect membership Id on the horse form it wont let me proceed

at the minute when i try to set up the relationship it only allows for one to one but i need One (member ID) to Many (Society Numbers)

[pdebaets]

Can a horse have multiple owners?

[custhasno]

no but an owner can have multiple horses (society number is the horse ID)

[pdebaets]

Right click on the relationship - the line joining the two tables in the relationship view - then click "Edit Relationship". Put a check in "Enforce Referential Integrity". You will likely want to select cascading deletes, since if you delete a member, you will probably want the horse records for that member deleted as well.

[custhasno]

Thanks
yeah i have now done this, it still shows as one-to-one but i hvent tested it yet by creating records

[pdebaets]

Did you draw the relationoship line from the primary key of the Members table to the foreign key in the Horses table?

[custhasno]

Youre a legend thanks

its now sorted and working great