Linking tables with no common data & different numbers of observations (1 Viewer)

DOGGED

New member
Local time
Today, 09:20
Joined
Apr 15, 2014
Messages
2
Hi all,

I am doing a project where we are collecting home owner data and information on all the dogs in the household. The data for homeowner has an autonumber primary key because no field is unique or has been consistently collected. I am struggling to link the data for the dogs to the owners because an autonumber primary key will not work since not all homes have dogs. I need to have this set up so that people who are not tech savvy can look up each homeowner (or dog) and get the dog (or home owner) information. And to make things even more fun we need up to 15 potential dogs per household each of whom will have 18 different pieces of data collected.

It looks a little like this (and you can see my not matching ID issue):

Homeowner table
ID First Name Last Name...........Total Dogs
1 Max Maximus 5
2 Min Minimus 0
3 Mus Musculus 1

Dogs 1-5 table
ID Date Dog 1 Name Dog 2 Name .......Dog 5 Name
1 (Max's) 4/11/14 Bobby Billy Betty
2 (Mus's) 4/11/14 Jojo

Min will have no dog records at all, just home information.

For the life of me I can not figure out how to link the dog's to the homeowners AND I need to develop something that people who are not very computer savvy can run when I am not here, so it has to be something very simple on the front end.

:banghead:

Any help will be appreciated!!

Lynn
 

jdraw

Super Moderator
Staff member
Local time
Today, 12:20
Joined
Jan 23, 2006
Messages
15,364
Before you go further, do some research.
You need to get a basic understanding of relational database principles; database design and normalization.

Here are a few links to get you started.
Design Principles http://forums.aspfree.com/attachment.php?attachmentid=4712&d=1201055452

Entity Relationship Diagramming tutorial -leads you through all steps
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

Crystal Long's Access Basics -- ebook and some videos for many key concepts and usages of Access.
http://allenbrowne.com/casu-22.html

Good luck with your project.
 

DOGGED

New member
Local time
Today, 09:20
Joined
Apr 15, 2014
Messages
2
Before you go further, do some research.
You need to get a basic understanding of relational database principles; database design and normalization.

Here are a few links to get you started.
Design Principles

Entity Relationship Diagramming tutorial -leads you through all steps
Crystal Long's Access Basics -- ebook and some videos for many key concepts and usages of Access.


Good luck with your project.

Sorry for the delayed reply, my wireless hotspot died and we lost power so I could not recharge.

So thank you for the references and thanks to them I seem to have the relationships worked out (although I am concerned that the relationship structure is not so hot - it is 1:1 since only one home owner has dogs 1-5, 6-10, 11-15 - but I can't change it since 1/3 of the data was entered haphazardly in the separate tables before I got here).

So now I need to make sure that my dogs and houses are linked. I have HouseID (my now common ID throughout the tables) in the dog tables but it does not update in the dog tables when a new dog record is entered in Homeowner Tables through a data entry form. I knew how to do this once but not now.

Sorry if this is not making sense. I realized the last time I made an Access database was 2005!
 

spikepl

Eledittingent Beliped
Local time
Today, 17:20
Joined
Nov 3, 2010
Messages
6,144
Your data structure was wrong. It is still wrong. It is not an opinion but a fact. It will cause you untold PITA from now on until forever if you retain it. Also, just because you already have some data stuffed inappropriately is no argument for not fixing things.

Your dog table should be:

tblDogs
--------
DogId
HouseID
DogName
DogDate

You can get help here on how to get data from your current table into the one I mentioned here - you'd need some append queries for that.
 

Users who are viewing this thread

Top Bottom