Trying to create pedigree/info storage program using Access

[Guest]

I am trying to create a program using Access. I have learned all the basics
about Access, but I still don't seem to know enough to make it do what I
want. I play a game where you breed cats. I am trying to make Access store
the information about each cat and some how make it form a pedigree as well.
I have been trying to have Access recognize that when I put a cat as a sire
or dam that the cat is then noted as an offspring of both the sire and dam. I
want to only have to enter in the basic information of each cat and have the
program build the family tree for me.

I found a somewhat similar question about the pedigree on here, but when I
did what it said to do, it didn't compile the information. I don't know how
to link it to the database properly I guess.

Here is a picture of the forms I have created to try and give you an idea of
what I'm trying to do. There's a program I'm somewhat mimicking but mine
doesn't include as much stuff. I would just ask for help with the original
program, but they went out of business, so I'm on my own.

http://i3.photobucket.com/albums/y92/EliKitty17/Database01.jpg

Please help me, I've asked everyone I personally know but no one seems to
know what to do. Is there a different program I should be using to create
this? Will I be able to distribute this to my friends without them having to
have Access on their computer? Any replies are greatly appreciated.

 

[Vincent Johns]

You might want to create an additional Table that links offspring with
parents; each record could contain 3 key values, one each for sire, dam,
and offspring. Members of a litter would have one record each,
differing only in the [offspring] value. Each of these keys would link
to your [Kitties] Table, which would contain other information such as
markings, name, contact info, registration info, date/place of birth, etc.

This will allow you to store your pedigree information, though making
the Form look like your current program will take a bit of artistry.

Access is capable of doing an excellent job, but it will take a bit of
work to set everything up, enter the data, etc. Other choices might be
a (human) genealogy program.

Yes, you can distribute the Reports from your database without your
friends' having access to Access. No, they will need Access to be able
to make changes to your design or to enter data into your database.
What you might want to do is to export some of the data as an Excel
spreadsheet; your friends would be able to update that sort of thing,
though you might have a little trouble re-importing their changes into
your database. (For example, you'd need to decide how to deal with a
changed record that both you and your friend had changed -- which of
them would you want to keep?)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

Okay, I guess I need to get the initial pedigree query working first. I based
it on the main database table. But I don't seem to understand how to get it
to take the table info and fill in the pedigree part for me. I used the info
from this website:

tp://www.allenbrowne.com/ser-06.html

to form the query, but it doesn't explain how to get it to work.

For the offspring part, would there have to be a set number of offspring? Or
would it grow on its own when I add a cat who's parents are the same as
another's? And would I set it up the same way as the other query?

With this additional table, would it be possible to make it update on it's
own so I would only have to fill in a cat's information once?

The purpose of this database is so that people could enter their own cats'
information and store it on their computer. So I'm guessing I'm working with
the wrong program if I want to distribute it. The program I'm some-what
basing it off of can be found here:

http://www.kindheartenterprises.com/software/pets/petshots.asp

if that would help clear things up any. The problem with this program is
that it has too much more than I need. I only want the info storage and the
pedigree part. But it seems that replicating that is more than difficult.

You might want to create an additional Table that links offspring with
parents; each record could contain 3 key values, one each for sire, dam,
and offspring. Members of a litter would have one record each,
differing only in the [offspring] value. Each of these keys would link
to your [Kitties] Table, which would contain other information such as
markings, name, contact info, registration info, date/place of birth, etc.

This will allow you to store your pedigree information, though making
the Form look like your current program will take a bit of artistry.

Access is capable of doing an excellent job, but it will take a bit of
work to set everything up, enter the data, etc. Other choices might be
a (human) genealogy program.

Yes, you can distribute the Reports from your database without your
friends' having access to Access. No, they will need Access to be able
to make changes to your design or to enter data into your database.
What you might want to do is to export some of the data as an Excel
spreadsheet; your friends would be able to update that sort of thing,
though you might have a little trouble re-importing their changes into
your database. (For example, you'd need to decide how to deal with a
changed record that both you and your friend had changed -- which of
them would you want to keep?)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

I am trying to create a program using Access. I have learned all the basics
about Access, but I still don't seem to know enough to make it do what I
want. I play a game where you breed cats. I am trying to make Access store
the information about each cat and some how make it form a pedigree as well.
I have been trying to have Access recognize that when I put a cat as a sire
or dam that the cat is then noted as an offspring of both the sire and dam. I
want to only have to enter in the basic information of each cat and have the
program build the family tree for me.

I found a somewhat similar question about the pedigree on here, but when I
did what it said to do, it didn't compile the information. I don't know how
to link it to the database properly I guess.

Here is a picture of the forms I have created to try and give you an idea of
what I'm trying to do. There's a program I'm somewhat mimicking but mine
doesn't include as much stuff. I would just ask for help with the original
program, but they went out of business, so I'm on my own.

http://i3.photobucket.com/albums/y92/EliKitty17/Database01.jpg

Please help me, I've asked everyone I personally know but no one seems to
know what to do. Is there a different program I should be using to create
this? Will I be able to distribute this to my friends without them having to
have Access on their computer? Any replies are greatly appreciated.

 

[Vincent Johns]

Dear Mr./Miss Stoffelees,

Here's my version of your initial pedigree query. I had to set up a
Table, of course, to provide a meaningful example, but I expect you may
want to make changes to it. For example, maybe date of birth
(whelping/queening/hatching, &c.) is not of importance to you, so you
could just zap that. (But for now, don't erase it, as some of my
Queries depend on it.)

Let's start with the Table that contains all of the (fake) data used in
this example. You should be able to reproduce everything here using
your own copy of Access. (Sorry, I had to fold this Datasheet, and most
of the others, to avoid having the lines get folded automatically at
unhappy places.)

The [Kitties_ID] field is an Autonumber type, meaning that it's
generated automatically and guaranteed to be unique. You might choose
some other type ID field, such as a name, but then you'd have to be sure
you never reused a name, and you'd have to type it yourself. (Also,
Autonumbers take only 4 bytes, while names are usually longer, a
consideration if you have big enough Tables that size is a problem.)

[Sire] and [Dam] fields contain "foreign key" values identifying which
record in [Kitties] identifies a parent of the kitty in the current
record. For example, in this Table, the record for "Pretty" (record #
548426157) has a [Sire] field of -1604637614, which refers to the record
for "Tiger".

[Kitties] Table Datasheet View:

Kitties_ID Name Sex Sire
----------- ------- --- -----------
289370756 ?? 289370756
-220441218 Lioness F 289370756
-1604637614 Tiger M 289370756
369735989 Puff F 289370756
548426157 Pretty F -1604637614
2051116689 Tugger M -1604637614
-776932656 Cutie F -1604637614
341341803 Growler M -1604637614
-224409238 Zinger F 2051116689
481390556 Mouser M 341341803
-41180253 Bitsy F 2051116689
252328743 Striper M 341341803

Dam Born
---------- --------
289370756 1/1/1950
289370756 1/1/1990
289370756 1/1/1990
289370756 1/1/1990
369735989 1/1/1996
369735989 1/1/1996
369735989 1/1/1996
-220441218 1/1/1997
-220441218 1/1/1998
-776932656 1/1/1999
-224409238 1/1/2000
548426157 1/1/2000

Well, that's it for the "data". The remaining examples are all based on
these. (For now, let's assume that inbreeding is not a problem for
virtual cats.)

Let's say that we want to list the parents & grandparents of everyone in
our collection. The following Query goes back 2 generations, but you
could extend it if you wish.

[Q_010Ancestors] SQL:

SELECT [Kitties].[Name], [Kitties].[Kitties_ID],
KS.Name AS S, KD.Name AS D, KSS.Name AS SS,
KSD.Name AS SD, KDS.Name AS DS, KDD.Name AS DD
FROM (((((Kitties
INNER JOIN Kitties AS KS
ON [Kitties].[Sire]=KS.Kitties_ID)
INNER JOIN Kitties AS KD
ON [Kitties].[Dam]=KD.Kitties_ID)
INNER JOIN Kitties AS KSS
ON KS.Sire=KSS.Kitties_ID)
INNER JOIN Kitties AS KSD
ON KS.Dam=KSD.Kitties_ID)
INNER JOIN Kitties AS KDS
ON KD.Sire=KDS.Kitties_ID)
INNER JOIN Kitties AS KDD
ON KD.Dam=KDD.Kitties_ID
ORDER BY [Kitties].[Born];

Running this gives the following list of ancestors. "S" refers to sire,
"DS" refers to dam's sire, etc.

[Q_010Ancestors] Query Datasheet View:

Name Kitties_ID S D SS
------- ----------- ------- ------- -----
?? 289370756 ?? ?? ??
Lioness -220441218 ?? ?? ??
Tiger -1604637614 ?? ?? ??
Puff 369735989 ?? ?? ??
Pretty 548426157 Tiger Puff ??
Tugger 2051116689 Tiger Puff ??
Cutie -776932656 Tiger Puff ??
Growler 341341803 Tiger Lioness ??
Zinger -224409238 Tugger Lioness Tiger
Mouser 481390556 Growler Cutie Tiger
Bitsy -41180253 Tugger Zinger Tiger
Striper 252328743 Growler Pretty Tiger

SD DS DD
------- ------ -------
?? ?? ??
?? ?? ??
?? ?? ??
?? ?? ??
?? ?? ??
?? ?? ??
?? ?? ??
?? ?? ??
Puff ?? ??
Lioness Tiger Puff
Puff Tugger Lioness
Lioness Tiger Puff

In a Form, I assume you'd use a Query like this as the Form's data
source, and place text boxes or similar controls at suitable places on
the Form.

OK, now let's go the other direction -- to offspring. Since I'm
distinguishing between toms and queens, I define a Query for the
offspring of queens:

[Q_020ChildF] SQL:

SELECT Parent.Kitties_ID, Parent.Name,
Child.Kitties_ID AS Offspring_Kitties_ID
FROM Kitties AS Child
INNER JOIN Kitties AS Parent
ON Child.Dam = Parent.Kitties_ID;

Running this produces a list like this:

[Q_020ChildF] Query Datasheet View:

Kitties_ID Name Offspring_Kitties_ID
---------- ------- --------------------
-776932656 Cutie 481390556
-224409238 Zinger -41180253
-220441218 Lioness 341341803
-220441218 Lioness -224409238
289370756 ?? 289370756
289370756 ?? 369735989
289370756 ?? -1604637614
289370756 ?? -220441218
369735989 Puff -776932656
369735989 Puff 2051116689
369735989 Puff 548426157
548426157 Pretty 252328743

For offspring of toms, we use the same SQL, with a minor change in the
last line.

[Q_030ChildM] SQL:

SELECT Parent.Kitties_ID, Parent.Name,
Child.Kitties_ID AS Offspring_Kitties_ID
FROM Kitties AS Child
INNER JOIN Kitties AS Parent
ON Child.Sire=Parent.Kitties_ID;

The list will be different from [Q_020ChildF], but the format is the same.

OK, we want a list of all offspring of each kitty, regardless of sex,
and we can do that with a Union Query:

[Q_040Offspring] SQL:

SELECT * FROM Q_020ChildF
UNION SELECT * FROM Q_030ChildM
ORDER BY Name;

The combined list looks like this. I used key values for offspring
instead of names, since I figured you wouldn't have lots of use for this
exact format. Names get added in the next step.

[Q_040Offspring] Query Datasheet View:

Kitties_ID Name Offspring_Kitties_ID
---------- ------- --------------------
289370756 ?? -1604637614
289370756 ?? -220441218
289370756 ?? 289370756
289370756 ?? 369735989
-776932656 Cutie 481390556
341341803 Growler 252328743
341341803 Growler 481390556
-220441218 Lioness -224409238
-220441218 Lioness 341341803
548426157 Pretty 252328743
369735989 Puff -776932656
369735989 Puff 548426157
369735989 Puff 2051116689
-1604637614 Tiger -776932656
-1604637614 Tiger 341341803
-1604637614 Tiger 548426157
-1604637614 Tiger 2051116689
2051116689 Tugger -224409238
2051116689 Tugger -41180253
-224409238 Zinger -41180253

What I want next is to assign a sequential number (random order here,
but with a bit of effort you could sort them by date or name) to each
offspring of a given parent, for the purpose of listing them later.

[Q_050OffspringNumber] SQL:

SELECT QO1.Kitties_ID, QO1.Name,
QO1.Offspring_Kitties_ID,
K.Name AS Off_Name,
Count(QO2.Kitties_ID) AS Sequence
FROM (Q_040Offspring AS QO1
INNER JOIN Q_040Offspring AS QO2
ON QO1.Kitties_ID = QO2.Kitties_ID)
INNER JOIN Kitties AS K
ON QO1.Offspring_Kitties_ID = K.Kitties_ID
WHERE (((QO2.Offspring_Kitties_ID)
<=[QO1]![Offspring_Kitties_ID]))
GROUP BY QO1.Kitties_ID, QO1.Name,
QO1.Offspring_Kitties_ID, K.Name
ORDER BY QO1.Name, Count(QO2.Kitties_ID);

So here you get the name and the sequence number.

[Q_050OffspringNumber] Query Datasheet View:

Kitties_ID Name Offspring_ Off_Name Sequence
Kitties_ID
----------- ------- ----------- -------- --------
289370756 ?? -1604637614 Tiger 1
289370756 ?? -220441218 Lioness 2
289370756 ?? 289370756 ?? 3
289370756 ?? 369735989 Puff 4
-776932656 Cutie 481390556 Mouser 1
341341803 Growler 252328743 Striper 1
341341803 Growler 481390556 Mouser 2
-220441218 Lioness -224409238 Zinger 1
-220441218 Lioness 341341803 Growler 2
548426157 Pretty 252328743 Striper 1
369735989 Puff -776932656 Cutie 1
369735989 Puff 548426157 Pretty 2
369735989 Puff 2051116689 Tugger 3
-1604637614 Tiger -776932656 Cutie 1
-1604637614 Tiger 341341803 Growler 2
-1604637614 Tiger 548426157 Pretty 3
-1604637614 Tiger 2051116689 Tugger 4
2051116689 Tugger -224409238 Zinger 1
2051116689 Tugger -41180253 Bitsy 2
-224409238 Zinger -41180253 Bitsy 1

Now let's reorganize these so that, for each parent in our Table, that
parent's offspring are combined into one record.

[Q_060OffspringXtab] SQL:

TRANSFORM First(QON.Off_Name) AS FON
SELECT QON.Name, QON.Kitties_ID
FROM Q_050OffspringNumber AS QON
GROUP BY QON.Name, QON.Kitties_ID
PIVOT QON.Sequence;

[Q_060OffspringXtab] Query Datasheet View:

Name Kitties_ID 1 2 3 4
------- ----------- ------- ------- ------ --------
?? 289370756 Tiger Lioness ?? Puff
Cutie -776932656 Mouser
Growler 341341803 Striper Mouser
Lioness -220441218 Zinger Growler
Pretty 548426157 Striper
Puff 369735989 Cutie Pretty Tugger
Tiger -1604637614 Cutie Growler Pretty Tugger
Tugger 2051116689 Zinger Bitsy
Zinger -224409238 Bitsy

Access gives you lots of freedom in deciding how to organize your stuff.
Here, I limited that freedom somewhat in allowing, for example, only a
couple of choices for [Sex], and in requiring the [Sire] and [Dam]
fields to contain only values that match the [Kitties_ID] field of some
existing record in [Kitties]. (This won't guarantee that the recorded
information is accurate, but it can help avoid some types of careless
mistakes, such as leaving the field empty.) How I enforced the rules is
not evident in what I've shown you, but if you wish to do that, look up
"Validation Rule" and "Referential Integrity" in Access Help. (Or, you
could simply be careful, and never make data-entry mistakes.)

Good luck, and pet some kittycats for me.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Okay, I guess I need to get the initial pedigree query working first. I based
it on the main database table. But I don't seem to understand how to get it
to take the table info and fill in the pedigree part for me. I used the info
from this website:

tp://www.allenbrowne.com/ser-06.html

to form the query, but it doesn't explain how to get it to work.

For the offspring part, would there have to be a set number of offspring? Or
would it grow on its own when I add a cat who's parents are the same as
another's? And would I set it up the same way as the other query?

With this additional table, would it be possible to make it update on it's
own so I would only have to fill in a cat's information once?

The purpose of this database is so that people could enter their own cats'
information and store it on their computer. So I'm guessing I'm working with
the wrong program if I want to distribute it. The program I'm some-what
basing it off of can be found here:

http://www.kindheartenterprises.com/software/pets/petshots.asp

if that would help clear things up any. The problem with this program is
that it has too much more than I need. I only want the info storage and the
pedigree part. But it seems that replicating that is more than difficult.

:

You might want to create an additional Table that links offspring with
parents; each record could contain 3 key values, one each for sire, dam,
and offspring. Members of a litter would have one record each,
differing only in the [offspring] value. Each of these keys would link
to your [Kitties] Table, which would contain other information such as
markings, name, contact info, registration info, date/place of birth, etc.

This will allow you to store your pedigree information, though making
the Form look like your current program will take a bit of artistry.

Access is capable of doing an excellent job, but it will take a bit of
work to set everything up, enter the data, etc. Other choices might be
a (human) genealogy program.

Yes, you can distribute the Reports from your database without your
friends' having access to Access. No, they will need Access to be able
to make changes to your design or to enter data into your database.
What you might want to do is to export some of the data as an Excel
spreadsheet; your friends would be able to update that sort of thing,
though you might have a little trouble re-importing their changes into
your database. (For example, you'd need to decide how to deal with a
changed record that both you and your friend had changed -- which of
them would you want to keep?)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Stoffelees wrote:

I am trying to create a program using Access. I have learned all the basics
about Access, but I still don't seem to know enough to make it do what I
want. I play a game where you breed cats. I am trying to make Access store
the information about each cat and some how make it form a pedigree as well.
I have been trying to have Access recognize that when I put a cat as a sire
or dam that the cat is then noted as an offspring of both the sire and dam. I
want to only have to enter in the basic information of each cat and have the
program build the family tree for me.

I found a somewhat similar question about the pedigree on here, but when I
did what it said to do, it didn't compile the information. I don't know how
to link it to the database properly I guess.

Here is a picture of the forms I have created to try and give you an idea of
what I'm trying to do. There's a program I'm somewhat mimicking but mine
doesn't include as much stuff. I would just ask for help with the original
program, but they went out of business, so I'm on my own.

http://i3.photobucket.com/albums/y92/EliKitty17/Database01.jpg

Please help me, I've asked everyone I personally know but no one seems to
know what to do. Is there a different program I should be using to create
this? Will I be able to distribute this to my friends without them having to
have Access on their computer? Any replies are greatly appreciated.

 

[Guest]

So, I created the entire database you made and mimicked/molded it to work
with the table I had created. I finally figured out why neither the one you
made or the one I mentioned earlier wouldn't work. I needed to have at least
3 generations. So, I understand how to get it to show 3 generations, now how
do I get it to show 2, 3, 4, however many I want (meaning it will vary with
each cat) with only one main query to run? And how can I get it to display
only one cat's pedigree at a time on the form? I'm sorry if I seem to
ignorant, but I'm doing my best to learn.

Sincerely,
Miss Stoffelees



:

Dear Mr./Miss Stoffelees,

Here's my version of your initial pedigree query. I had to set up a Table,
of course, to provide a meaningful example, but I expect you may want to make
changes to it. For example, maybe date of birth (whelping/queening/hatching,
&c.) is not of importance to you, so you could just zap that. (But for now,
don't erase it, as some of my Queries depend on it.)

Let's start with the Table that contains all of the (fake) data used in this
example. You should be able to reproduce everything here using your own copy
of Access. (Sorry, I had to fold this Data sheet, and most of the others, to
avoid having the lines get folded automatically at unhappy places.)

The [Kitties_ID] field is an Auto number type, meaning that it's generated
automatically and guaranteed to be unique. You might choose some other type
ID field, such as a name, but then you'd have to be sure you never reused a
name, and you'd have to type it yourself. (Also, Auto numbers take only 4
bytes, while names are usually longer, a consideration if you have big enough
Tables that size is a problem.)

[Sire] and [Dam] fields contain "foreign key" values identifying which
record in [Kitties] identifies a parent of the kitty in the current record.
For example, in this Table, the record for "Pretty" (record # 548426157) has
a [Sire] field of -1604637614, which refers to the record for "Tiger".

[Kitties] Table Datasheet View:

Kitties_ID Name Sex Sire
----------- ------- --- -----------
289370756 ?? 289370756
-220441218 Lioness F 289370756
-1604637614 Tiger M 289370756
369735989 Puff F 289370756
548426157 Pretty F -1604637614
2051116689 Tugger M -1604637614
-776932656 Cutie F -1604637614
341341803 Growler M -1604637614
-224409238 Zinger F 2051116689
481390556 Mouser M 341341803
-41180253 Bitsy F 2051116689
252328743 Striper M 341341803

Dam Born
---------- --------
289370756 1/1/1950
289370756 1/1/1990
289370756 1/1/1990
289370756 1/1/1990
369735989 1/1/1996
369735989 1/1/1996
369735989 1/1/1996
-220441218 1/1/1997
-220441218 1/1/1998
-776932656 1/1/1999
-224409238 1/1/2000
548426157 1/1/2000

Well, that's it for the "data". The remaining examples are all based on
these. (For now, let's assume that inbreeding is not a problem for virtual
cats.)

Let's say that we want to list the parents & grandparents of everyone in our
collection. The following Query goes back 2 generations, but you could extend
it if you wish.

[Q_010Ancestors] SQL:

SELECT [Kitties].[Name], [Kitties].[Kitties_ID],
KS.Name AS S, KD.Name AS D, KSS.Name AS SS,
KSD.Name AS SD, KDS.Name AS DS, KDD.Name AS DD
FROM (((((Kitties
INNER JOIN Kitties AS KS
ON [Kitties].[Sire]=KS.Kitties_ID)
INNER JOIN Kitties AS KD
ON [Kitties].[Dam]=KD.Kitties_ID)
INNER JOIN Kitties AS KSS
ON KS.Sire=KSS.Kitties_ID)
INNER JOIN Kitties AS KSD
ON KS.Dam=KSD.Kitties_ID)
INNER JOIN Kitties AS KDS
ON KD.Sire=KDS.Kitties_ID)
INNER JOIN Kitties AS KDD
ON KD.Dam=KDD.Kitties_ID
ORDER BY [Kitties].[Born];

Running this gives the following list of ancestors. "S" refers to sire,
"DS" refers to dam's sire, etc.

[Q_010Ancestors] Query Datasheet View:

Name Kitties_ID S D SS
------- ----------- ------- ------- -----
?? 289370756 ?? ?? ??
Lioness -220441218 ?? ?? ??
Tiger -1604637614 ?? ?? ??
Puff 369735989 ?? ?? ??
Pretty 548426157 Tiger Puff ??
Tugger 2051116689 Tiger Puff ??
Cutie -776932656 Tiger Puff ??
Growler 341341803 Tiger Lioness ??
Zinger -224409238 Tugger Lioness Tiger
Mouser 481390556 Growler Cutie Tiger
Bitsy -41180253 Tugger Zinger Tiger
Striper 252328743 Growler Pretty Tiger

SD DS DD
------- ------ -------
?? ?? ??
?? ?? ??
?? ?? ??
?? ?? ??
?? ?? ??
?? ?? ??
?? ?? ??
?? ?? ??
Puff ?? ??
Lioness Tiger Puff
Puff Tugger Lioness
Lioness Tiger Puff

In a Form, I assume you'd use a Query like this as the Form's data source,
and place text boxes or similar controls at suitable places on the Form.

OK, now let's go the other direction -- to offspring. Since I'm
distinguishing between toms and queens, I define a Query for the offspring of
queens:

[Q_020ChildF] SQL:

SELECT Parent.Kitties_ID, Parent.Name,
Child.Kitties_ID AS Offspring_Kitties_ID
FROM Kitties AS Child
INNER JOIN Kitties AS Parent
ON Child.Dam = Parent.Kitties_ID;

Running this produces a list like this:

[Q_020ChildF] Query Datasheet View:

Kitties_ID Name Offspring_Kitties_ID
---------- ------- --------------------
-776932656 Cutie 481390556
-224409238 Zinger -41180253
-220441218 Lioness 341341803
-220441218 Lioness -224409238
289370756 ?? 289370756
289370756 ?? 369735989
289370756 ?? -1604637614
289370756 ?? -220441218
369735989 Puff -776932656
369735989 Puff 2051116689
369735989 Puff 548426157
548426157 Pretty 252328743

For offspring of toms, we use the same SQL, with a minor change in the last
line.

[Q_030ChildM] SQL:

SELECT Parent.Kitties_ID, Parent.Name,
Child.Kitties_ID AS Offspring_Kitties_ID
FROM Kitties AS Child
INNER JOIN Kitties AS Parent
ON Child.Sire=Parent.Kitties_ID;

The list will be different from [Q_020ChildF], but the format is the same.

OK, we want a list of all offspring of each kitty, regardless of sex, and we
can do that with a Union Query:

[Q_040Offspring] SQL:

SELECT * FROM Q_020ChildF
UNION SELECT * FROM Q_030ChildM
ORDER BY Name;

The combined list looks like this. I used key values for offspring instead
of names, since I figured you wouldn't have lots of use for this exact
format. Names get added in the next step.

[Q_040Offspring] Query Datasheet View:

Kitties_ID Name Offspring_Kitties_ID
---------- ------- --------------------
289370756 ?? -1604637614
289370756 ?? -220441218
289370756 ?? 289370756
289370756 ?? 369735989
-776932656 Cutie 481390556
341341803 Growler 252328743
341341803 Growler 481390556
-220441218 Lioness -224409238
-220441218 Lioness 341341803
548426157 Pretty 252328743
369735989 Puff -776932656
369735989 Puff 548426157
369735989 Puff 2051116689
-1604637614 Tiger -776932656
-1604637614 Tiger 341341803
-1604637614 Tiger 548426157
-1604637614 Tiger 2051116689
2051116689 Tugger -224409238
2051116689 Tugger -41180253
-224409238 Zinger -41180253

What I want next is to assign a sequential number (random order here, but
with a bit of effort you could sort them by date or name) to each offspring
of a given parent, for the purpose of listing them later.

[Q_050OffspringNumber] SQL:

SELECT QO1.Kitties_ID, QO1.Name,
QO1.Offspring_Kitties_ID,
K.Name AS Off_Name,
Count(QO2.Kitties_ID) AS Sequence
FROM (Q_040Offspring AS QO1
INNER JOIN Q_040Offspring AS QO2
ON QO1.Kitties_ID = QO2.Kitties_ID)
INNER JOIN Kitties AS K
ON QO1.Offspring_Kitties_ID = K.Kitties_ID
WHERE (((QO2.Offspring_Kitties_ID)
<=[QO1]![Offspring_Kitties_ID]))
GROUP BY QO1.Kitties_ID, QO1.Name,
QO1.Offspring_Kitties_ID, K.Name
ORDER BY QO1.Name, Count(QO2.Kitties_ID);

So here you get the name and the sequence number.

[Q_050OffspringNumber] Query Datasheet View:

Kitties_ID Name Offspring_ Off_Name Sequence
Kitties_ID
----------- ------- ----------- -------- --------
289370756 ?? -1604637614 Tiger 1
289370756 ?? -220441218 Lioness 2
289370756 ?? 289370756 ?? 3
289370756 ?? 369735989 Puff 4
-776932656 Cutie 481390556 Mouser 1
341341803 Growler 252328743 Striper 1
341341803 Growler 481390556 Mouser 2
-220441218 Lioness -224409238 Zinger 1
-220441218 Lioness 341341803 Growler 2
548426157 Pretty 252328743 Striper 1
369735989 Puff -776932656 Cutie 1
369735989 Puff 548426157 Pretty 2
369735989 Puff 2051116689 Tugger 3
-1604637614 Tiger -776932656 Cutie 1
-1604637614 Tiger 341341803 Growler 2
-1604637614 Tiger 548426157 Pretty 3
-1604637614 Tiger 2051116689 Tugger 4
2051116689 Tugger -224409238 Zinger 1
2051116689 Tugger -41180253 Bitsy 2
-224409238 Zinger -41180253 Bitsy 1

Now let's reorganize these so that, for each parent in our Table, that
parent's offspring are combined into one record.

[Q_060OffspringXtab] SQL:

TRANSFORM First(QON.Off_Name) AS FON
SELECT QON.Name, QON.Kitties_ID
FROM Q_050OffspringNumber AS QON
GROUP BY QON.Name, QON.Kitties_ID
PIVOT QON.Sequence;

[Q_060OffspringXtab] Query Datasheet View:

Name Kitties_ID 1 2 3 4
------- ----------- ------- ------- ------ --------
?? 289370756 Tiger Lioness ?? Puff
Cutie -776932656 Mouser
Growler 341341803 Striper Mouser
Lioness -220441218 Zinger Growler
Pretty 548426157 Striper
Puff 369735989 Cutie Pretty Tugger
Tiger -1604637614 Cutie Growler Pretty Tugger
Tugger 2051116689 Zinger Bitsy
Zinger -224409238 Bitsy

Access gives you lots of freedom in deciding how to organize your stuff.
Here, I limited that freedom somewhat in allowing, for example, only a
couple of choices for [Sex], and in requiring the [Sire] and [Dam] fields to
contain only values that match the [Kitties_ID] field of some existing record
in [Kitties]. (This won't guarantee that the recorded information is
accurate, but it can help avoid some types of careless mistakes, such as
leaving the field empty.) How I enforced the rules is not evident in what
I've shown you, but if you wish to do that, look up "Validation Rule" and
"Referential Integrity" in Access Help. (Or, you could simply be careful,
and never make data-entry mistakes.)

Good luck, and pet some kittycats for me.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

Okay, I should have waited to post before, but now I'm having another
problem. In your second and third queries there are tables and field names
mentioned that don't exist. Your query still runs but when I try and place my
fields from my table in it and alter it, it won't work. How is that?

~Stoffelees

 

[Vincent Johns]

Stoffelees,

As you probably noticed, I did a sneaky trick. I had the kitty whose
name is "??" be his own ancestor, so with this Table, we could display
12 generations and not run out of ancestors. (Of course, the imaginary
kittycat "??" would appear many times in such a list.)

But if you want the one Query to display different kinds of results
depending on which cat is involved, I don't know how to do that.

To hide the names of ancestors for which you don't have records, I
suggest changing the name "??" to just " " and making that the default
parent of any new cat. You can change it when you determine who the
parents are. The " " values will not clutter up your lists.

I suggest that if you want exactly 4 generations, you modify the Query I
gave you to display exactly 4 generations (but it will be kind of
messy). You can filter out the kitties with fewer generations on record
by specifying a suitable criterion in the Criteria property for one of
the fields (e.g., to say that, to be included in the list, the cat's
sire's sire should not be the one named "??").

To display only one cat's pedigree, specify a criterion similar to
[Name] = "Mouser" .

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

So, I created the entire database you made and mimicked/molded it to work
with the table I had created. I finally figured out why neither the one you
made or the one I mentioned earlier wouldn't work. I needed to have at least
3 generations. So, I understand how to get it to show 3 generations, now how
do I get it to show 2, 3, 4, however many I want (meaning it will vary with
each cat) with only one main query to run? And how can I get it to display
only one cat's pedigree at a time on the form? I'm sorry if I seem to
ignorant, but I'm doing my best to learn.

Sincerely,
Miss Stoffelees



:

Dear Mr./Miss Stoffelees,

Here's my version of your initial pedigree query. I had to set up a Table,
of course, to provide a meaningful example, but I expect you may want to make
changes to it. For example, maybe date of birth (whelping/queening/hatching,
&c.) is not of importance to you, so you could just zap that. (But for now,
don't erase it, as some of my Queries depend on it.)

Let's start with the Table that contains all of the (fake) data used in this
example. You should be able to reproduce everything here using your own copy
of Access. (Sorry, I had to fold this Data sheet, and most of the others, to
avoid having the lines get folded automatically at unhappy places.)

The [Kitties_ID] field is an Auto number type, meaning that it's generated
automatically and guaranteed to be unique. You might choose some other type
ID field, such as a name, but then you'd have to be sure you never reused a
name, and you'd have to type it yourself. (Also, Auto numbers take only 4
bytes, while names are usually longer, a consideration if you have big enough
Tables that size is a problem.)

[Sire] and [Dam] fields contain "foreign key" values identifying which
record in [Kitties] identifies a parent of the kitty in the current record.
For example, in this Table, the record for "Pretty" (record # 548426157) has
a [Sire] field of -1604637614, which refers to the record for "Tiger".

[Kitties] Table Datasheet View:

Kitties_ID Name Sex Sire
----------- ------- --- -----------
289370756 ?? 289370756
-220441218 Lioness F 289370756
-1604637614 Tiger M 289370756
369735989 Puff F 289370756
548426157 Pretty F -1604637614
2051116689 Tugger M -1604637614
-776932656 Cutie F -1604637614
341341803 Growler M -1604637614
-224409238 Zinger F 2051116689
481390556 Mouser M 341341803
-41180253 Bitsy F 2051116689
252328743 Striper M 341341803

Dam Born
---------- --------
289370756 1/1/1950
289370756 1/1/1990
289370756 1/1/1990
289370756 1/1/1990
369735989 1/1/1996
369735989 1/1/1996
369735989 1/1/1996
-220441218 1/1/1997
-220441218 1/1/1998
-776932656 1/1/1999
-224409238 1/1/2000
548426157 1/1/2000

Well, that's it for the "data". The remaining examples are all based on
these. (For now, let's assume that inbreeding is not a problem for virtual
cats.)

Let's say that we want to list the parents & grandparents of everyone in our
collection. The following Query goes back 2 generations, but you could extend
it if you wish.

[Q_010Ancestors] SQL:

SELECT [Kitties].[Name], [Kitties].[Kitties_ID],
KS.Name AS S, KD.Name AS D, KSS.Name AS SS,
KSD.Name AS SD, KDS.Name AS DS, KDD.Name AS DD
FROM (((((Kitties
INNER JOIN Kitties AS KS
ON [Kitties].[Sire]=KS.Kitties_ID)
INNER JOIN Kitties AS KD
ON [Kitties].[Dam]=KD.Kitties_ID)
INNER JOIN Kitties AS KSS
ON KS.Sire=KSS.Kitties_ID)
INNER JOIN Kitties AS KSD
ON KS.Dam=KSD.Kitties_ID)
INNER JOIN Kitties AS KDS
ON KD.Sire=KDS.Kitties_ID)
INNER JOIN Kitties AS KDD
ON KD.Dam=KDD.Kitties_ID
ORDER BY [Kitties].[Born];

Running this gives the following list of ancestors. "S" refers to sire,
"DS" refers to dam's sire, etc.

[Q_010Ancestors] Query Datasheet View:

Name Kitties_ID S D SS
------- ----------- ------- ------- -----
?? 289370756 ?? ?? ??
Lioness -220441218 ?? ?? ??
Tiger -1604637614 ?? ?? ??
Puff 369735989 ?? ?? ??
Pretty 548426157 Tiger Puff ??
Tugger 2051116689 Tiger Puff ??
Cutie -776932656 Tiger Puff ??
Growler 341341803 Tiger Lioness ??
Zinger -224409238 Tugger Lioness Tiger
Mouser 481390556 Growler Cutie Tiger
Bitsy -41180253 Tugger Zinger Tiger
Striper 252328743 Growler Pretty Tiger

SD DS DD
------- ------ -------
?? ?? ??
?? ?? ??
?? ?? ??
?? ?? ??
?? ?? ??
?? ?? ??
?? ?? ??
?? ?? ??
Puff ?? ??
Lioness Tiger Puff
Puff Tugger Lioness
Lioness Tiger Puff

In a Form, I assume you'd use a Query like this as the Form's data source,
and place text boxes or similar controls at suitable places on the Form.

OK, now let's go the other direction -- to offspring. Since I'm
distinguishing between toms and queens, I define a Query for the offspring of
queens:

[Q_020ChildF] SQL:

SELECT Parent.Kitties_ID, Parent.Name,
Child.Kitties_ID AS Offspring_Kitties_ID
FROM Kitties AS Child
INNER JOIN Kitties AS Parent
ON Child.Dam = Parent.Kitties_ID;

Running this produces a list like this:

[Q_020ChildF] Query Datasheet View:

Kitties_ID Name Offspring_Kitties_ID
---------- ------- --------------------
-776932656 Cutie 481390556
-224409238 Zinger -41180253
-220441218 Lioness 341341803
-220441218 Lioness -224409238
289370756 ?? 289370756
289370756 ?? 369735989
289370756 ?? -1604637614
289370756 ?? -220441218
369735989 Puff -776932656
369735989 Puff 2051116689
369735989 Puff 548426157
548426157 Pretty 252328743

For offspring of toms, we use the same SQL, with a minor change in the last
line.

[Q_030ChildM] SQL:

SELECT Parent.Kitties_ID, Parent.Name,
Child.Kitties_ID AS Offspring_Kitties_ID
FROM Kitties AS Child
INNER JOIN Kitties AS Parent
ON Child.Sire=Parent.Kitties_ID;

The list will be different from [Q_020ChildF], but the format is the same.

OK, we want a list of all offspring of each kitty, regardless of sex, and we
can do that with a Union Query:

[Q_040Offspring] SQL:

SELECT * FROM Q_020ChildF
UNION SELECT * FROM Q_030ChildM
ORDER BY Name;

The combined list looks like this. I used key values for offspring instead
of names, since I figured you wouldn't have lots of use for this exact
format. Names get added in the next step.

[Q_040Offspring] Query Datasheet View:

Kitties_ID Name Offspring_Kitties_ID
---------- ------- --------------------
289370756 ?? -1604637614
289370756 ?? -220441218
289370756 ?? 289370756
289370756 ?? 369735989
-776932656 Cutie 481390556
341341803 Growler 252328743
341341803 Growler 481390556
-220441218 Lioness -224409238
-220441218 Lioness 341341803
548426157 Pretty 252328743
369735989 Puff -776932656
369735989 Puff 548426157
369735989 Puff 2051116689
-1604637614 Tiger -776932656
-1604637614 Tiger 341341803
-1604637614 Tiger 548426157
-1604637614 Tiger 2051116689
2051116689 Tugger -224409238
2051116689 Tugger -41180253
-224409238 Zinger -41180253

What I want next is to assign a sequential number (random order here, but
with a bit of effort you could sort them by date or name) to each offspring
of a given parent, for the purpose of listing them later.

[Q_050OffspringNumber] SQL:

SELECT QO1.Kitties_ID, QO1.Name,
QO1.Offspring_Kitties_ID,
K.Name AS Off_Name,
Count(QO2.Kitties_ID) AS Sequence
FROM (Q_040Offspring AS QO1
INNER JOIN Q_040Offspring AS QO2
ON QO1.Kitties_ID = QO2.Kitties_ID)
INNER JOIN Kitties AS K
ON QO1.Offspring_Kitties_ID = K.Kitties_ID
WHERE (((QO2.Offspring_Kitties_ID)
<=[QO1]![Offspring_Kitties_ID]))
GROUP BY QO1.Kitties_ID, QO1.Name,
QO1.Offspring_Kitties_ID, K.Name
ORDER BY QO1.Name, Count(QO2.Kitties_ID);

So here you get the name and the sequence number.

[Q_050OffspringNumber] Query Datasheet View:

Kitties_ID Name Offspring_ Off_Name Sequence
Kitties_ID
----------- ------- ----------- -------- --------
289370756 ?? -1604637614 Tiger 1
289370756 ?? -220441218 Lioness 2
289370756 ?? 289370756 ?? 3
289370756 ?? 369735989 Puff 4
-776932656 Cutie 481390556 Mouser 1
341341803 Growler 252328743 Striper 1
341341803 Growler 481390556 Mouser 2
-220441218 Lioness -224409238 Zinger 1
-220441218 Lioness 341341803 Growler 2
548426157 Pretty 252328743 Striper 1
369735989 Puff -776932656 Cutie 1
369735989 Puff 548426157 Pretty 2
369735989 Puff 2051116689 Tugger 3
-1604637614 Tiger -776932656 Cutie 1
-1604637614 Tiger 341341803 Growler 2
-1604637614 Tiger 548426157 Pretty 3
-1604637614 Tiger 2051116689 Tugger 4
2051116689 Tugger -224409238 Zinger 1
2051116689 Tugger -41180253 Bitsy 2
-224409238 Zinger -41180253 Bitsy 1

Now let's reorganize these so that, for each parent in our Table, that
parent's offspring are combined into one record.

[Q_060OffspringXtab] SQL:

TRANSFORM First(QON.Off_Name) AS FON
SELECT QON.Name, QON.Kitties_ID
FROM Q_050OffspringNumber AS QON
GROUP BY QON.Name, QON.Kitties_ID
PIVOT QON.Sequence;

[Q_060OffspringXtab] Query Datasheet View:

Name Kitties_ID 1 2 3 4
------- ----------- ------- ------- ------ --------
?? 289370756 Tiger Lioness ?? Puff
Cutie -776932656 Mouser
Growler 341341803 Striper Mouser
Lioness -220441218 Zinger Growler
Pretty 548426157 Striper
Puff 369735989 Cutie Pretty Tugger
Tiger -1604637614 Cutie Growler Pretty Tugger
Tugger 2051116689 Zinger Bitsy
Zinger -224409238 Bitsy

Access gives you lots of freedom in deciding how to organize your stuff.
Here, I limited that freedom somewhat in allowing, for example, only a
couple of choices for [Sex], and in requiring the [Sire] and [Dam] fields to
contain only values that match the [Kitties_ID] field of some existing record
in [Kitties]. (This won't guarantee that the recorded information is
accurate, but it can help avoid some types of careless mistakes, such as
leaving the field empty.) How I enforced the rules is not evident in what
I've shown you, but if you wish to do that, look up "Validation Rule" and
"Referential Integrity" in Access Help. (Or, you could simply be careful,
and never make data-entry mistakes.)

Good luck, and pet some kittycats for me.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

Stoffelees,

That's because I gave them aliases, to cut down on the length of the
SQL. Remember, there is only one Table in my example, but a reference
to it could be a reference to a parent or a grandparent, etc., so
another reason for using the aliases was to help keep track of just what
each reference meant.

For example, in my second Query,

[Q_020ChildF] SQL:

SELECT Parent.Kitties_ID, Parent.Name,
Child.Kitties_ID AS Offspring_Kitties_ID
FROM Kitties AS Child
INNER JOIN Kitties AS Parent
ON Child.Dam = Parent.Kitties_ID;

you'll notice that it refers to a [Parent] Table, but there is no
[Parent] Table in the database. But there IS a reference to "Kitties AS
Parent"; what follows the AS is the alias, and you can use that to keep
track of what the particular link means.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

Thank you so much, you have no idea how much you have helped me. I've learned
more from your responses than I did with all of Dell's beginner training
courses for Access, which may be a good thing or a bad thing depending on how
you look at it XD

Sincerely,
Miss Stoffelees

Stoffelees,

That's because I gave them aliases, to cut down on the length of the
SQL. Remember, there is only one Table in my example, but a reference
to it could be a reference to a parent or a grandparent, etc., so
another reason for using the aliases was to help keep track of just what
each reference meant.

For example, in my second Query,

[Q_020ChildF] SQL:

SELECT Parent.Kitties_ID, Parent.Name,
Child.Kitties_ID AS Offspring_Kitties_ID
FROM Kitties AS Child
INNER JOIN Kitties AS Parent
ON Child.Dam = Parent.Kitties_ID;

you'll notice that it refers to a [Parent] Table, but there is no
[Parent] Table in the database. But there IS a reference to "Kitties AS
Parent"; what follows the AS is the alias, and you can use that to keep
track of what the particular link means.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Okay, I should have waited to post before, but now I'm having another
problem. In your second and third queries there are tables and field names
mentioned that don't exist. Your query still runs but when I try and place my
fields from my table in it and alter it, it won't work. How is that?

~Stoffelees

 

[Vincent Johns]

Stoffelees,

Don't be too hard on Dell's training; what you're doing is a bit
unusual, and with a tool as versatile as Access you (any of us) can
spend just years learning about new ways to use it to solve problems. I
suggest that, when you have time, you use the Database Wizard to build
some of the sample databases provided (use File --> New --> "Databases"
tab --> select one of the samples). Also, you might look at the
"Northwind Traders" sample database for ideas -- but I should warn you
that it might make you hungry.

Incidentally, as I was answering your question, I had assistance
from a striped kitty who figured that his being petted was just as
important as whatever I was doing with the computer. Have fun with your
project!

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Thank you so much, you have no idea how much you have helped me. I've learned
more from your responses than I did with all of Dell's beginner training
courses for Access, which may be a good thing or a bad thing depending on how
you look at it XD

Sincerely,
Miss Stoffelees

:

Stoffelees,

That's because I gave them aliases, to cut down on the length of the
SQL. Remember, there is only one Table in my example, but a reference
to it could be a reference to a parent or a grandparent, etc., so
another reason for using the aliases was to help keep track of just what
each reference meant.

For example, in my second Query,

[Q_020ChildF] SQL:

SELECT Parent.Kitties_ID, Parent.Name,
Child.Kitties_ID AS Offspring_Kitties_ID
FROM Kitties AS Child
INNER JOIN Kitties AS Parent
ON Child.Dam = Parent.Kitties_ID;

you'll notice that it refers to a [Parent] Table, but there is no
[Parent] Table in the database. But there IS a reference to "Kitties AS
Parent"; what follows the AS is the alias, and you can use that to keep
track of what the particular link means.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Stoffelees wrote:

Okay, I should have waited to post before, but now I'm having another
problem. In your second and third queries there are tables and field names
mentioned that don't exist. Your query still runs but when I try and place my
fields from my table in it and alter it, it won't work. How is that?

~Stoffelees