combine multiple fields into a query

[nobby]

Hi. I don't know if this is possible
I am making a family tree database and would like the ability to lookup date of birth, date of marriage, christening date ,and date of death, from one combo box ( like a timeline starting from a certain year) so I can do another combo to show and end date ( like a timeline ending with a certain year)

I envisage it telling me all the events between 2 dates

I have a demo database if needed

Thanks

 

[MajP]

I assuming you do not have the dates normalized, but you have a field for each date.

Something like

PersonName DateOfBirth DateOfMarriage DateOfDeath

if that is the case you can do a union query to get all dates in one fied and the name of the event

John Smith  Birth Date        1/1/1900
John Smith  Marriage Date  1/1/1920
John Smith  Date of Death  1/1/1970

Once you do that you can query for all events between two dates.

 

[nobby]

Hi MajP

Thanks so much for the quick reply
The problem I have is it is all on one table
ID,First,Middle,Last,Year of Birth,ChristenYear, Marriedyear,Year of Death

Is it still possible ?

 

[Dreamweaver]

like a timeline starting from a certain year

Would you want something like
Mother Name
..Child Name
....Childs first child
....Childs second Child
And so on


If so then I would not use a combo box as it's a complex dataset depending on your table structure I haven't done a lot with this sort of data set but when I did I created a family ID If I remember corrctly

 

[isladogs]

You might be interested in this family tree example app http://www.accessforums.net/showthread.php?t=75300&p=419601#post419601

 

[jdraw]

There is a Family Tree template here also.
Good luck with your project.

 

[MajP]

Thanks so much for the quick reply
The problem I have is it is all on one table
ID,First,Middle,Last,Year of Birth,ChristenYear, Marriedyear,Year of Death

Is it still possible ?

Yes. Here is some date from that template

ID	Full Name	Given Name	Surname	Sex	Birth Date	Birth Location	Death Date	Death Location
7581	Michael Alexander	Michael	Alexander	0	1/1/1900	Charlotte, NC	12/31/1959	Roanoke, NC
7582	Michelle Alexander	Michelle	Alexander	1	1/1/1902	Richmond, VA	7/1/1963	Charlotte, NC
7583	Sean P. Alexander	Sean	Alexander	0	1/1/1921	Charlotte, NC	12/31/1989	Charlotte, NC
7584	Jay Adams	Jay	Adams	0	1/1/1951	Matthews, NC		
7585	Julie Bankert	Julie	Bankert	1	1/1/1955	Charleston, SC		
7599	Terry Adams	Terry	Adams	1				
7600	Ben Smith	Ben	Smith	0

Here is the sql for the Union query

SELECT 
 [Full Name],
 "Birth Date" as EventName, 
 [Birth Date] AS EventDate, 
 Individuals.[Birth Location] AS EventLocation
FROM 
 Individuals
WHERE
 Not [Birth Date] is Null
UNION SELECT 
 Individuals.[Full Name],
 "Death Date" as EventName, 
 Individuals.[Death Date] AS EventDate, 
 Individuals.[Death Location] AS EventLocation
FROM 
 Individuals
WHERE 
 Not [Death Date] is Null
ORDER BY 1,2

The results look like

Full Name	EventName	EventDate	EventLocation
Jay Adams	Birth Date	1/1/1951	Matthews, NC
Julie Bankert	Birth Date	1/1/1955	Charleston, SC
Michael Alexander	Birth Date	1/1/1900	Charlotte, NC
Michael Alexander	Death Date	12/31/1959	Roanoke, NC
Michelle Alexander	Birth Date	1/1/1902	Richmond, VA
Michelle Alexander	Death Date	7/1/1963	Charlotte, NC
Sean P. Alexander	Birth Date	1/1/1921	Charlotte, NC
Sean P. Alexander	Death Date	12/31/1989	Charlotte, NC

For more fields you just keep on unioning.

 

[MajP]

Would you want something like
Mother Name
..Child Name
....Childs first child
....Childs second Child
And so on
If so then I would not use a combo box as it's a complex dataset depending on your table structure I haven't done a lot with this sort of data set but when I did I created a family ID If I remember correctly

If that is what you are looking for, you may be interested in this thread
https://www.access-programmers.co.uk/forums/showthread.php?t=302630&page=6
In there I do a lot of different recursive calls and make tree views and other graphics to show families.

 

[nobby]

Hi All and thanks for the advice
I chickened out eventually as the solution using join table was great but I couldn't adapt it to my DB however, it did inspire me to do an append queries for each event IE Birth marriage etc and append them to a timeline tables
(Yes, I can hear you all coughing in embarrassing way I've handled it but I only need do it once )
Many thanks again for all who supported me