Noob in over his head

[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

 

[stopher]

So you are saying that a horse is registered by one person and one person only. In which case, yes you need the membership ID as a foreign key in the Horse Info table.

I looked at your Horse Info table and I see that you already have Membership ID in there. So you just need to create a link between this and the PK in the Members table. Note that in order to get the 1-many symbols you need to set the referential integrity on the relationship. When I tried it on your tables it worked fine.

I took a look at your form (Horse Info). I was surprised to see Members as a subform which suggests many members can be associated with a single horse. This contradicts what you have said earlier. Or are these members owners rather than the person registering?

So I think you need to add Membership ID to your Horse Info form.

hth

Chris

 

[custhasno]

Thanks for the reply

I have got the entire thing working now.

On the Horse form, the members subform is a pig ugly solution to a problem i had.
I wanted something on the form to display the first and last name of the member based on the Member ID when the inputter typed it in. This would prevent any errors where the inputter registers a horse to the incorrect member.

Maybe you know an easier way to do the same job, cause with the subform all i can provide is a list of all members that the inputter can double check.

Bear in mind i am a noob but i dont mind spending time watching tutorials etc if it can be learned roughly in a day or two, just point the way