Access database set up querys, Guru required

[soldierB]

Hello friends

I an trying to work with my Access database, I want to take the info from my main table and pull querys from that main table to build up slightly more complex querys.

But Its not working!!

Here is what im doing.
The main table holds peoples names and other personal details.

Each row has an ID number, also each row has a [father] and [mother] field that holds the [ID] number of the father or mother the Id number relates to the number in the [ID] Field.

So what I want to do is pull a query that will show the person in question along with his parents and thier parents etc etc......like a big family tree/pedigree style idea.

Its all started out OK but as im building my querys ive got to one where 2 or 3 of the fields are not pulling any data?? I dont know why ive looked at it all over and over.

I wonder if any of you Guys can assist me here.
I dont think I can attach my database here buy I can email it to anyone who can help its only small.

Any assistance is greatly appreciated
Thank you SB..

 

[lespaul]

So you have something like this:

PersonID      FirstName     LastName      FatherID      MotherID
1             Jane          Doe
2             John          Doe
3             Baby1         Doe             2           1
4             Baby2         Doe             2           1
5             Jill          Smith
6             David         Smith
7             Daughter      Smith           6           5
8             Son           Smith           6           5

and you want to show:

PersonName            FatherName            MotherName
Baby1 Doe             John Doe              Jane Doe
Baby2 Doe             John Doe              Jane Doe
Daughter Smith        Jill Smith            David Smith
Son Smith             Jill Smith            David Smith

Is that what you mean?

What would you want it to show for those that are your 'origins' and you don't have their mother and father?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[soldierB]

Yea I think thats what Im trying to do, would you mind having a look for me?

Thanks for the reply

 

[lespaul]

something like this:


SELECT FirstName + ' ' + LastName As ChildName, A.FirstName + ' ' A.LastName As MotherName, B.FirstName + ' ' + B.LastName As FatherName
FROM TableName
LEFT JOIN TableName A on TableName.MotherID = A.PersonID
LEFT JOIN TableName B on TableName.FatherID = B.PersonID

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

You need 3 instances of your table (so 3 different alias) with 2 outer joins, e.g.:
SELECT P.ID As PersonID, P.Name As PersonName, father As FatherID, F.Name As FatherName, mother As MotherID, F.Name As MotherName
FROM (yourTable As P
LEFT JOIN yourTable As F on P.father = F.ID)
LEFT JOIN yourTable As M on P.mother = M.ID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[soldierB]

Hi Leslie,

As I say the querys I have are workin, each one I make pulls data from the last one and adds more data,

Im no expert and couldnt manage to write my own SQL for this but here is what the access SQL behind my last query looks like.
As I say it is pulling the data but the first 3-4 fields are coming through blank with no data and the rest seems fine.
I looked back at the preveious query I made and its fine so tis the one before that.

Here is my Accesss SQL perhaps you can read it, its getting to complex for me

SELECT Query2.*, qryParents.*, qryParents_1.*, qryParents_2.*, qryParents_3.*, qryParents_4.*, qryParents_5.*, qryParents_6.*, qryParents_7.*
FROM (((((((Query2 INNER JOIN qryParents ON Query2.qryParents.Father = qryParents.Id) INNER JOIN qryParents AS qryParents_1 ON Query2.qryParents.Mother = qryParents_1.Id) INNER JOIN qryParents AS qryParents_2 ON Query2.qryParents_1.Father = qryParents_2.Id) INNER JOIN qryParents AS qryParents_3 ON Query2.qryParents_1.Mother = qryParents_3.Id) INNER JOIN qryParents AS qryParents_4 ON Query2.qryParents_2.Father = qryParents_4.Id) INNER JOIN qryParents AS qryParents_5 ON Query2.qryParents_2.Mother = qryParents_5.Id) INNER JOIN qryParents AS qryParents_6 ON Query2.qryParents_3.Father = qryParents_6.Id) INNER JOIN qryParents AS qryParents_7 ON Query2.qryParents_3.Mother = qryParents_7.Id;

Thanks again

 

[lespaul]

The query that both PHV and I wrote would work with the table structure I outlined above. You indicated that what I had assumed and laid out for you was correct. If you will "show" us your actual table layout (as I did above), then we can help you. Without your telling us the SQL statement of Query2, qryParents, qryParents_1, qryParents_2, etc. it's really impossible to help you. What we need is the TABLE structure that stores the person and their parents.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

Try to replace all INNER JOIN with LEFT JOIN

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[soldierB]

Thanks PHV

But access is making up the querys, Im just Visually bulding the querys using the access interface so how can I change it?

Thanks again

Guys remember im a bit of a newbie here.
Regards to you all.

 

[PHV]

The SQL view pane of the query window acts like an editor.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[soldierB]

Thanks Leslie,

Is there somewhere I can upload my database to for you guys to check it out?

Regards as ever

 

[soldierB]

Hi PHV,

I made the changes as you suggested, and the query still runs but the same fields are missing data??

SELECT Query2.*, qryParents.*, qryParents_1.*, qryParents_2.*, qryParents_3.*, qryParents_4.*, qryParents_5.*, qryParents_6.*, qryParents_7.*
FROM (((((((Query2 LEFT JOIN qryParents ON Query2.qryParents.Father=qryParents.Id) LEFT JOIN qryParents AS qryParents_1 ON Query2.qryParents.Mother=qryParents_1.Id) LEFT JOIN qryParents AS qryParents_2 ON Query2.qryParents_1.Father=qryParents_2.Id) LEFT JOIN qryParents AS qryParents_3 ON Query2.qryParents_1.Mother=qryParents_3.Id) LEFT JOIN qryParents AS qryParents_4 ON Query2.qryParents_2.Father=qryParents_4.Id) LEFT JOIN qryParents AS qryParents_5 ON Query2.qryParents_2.Mother=qryParents_5.Id) LEFT JOIN qryParents AS qryParents_6 ON Query2.qryParents_3.Father=qryParents_6.Id) LEFT JOIN qryParents AS qryParents_7 ON Query2.qryParents_3.Mother=qryParents_7.Id;

 

[soldierB]

I will try and outline what I have done, the set up I have is just a smaller scale of what I finally want, as if I can get it to work the main table will hold more fields than I have at the moment, at the moment I have only added a few fields in the main table to work with as they are the only fields I will want to pull through in my querys.

Ok Here goes....

I have 1 table (Main)

Fields are

FieldName DataType

ID : Autonumber
Title : Text
Name : text
Sex : Text
DOB : Text
Father : Number
Mother : Number

Ok now from that I have created 3 querys to get the parents names and details.


qryFather

SELECT Main.Id, Main.Title, Main.Name, Main.Father, Main.Mother
FROM Main;

and

qryMother

SELECT Main.Id, Main.Title, Main.Name, Main.Father, Main.Mother
FROM Main;

and

qryParents

SELECT Main.Id, Main.Title, Main.Name, Main.Father, Main.Mother
FROM Main;

Then to start getting the patents I have done this so far.

Query1 *This works

SELECT Main.*, qryFather.*, qryMother.*
FROM (Main INNER JOIN qryFather ON Main.Father = qryFather.Id) INNER JOIN qryMother ON Main.Mother = qryMother.Id;

Then
Query2 * This works

SELECT Query1.*, qryParents.*, qryParents_1.*, qryParents_2.*, qryParents_3.*
FROM (((Query1 INNER JOIN qryParents ON Query1.qryFather.Father=qryParents.Id) INNER JOIN qryParents AS qryParents_1 ON Query1.qryFather.Mother=qryParents_1.Id) INNER JOIN qryParents AS qryParents_2 ON Query1.qryMother.Father=qryParents_2.Id) INNER JOIN qryParents AS qryParents_3 ON Query1.qryMother.Mother=qryParents_3.Id;

My next Query is where I hit a hurdle!!

Query3

SELECT Query2.*, qryParents.*, qryParents_1.*, qryParents_2.*, qryParents_3.*, qryParents_4.*, qryParents_5.*, qryParents_6.*, qryParents_7.*
FROM (((((((Query2 LEFT JOIN qryParents ON Query2.qryParents.Father=qryParents.Id) LEFT JOIN qryParents AS qryParents_1 ON Query2.qryParents.Mother=qryParents_1.Id) LEFT JOIN qryParents AS qryParents_2 ON Query2.qryParents_1.Father=qryParents_2.Id) LEFT JOIN qryParents AS qryParents_3 ON Query2.qryParents_1.Mother=qryParents_3.Id) LEFT JOIN qryParents AS qryParents_4 ON Query2.qryParents_2.Father=qryParents_4.Id) LEFT JOIN qryParents AS qryParents_5 ON Query2.qryParents_2.Mother=qryParents_5.Id) LEFT JOIN qryParents AS qryParents_6 ON Query2.qryParents_3.Father=qryParents_6.Id) LEFT JOIN qryParents AS qryParents_7 ON Query2.qryParents_3.Mother=qryParents_7.Id;

The following fields in the Query are missing when I view the data in access

Query1.Main.Id
Query1.Main.Title
Query1.Main.Name
Query1.Main.Father
Query1.Main.Mother

The rest of the data is there and correct.
This is not the end of it I had hoped to build on a further 2 Querys doing the same thing to get a full record of all a persons parents..Gparents..gggparents...etc

I hope you guys get what im trying to do, to be honest I find it very hard to follow the Raw SQL.

Thanks again
Regards Sb

 

[lespaul]

have you tried creating a brand new query and using the SQL we provided earlier?

with the table structure you laid out:

I have 1 table (Main)

Fields are

FieldName DataType

ID : Autonumber
Title : Text
Name : text
Sex : Text
DOB : Text
Father : Number
Mother : Number


the SQL (paste in the SQL view of the query)

SELECT M.Name, F.Father As FatherName, B.Mother As MotherName
FROM (Main M
LEFT JOIN Main F on M.ID = F.ID)
LEFT JOIN Main B on M.ID = B.ID

this should return:

ChildName     FatherName     MotherName
Baby1 Doe      John Doe      Jane Doe
Baby2 Doe      John Doe      Jane Doe
Daughter Smith David Smith   Jill Smith
Son Smith      David Smith   Jill Smith

You don't need any of the other queries.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[soldierB]

Hi Lespaul,
I tried that but that SQL doesnt return the parents name it returns their ID numbers.

If it were to return the names that would be fine but its not bringing me in enough data

What I want at the end is a query that will pull the person who you wish to look at, this will be done hopefully on a web page using Recordsets and some SQL to pull a specific record from the querys in the database.

I can do the ASP/VBS side with the recordsets etc, but the database/query set up is whats stopping me.

As I mentioned the fields I have in the database are
Fields are

FieldName DataType

ID : Autonumber
Title : Text
Name : text
Sex : Text
DOB : Text
Father : Number
Mother : Number

But in reality, I will have additional fields in there like address, phoneNo,etc,etc,

But the querys only need to pull the initial database fields I have stated with.

So on my intranet page I will have a list of names pulled from the main table, when the user selects a name that persons family tree will be presented,

It will be laid out in a family tree style as below on an asp page in a table.

Gfather

Father
Gmother

Focus Person
Gfather2

Mother

Gmother2

If the data above was say 2 generations of a persons parentage I would like to show up to 5 or more generations....

What I have attempted up to query2 is giving me the above data correctly, but when I attempt to increase to a further generation(Query3)to get the Great Grandparents the data is not all comming through and some fields are missing data in my Query 3:example above in my last post.

I hope that make sence to you guys, I think we seem to have crossed wires a little.

If im not explaining myself properly or using the correct terms I do appologise, I have some literature here but am realy bewildered a little as as far as I can see what I have is basically what you guys are saying.

This is just a bit of fun as projects go im trying to make up a family tree planner for myself if its possible and as when children are born into the family they can be added to the data and will show up too, Hopefully.....I hope some of you guys can bear with me through this as I am struggeling but willing to learn if it can be done correctly.

Thanks again
Sb

 

[soldierB]

Thanks for your thoughts guys,

I figured out what I was doing wrong!!!!!
When making my querys visually I was not naming the fields, I was simply dragging the fields from the querys I needed and allowed them to name themselves....Individually naming each field through the querys seems to have solved my problem.

Thanks again
Regards
Sb

 

[soldierB]

Sorry,

I thought I had it worked out by naming the fields......I managed to complete query3 and its working.

But making query 4 has brought up new issues, when I pull query3 into query4 its fine initially to view the data, but when I start to add qryParents into this query4 to form the relationships the data is going all wrong???

Nothing is ever easy eh?
I realy thought I had it for a bit!!

Thoughts are still very appreciated.
Thank you
Sb

 

[lespaul]

I think your table structure is flawed (if what i described above is accurate). I believe you would need something like the following setup:

Person
PersonID
Name
Birthplace
DOB
etc.

Relations
RelationID
RelationName

PersonRelations
Person1ID (FK to PersonID)
Person2ID (FK to PersonID)
RelationshipID (FK to RelationID)

Person
ID     Name             Birthplace             DOB
1      Leslie Andrew    Shreveport, LA         01/15/1967
2      Robert G Andrew  Longview, TX           06/22/1944
3      Gail William     Dover, DE              06/29/1944
4      Tracy Andrew     Charleston, SC         12/31/1969
5      Robert L Andrew  Boise, ID              05/25/1918

Relations
RelationID       Type
1                Mother
2                Father
3                Sister
4                Brother
5                Grandfather
6                Daughter

PersonRelations  (Person1 is Person2's ____)
Person1ID          Person2ID           RelationshipID
1                   2                     6
2                   1                     2
1                   3                     6
3                   1                     1
1                   4                     3

This is the only way I could think of to accomplish what you are trying to do. It may be that you just store the mother and father and do some kind of recursive search with VBA to build the generations. I don't think you can achieve what you want with a pure SQL solution (however, I have said that in the past and PHV always proves me wrong). But you really don't know how far back you need to go.

 

[soldierB]

Thanks Lespaul,

Ok so I know I can pull the data for the subject person and their parents in a query and the data is fine.

So your suggestion is to use that query and write some code to loop through the records matching ID's and grabbing the required fields??

Sounds Hard!!

Can you suggest how to go about this or another forum to ask on?

I can code a little in VB6/VBS, generally I use D-Weaver to make and control my recordsets to databases etc.

Ive never tried VBA, so am unsure as to how to start something using it and what software is needed?

As I say Im not looking for someone to do this, but any assistance and examples would be greatfully recieved,there is no time limit on this so I have all the time in the world but it is something I would like to accomplish,

I started on the database side as I just read in a book I have that its better to have your querys set in the database itself instead of hard coding it all?

I think I need a little assistance now in rethinking my problem and breaking it into managable chunks...

Thanks again Guys
Sb