Noob query

[custhasno]

hi

I am creating a database for a horse society, with 2 tables, one for members info which is general contat info and a unique member ID that the databease auto assignes. The second table is for horse info with a unique horse society number and many details on the horse with attacted documents, drop downs etc



This i Have sorted so far, and im a noob so bear with me. The relationship is set up to allow members to register multiple horses and i now need to creat the forms for the inputted to use to register both members and horses.

Here comes my question, because every member submitting a horse will already have been recorded in member form, sometimes months earlier and assigned a unique member ID, Can i create a field in the hore registration form that automatically fills out Member name and address once the member Id is inputted, and do i need to put a membership ID field in my horse reg table? or does it have to be a link only on the form to the member info table?

[CraigDolphin]

Hi,

first up....have you any background on table normalization? It is helpful for understanding how to construct your tables for ease of use and maintenance down the road.

In any case: The way I would structure your database would be somehting like:

table Horses
HorseID (auto, pk)
HorseName
HorseBreedID
etc

table Members
MemberID (auto, pk)
FirstName
MiddleName
LastName
NameSuffix
AddressLine1
AddressLine2
City
StateProvince
ZipPostalCode
etc

And then you'd create a junctiontable to reflect ownership....and maybe history of ownership too if you wish to track ownership over time....for example

table MembersHorses
MemberHorseID (auto, pk)
MemberID (fk)
HorseID (fk)
OwnershipPercent
OwnshipStartingDate
OwnershipEndingDate

This would allow you to deal with multiple owners for one horse, or multiple horses for one owner, or multiple horses for multiple owners. Current owners would be those with a starting ownership date for the horse, and a null ending date for that horse.

Or if you have no interest in tracking history you could assume that all record represent current ownership status and don't bother with the date fields.

As for your specific question: you should only store the id of the member in the junction table. You should not store member names etc in the horses table. These belong in the Members table only. Recall that you can display a name in a combo box while storing the member id. So, in the subform that is bound to the MembersHorses table, a combo box will allow users to select the desired member name, while storing the id, all without them ever seeing the id number at all.

[custhasno]

thanks, i get most of that.

They will not look to track ownership over time so i will go the path of creating a junction table thanks for that.

What i want though is more for the end user who will get a peice of paper from existing member with the horse reg info, they input this via a form and the horse is auto allocated a unique ref number (horse ID). They also at this point recorde which existing member (they will always be existing members) is registering the horse.

On the peice of paper they have will also be the members name and membership ID which they where allocated by us previously when they register.

I need to make sure when they type in a membership Id that something on the same screen auto populates with the corresponding member name and address, the reason fro this is so that the inputted can verify the member Id they inputted was correct and matches the name they have with the records in our system.

sorry for the rant btw

[CraigDolphin]

If the horse is already registered, why do you want it to be re-registered with a different number?

[custhasno]

thanks again for the reply

owners will register and pay the society a fee so they can register any horses they breed as appalossa and get passports sent out by usso each day te office could get dozens of paper forms from members looking to register new horsen we register them we must keep track of which member registered which horse.

So the owner already registered, the horse is not, the owner simply provides their name and member id along with the horse registration info from a paper form we give them. but horses always have to have a member Id as non members cant register horses, they also have their own UELN number wich is the ID we assign each horse much like the member ID.

[custhasno]

the other thing is any joint member (ie multiple owners of one horse) will be registered as seperate joing members with one ID number, so the only relationship i need to worry about is one member ID to each horse.

I think my confusion, of which there is much, is arround how to best work the forms, I need a seperate membership registration form, thats pretty much done and the horse registration form, which has been giving me night nightmares.

I first of all need to put a feild in there for the inputter to type in the member ID, but im not looking to record this information in the horse table, or even in the members table as its already recorded.

I only want to have maybe a subform that allows the inputter to type in the member ID and confirm that that Id is infact for Mr Al Johnson from idaho etc.

I think my confusion is with that, hopefully.

Am i fool, am i over complicating it?

[CraigDolphin]

To be honest I'm having trouble interpreting exactly what you mean. I've attached an example database that might help you understand how you could use the kind of structure I mentioned in a form/subform situation. Maybe that will help you see what I mean. You only store the member id in the junction table, but you can display the additional information from the members table in the subform for the user to see.

Hope it is somewhat helpful.