Relating tables

[Lostguy]

Hello!

Getting wrapped around something basic:

I have 2 tables:

tblDog with DogID (PK, Autonumber) and DogName (text)

tblBreed with BreedID (PK, Autonumber) and Breed (text) and DogID
(link back to tblDog)


I am just trying to link these two tables together so I can list the
names and breeds of the dogs (pull together one field from each of the
two tables).

Now, is it "one dog can have many breeds" (one to many) or "many
breeds can be applied to one dog" (many to one)??

I have looked at many tutorials and still have problems figuring out
1) which one is the one and which is the many, 2) the proper type of
join type, 3) whether or not to check those darn cascade update/delete
buttons, and 4) which PK gets linked between the tables (does dogID go
in and get linked in both, or does breedID?).

Any help or tricks appreciated! I would like to move on past this, but
with every database I do, I can set the tables up, but when I try to
link them (create the relationship), I have trouble.

VR/

Lost

 

[Jeanette Cunningham]

Hi,
I can still remember the uncertaintity of trying to work out the one to many
tangle.
There will be many dogs with the same breed.
However I don't know much about breeding dogs, so you will need to say if
there any dogs that have more than one breed.

If dogs can have more than one breed, then we have:
Many dogs can have the same breed and Many breeds can have the same breed.
If the latter, this is what we call Many to Many.

If you need help to set up the Many to Many relationship, post back.

Jeanette Cunningham

 

[John W. Vinson]

Hello!

Getting wrapped around something basic:

I have 2 tables:

tblDog with DogID (PK, Autonumber) and DogName (text)

tblBreed with BreedID (PK, Autonumber) and Breed (text) and DogID
(link back to tblDog)

That's backwards... whenver you have a one to many relationship you need the
"foreign key" in the MANY side table.

Each dog presumably has one and only one breed ("Boxer Mix" for example).

Each breed will presumably be represented by many dogs.

You therefore have a One (breed) to Many (dogs) relationship.

The proper structure would be

tblDog
DogID (PK, autonumber)
DogName
Breed <foreign key to tblBreed>
<other information about the dog itself>

tblBreed
Breed <Text, Primary Key>

You would create a Relationship, referential integrity enforced, from
tblBreed.Breed to tblDog.Breed; Access will automatically make it a one to
many relationship.

The breed name will be unique within the table of breeds, it will be
reasonably stable (an Akita today will still be called an Akita next year,
most likely), and it's reasonably short (I'd use 20 characters), so it's an
acceptable primary key; despite Access' blandishments it is NOT essential to
add an Autonumber to every table.

If you're keeping close track of crossbreeds (dam was a German Shepherd /
Australian Shepherd cross, sire was an outrageously ambitious Chihuahua for
example) you might need a many to many relationship with a third table, but
this would be of interest only to a subset of dog breeders I'd guess!

John W. Vinson [MVP]