Tags: access, address, base, cant, children, creat, database, dates, drop, family, microsoft, mysql, oracle, page, pagewith, parent, phones, sql, track
HELP! I can't keep track family address, phones, dates, etc.
On Database » Microsoft Access
12,597 words with 9 Comments; publish: Sun, 01 Jun 2008 16:37:00 GMT; (25093.75, « »)
I want to creat an Access Data base that would start with my parent(s) page
with their page having a drop down list of their children (me and 6 others)
linking to an individual child page of our info, further linking to pages for
our spouse, children and so on for a couple of generations. Each individuals
pages would have fields for name, spouse, addresses (home, company name/work,
vacation home) phone numbers (home, work, fax, cell, other) Date of birth
(with current age calculation automatically inserted) Date of Death, date of
marriage, e-mail addresses, etc. Reports would be an address book,
alphabetical lookup by first or last names, selected mailing labels, etc. It
would be neat to have the cover page have areas (button) for Mom/Dad photo,
each child, by clicking the photo you go to that persons main info page with
spouse and child links from drop down lists....
http://ms-access.itags.org/q_ms-access-database_85840.html
All Comments
Leave a comment...
- 9 Comments
- To store the data have one table, tblPerson, for every one
uidPerson
Name
Forename
DOBirth
DODeath
Photo
fkPerson_Father
fkPerson_Mother
fkPerson_Spouse
fkPerson_Father and fkPerson_Mother fkPerson_Spouse relate back to uidPerson
(in a self-join / pigs-ear relationship).
This is only a start
HTH, Graeme.
#1; Sun, 01 Jun 2008 16:38:00 GMT
- Hello,
Thank you for your response but being new to Access I really do not
understand what you mean or what the list of items you listed means.
uidperson, fkperson, etc. Could you further expound on this? I don't have
a clue where to start from your information. THANKS!
Ted Pagels
FIREPROSe, LLC
"Graeme Richardson" wrote:
> To store the data have one table, tblPerson, for every one
> uidPerson
> Name
> Forename
> DOBirth
> DODeath
> Photo
> fkPerson_Father
> fkPerson_Mother
> fkPerson_Spouse
> fkPerson_Father and fkPerson_Mother fkPerson_Spouse relate back to uidPerson
> (in a self-join / pigs-ear relationship).
> This is only a start
> HTH, Graeme.
>
>
#2; Sun, 01 Jun 2008 16:39:00 GMT
- One Relational table can be used to store every (biological) ancestor for a
person.
uid is unique id / primary key (use AutoNumber data type)
fk is foreign key (link to a primary key in a related table) (use Data Type:
Number; Field Size: Long Integer)
Create the one table, call it tblPerson.
Open the relationships window (Tools > Relationships menu from the Database
container window) and add the table 4 times (it'll appear as tblPerson,
tblPerson_1, tblPerson_2, tblPerson_3)
Drag a link from (and, for completeness enforce referential integrity):
tblPerson_1.fkPerson_Father to tblPerson.uidPerson
tblPerson_2.fkPerson_Mother to tblPerson.uidPerson
tblPerson_3.fkPerson_Spouse to tblPerson.uidPerson
so, looking at a person record, you can trace who the father is/was by
taking the value from the fkPerson_Father field and searching the table for
that primary key value.
Try it, come back with questions.
Graeme.
#3; Sun, 01 Jun 2008 16:40:00 GMT
- Hello,
I'm so sorry but I just don't understand what you are trying to tell me what
to do where do I start, with a template or what? Again I'm new to Access. I
do not know what to try. Do you have a template available to start from that
you can e-mail to me or I can look up someplace easy? I don't know what a
relational table is or the other abbreviated abbreviations such as foriegn
key?
"Graeme Richardson" wrote:
> One Relational table can be used to store every (biological) ancestor for a
> person.
> uid is unique id / primary key (use AutoNumber data type)
> fk is foreign key (link to a primary key in a related table) (use Data Type:
> Number; Field Size: Long Integer)
> Create the one table, call it tblPerson.
> Open the relationships window (Tools > Relationships menu from the Database
> container window) and add the table 4 times (it'll appear as tblPerson,
> tblPerson_1, tblPerson_2, tblPerson_3)
> Drag a link from (and, for completeness enforce referential integrity):
> tblPerson_1.fkPerson_Father to tblPerson.uidPerson
> tblPerson_2.fkPerson_Mother to tblPerson.uidPerson
> tblPerson_3.fkPerson_Spouse to tblPerson.uidPerson
> so, looking at a person record, you can trace who the father is/was by
> taking the value from the fkPerson_Father field and searching the table for
> that primary key value.
> Try it, come back with questions.
> Graeme.
>
>
#4; Sun, 01 Jun 2008 16:41:00 GMT
- Hello,
I'm so sorry but I just don't understand what you are trying to tell me what
to do where do I start, with a template or what? Again I'm new to Access. I
do not know what to try. Do you have a template available to start from that
you can e-mail to me or I can look up someplace easy? I don't know what a
relational table is or the other abbreviated abbreviations such as foriegn
key?
"Graeme Richardson" wrote:
> One Relational table can be used to store every (biological) ancestor for a
> person.
> uid is unique id / primary key (use AutoNumber data type)
> fk is foreign key (link to a primary key in a related table) (use Data Type:
> Number; Field Size: Long Integer)
> Create the one table, call it tblPerson.
> Open the relationships window (Tools > Relationships menu from the Database
> container window) and add the table 4 times (it'll appear as tblPerson,
> tblPerson_1, tblPerson_2, tblPerson_3)
> Drag a link from (and, for completeness enforce referential integrity):
> tblPerson_1.fkPerson_Father to tblPerson.uidPerson
> tblPerson_2.fkPerson_Mother to tblPerson.uidPerson
> tblPerson_3.fkPerson_Spouse to tblPerson.uidPerson
> so, looking at a person record, you can trace who the father is/was by
> taking the value from the fkPerson_Father field and searching the table for
> that primary key value.
> Try it, come back with questions.
> Graeme.
>
>
#5; Sun, 01 Jun 2008 16:42:00 GMT
- MS Access has a relational database engine called jet.
The terms I've supplied are common expressions used in Relational database
design.
Relational databases is a large subject. For an introduction try the help
files.
Microsoft have online tutorials also. This is just one link (I haven't
studued the whole 'course')
http://office.microsoft.com/training...RC061181381033
I don't think there's a wizard to create the table. You could start by
uisiong the wizard to create a contact table and modify it.
Graeme.
#6; Sun, 01 Jun 2008 16:43:00 GMT
- "Firepros.ms-access.itags.org.aol.com" <Fireprosaolcom.ms-access.itags.org.discussions.microsoft.com> wrote in
message news:58BEC68D-6249-4926-8022-CDD92017DF3A.ms-access.itags.org.microsoft.com...
> Hello,
> I'm so sorry but I just don't understand what you are trying to tell
me what
> to do where do I start, with a template or what? Again I'm new to
Access. I
> do not know what to try. Do you have a template available to start
from that
> you can e-mail to me or I can look up someplace easy? I don't know
what a
> relational table is or the other abbreviated abbreviations such as
foriegn
> key?
>
GraemeR is right. Relational Databases are a *huge* topic. I started
out writing a quick primer on it, and two hours and four pages later
(of trying to make it as simple as I could), I realized that nothing I
could put in anything less than a chapter length work could even hope
to scrath the surface (and I was doing my best to illustrate the
difference between MS Access and most RDBMS software along the way . .
..).
Sincerely,
Chris O.
#7; Sun, 01 Jun 2008 16:44:00 GMT
- Thanks so much I'll try that! PEACE!
"GraemeR" wrote:
> MS Access has a relational database engine called jet.
> The terms I've supplied are common expressions used in Relational database
> design.
> Relational databases is a large subject. For an introduction try the help
> files.
> Microsoft have online tutorials also. This is just one link (I haven't
> studued the whole 'course')
> http://office.microsoft.com/training...RC061181381033
> I don't think there's a wizard to create the table. You could start by
> uisiong the wizard to create a contact table and modify it.
> Graeme.
>
>
#8; Sun, 01 Jun 2008 16:45:00 GMT
- On Wed, 26 Jan 2005 16:11:07 -0800, "Firepros.ms-access.itags.org.aol.com"
<Firepros.ms-access.itags.org.aol.com.ms-access.itags.org.discussions.microsoft.com> wrote:
>I want to creat an Access Data base that would start with my parent(s) page
>with their page having a drop down list of their children (me and 6 others)
>linking to an individual child page of our info, further linking to pages for
>our spouse, children and so on for a couple of generations. Each individuals
>pages would have fields for name, spouse, addresses (home, company name/work,
>vacation home) phone numbers (home, work, fax, cell, other) Date of birth
>(with current age calculation automatically inserted) Date of Death, date of
>marriage, e-mail addresses, etc. Reports would be an address book,
>alphabetical lookup by first or last names, selected mailing labels, etc. It
>would be neat to have the cover page have areas (button) for Mom/Dad photo,
>each child, by clicking the photo you go to that persons main info page with
>spouse and child links from drop down lists....
I hope Graeme will forgive me for jumping in here. His advice is (as
usual) right on the mark but I see you're having difficulty with the
jargon.
In Access, you need to design your database starting with the Tables.
You need to identify the "Entities" - real-life people, things, or
events - of importance to your application; each kind of Entity gets
its own Table. You then need to figure out the relationships between
the entities - which, in the case of geneology, can be surprisingly
complicated (step-parents, just for example: not only can one person
have several children, one child might - like me - have two mothers as
well as a father).
Don't jump into designing your data entry forms or your reports too
soon! The first thing to do is to set up Tables to store the
information you want. These constitute the foundation of your
building; the forms and reports are the superstructure and come later.
I'd start with a People table:
People
PersonID <Autonumber, a unique identifier for a given person>
BirthLastName
BirthFirstName
CurrentLastName <married name, or for name changes>
CurrentFirstName <some people change their name of course>
Nickname
DateOfBirth [Date/Time] <note that you can't use approximate dates>
DateOfDeath
Addresses
AddressID <autonumber>
Address1
Address2
City
StateProvince
PostCode <don't use Zip, you might have relatives in Canada or
Zimbabwe>
PeopleAddresses
PersonID
AddressID
MailingAddress Yes/No
<this lets multiple people have the same address, or lets one person
have several addresses>
RelationshipTypes
RelationshipType Text <Spouse, Sibling, Child, Parent>
Relationships
PersonID1
PersonID2
RelationshipType
RelationshipDate
RelationshipEndDate
Note that you should not put a MarriageDate in the People table;
especially these days, a given person might have zero, one, two, or
more marriagedates. Instead the date that a marriage relationship
begins is a property OF THE RELATIONSHIP, not of either person.
You'll have a fair bit of work setting up Forms and Subforms for this,
but it's all doable.
John W. Vinson[MVP]
#9; Sun, 01 Jun 2008 16:46:00 GMT