2 one to many relations

[brback via AccessMonster.com]

Not quite sure where i should post this, but since i didn't find any "tables
discussion group" i ended up at queries. As i am already confused enough i
will use the original names for the stuff in my base and hope you can keep
track of it instead. I got 3 tables: Pet, Species and Breed. Species has a
one to many relation to both Pet and Breed

PET
Pet_ID (Primary key)
Pet Name
Species_ID

SPECIES
Species_ID (Primary key)
Species name

BREED
Breed_ID (Primary key)
Breed name
Species_ID

Making a querie with these 3 tables combined does not turn out well. The idea
behind this querie is to choose pet name first, then set Species, and at last
choose the Breed under the chosen Species.

Ive experimented a bit with the relations and changing PET/Species_ID to
Breed_ID, making a one to many relation from Pet to Breed, and yet another
one to many relation from Breed to Species. This turned out fine when making
a querie. Only trouble know is that i cant determine Breed out from Species,
since i have to choose Breed first, and when chosen Species is set
automatically.

I've been struggling alot to make a solution to the first option where im
supposed to pick a Breed after i've set Species. If anyone of you can help me
out with this which hopefully has an easy solution I'll be more then pleased.
If you have any question or ive been vague at some point feel free to say so
Thanks alot in advance!

 

[Guest]

The way you have your tables laid out you will have multiple PET for each
SPECIES and multiple BREED for each SPECIES. Based on this you can not
“choose pet name first, then set Species†as PET is subordinate to SPECIES.
You can “choose the Breed under the chosen Species†as BREED is subordinate
to SPECIES.

Post an example of data you would have in each table. Also an example of
what you expects as a result of your query. How are you going to use the
data?

 

[brback via AccessMonster.com]

Post an example of data you would have in each table. Also an example of
what you expects as a result of your query. How are you going to use the
data?

Thank you for you answer

hmm...PET has some more info that is irrelevant for the 2 other tables but
ill include them now to try and make a better "picture".

PET
Pet_ID (Primary key) 1 (Autonumber)
(Used for Query)
Pet Name Charlie
(Used for Query)
Birth date 10.12.1989
(Used for Query)
Sex Male
(Used for Query)
Length 1.90
(Used for Query)
Weight 90
(Used for Query)
Species_ID 1 (From Species)
(Used for Query)

SPECIES
Species_ID (Primary key) 1 (Autonumber)
Species name Dog
(Used for Query)

BREED
Breed_ID (Primary key) 1 (Autonumber)
(Used for Query)
Breed name Golden Retriever
(Used for Query)
Species_ID 1 (From Species)


Basically i just want a quercy with PET info and then be able be to choose
SPECIES which then again determines what BREED i can choose. So if i set
SPECIES to Bird i should not be able to pick Golden Retriever, Salamon etc.
but only Bird SPECIES such as Parrot, Crow and so on. Hope this made any
sense
Once more thanks alot in advance to any answer and suggestions

 

[Pat Hartman\(MVP\)]

The problem is that PET needs to be related to BREED. The real hierarchy is
Species/Breed/Pet.

On your form, you will have an unbound combo that presents species. When
you choose a species, you need to requery the breed combo to limit the
selections to breeds for that species. So in the AfterUpdate event of the
Species combo:

Me.cboBreed.Requery

Make sure the RowSource for the breed combo includes criteria that
references the species combo:

Select * from BREED
Where Species_ID = Forms!YourForm!cboSpecies;

 

[Michel Walsh]

Hi,


If you select the pet (Charlie), you AUTOMATICALLY KNOW it is a dog (because
Charlie.Species_ID = 1), and what is left unknown is the breed. And there is
no way to know the breed since, I assume, there are other breeds that are
dogs, but the information on hand just tell us that Charlie is a dog... it
should rather tell us it is a Golden Retriever.

Changing the table design of table Pet so you have Breed_ID instead of
Species_ID in table PET, then knowing you speak of Charlie you will
automatically know you speak of a Golden Retriever (from the relation
between Pets and Breeds) and that a Golden Retriever is a dog (from the
relation between Breeds and Species).


Hoping it may help,
Vanderghast, Access MVP