Normalizing for Greyhounds DB (1 Viewer)

[indyaries]

Greetings,

I have a friend who is using Excel to track a Greyhound Protective Association roster. These are folks that foster and adopt Greyhounds -- thus a worthy cause. It has over 600 rows of data.

I've attached 50 rows of sample data in Excel format.

Along with contact information, there are also fields to track up to 5 dogs. I've gone through the spreadsheet and tried to set it up for import to Access 2000. I have the dogs numbered 1 through 5.

I'd like to set this up in two (or more ??) tables, with Contacts in one, and the dog information in another. I'm guessing it will be one-to-many (Contacts -->Dogs).

However, I can't for the life of me figure out how to get this all together so that she can use a form-subform setup to input / access the data.

I'd like to pull up a name in the form, and have the subform display all of the dogs that this person has adopted, with the ability to add more dogs. Thus, they could have one dog, or 2 dozen dogs.

Any assistance would be greatly appreciated !! Thanks in advance.

 

[KenHigg]

This is extremely easy. All you need is a foreign key field in the dog table that points back to the contact tables primary key field. Then go into relationships view and define this relationship...

Try searching the forum for 'Normalization'.

Good luck - this sounds like a good 'first' database project to cut your teeth on!

 

[indyaries]

Hi Kenn,

Thanks for the rapid reply. Since some people already have more than one dog, does that mean that;

1. I'd have a table for Contact info
2. One table for each of dog 1 through 5 ??

Is there a way to set it up (perhaps in a query?) to have all 5 dog tables somehow linked, then use THAT query as the basis for a subform?

Or....is there a more efficient way ?!?!

Thanks again !!

 

[KenHigg]

No, just one table for all the dogs...

I'm fixing to hit the sack. If no one has helped by morning time, I'll give your post another look...

Cheers!

 

[Pat Hartman]

Almost any template database installed with access or available for download will have an example of a 1 (contact) to many (dogs) relationship. Go to http://office.microsoft.com/en-us/r...Scope=TC&QueryID=c6c7SwZPB0&TRC=30&iStartAt=1 and download the contributions db. It should give you a start since it will have a person table with address information and a contributions table. You would replace the contributions table with a dogs table. You might also want to include a breed table so you can use it to populate combos.

Converting the spreadsheet to tables is a multi-step process. Your first append query will take the contact information and append it to the people table. Then you will need 5 append queries, one for each dog column to append data to the dogs table. make sure to include criteria so you don't append empty rows.

 

[indyaries]

Pat and Kenn,

Thanks for your help. Pat, I'll check out that Contributions table you pointed me to.

Thanks again !!