Birthday Querie (For multiple Birthdates in each record

[naigy via AccessMonster.com]

Hi I am in the process of setting up a contacts database where for some
contacts i am wanting to store Spouse name and birthdate and where applicable
the same for the children all in one record. The fields are as follows (The
assumption is made that everyone shares the lastname field). At current I do
not have contacts where I want to store childrens details for more then 2
children.

Primary Firstname
Lastname
Birthdate
SpouseFirstname
SpouseBirthdate
1stChildFirstname
1stChildBirthdate
2ndChildFirstname
2ndChildBirthdate


Now what I want to be able to do is print out a list with everybodies names
and their date of birth (sorted by month then day which I will then format
into age also).

The only way I can see I can do this is to create 4 seperate queries and
output 4 seperate reports (or subreports) but I want to be able to merge it
all into one list so it is all ordered rather than broken into Primary,
spouse, 1st child, 2nd child. Is this doable and if so can someone point me
in the right direction. Thanks.

 

[David F Cox]

at current? and if the next contact has 10 children?
Can contacts be married to one another?
Not be married and have a partner with a diiferent name, and share
parenting of children with different surnames?
Time and the world is going to overwhem your design, and you might be better
advised to design it right now.
You are dealing with relations, and I would advise you to change your design
to be relational.

...The only way I can see I can do this is to create 4 seperate queries
and
output 4 seperate reports (or subreports)...

You are straying from the highway, with only 2 out of N children, and are
already getting tangled in the undergrowth.

... Now what I want to be able to do is print out a list with everybodies
names
and their date of birth (sorted by month then day which I will then format
into age also)...

On the highway that is one query.

 

[naigy via AccessMonster.com]

Okay so basically you are telling me this can't be done is that correct.

I believe going down the relational database line is going to make it more
complex then it needs to be for my purpose (I have done it in the past for
other projects) but I can see your point about thinking of the future. I will
attempt to map this out and go down this route with hopefully more success.

Thanks for your help.

 

[David F Cox]

No, if I thought it could not be done I would have said so.

You can get several queries to output their data in a common format, and
then create a UNION query in SQL view. It is not that difficult. Something
like:-

(SELECT Name1, Name2, DOB, Age FROM .......)
UNION
(SELECT Name1, Name2, DOB, Age FROM .......)
UNION
(SELECT Name1, Name2, DOB, Age FROM .......)


and then use that query as an input to your query to sort the data.

In a properly designed database I believe that it would only take 1 query to
do the whole job.

Good Luck.

 

[John Spencer]

To expand just a little on David Cox's response. The union query should look like

SELECT LastName
, [Primary FirstName] as FirstName
, Birthdate
, "Primary" as FamilyPosition
FROM [YourTable]
UNION ALL
SELECT LastName
, [SpouseFirstName]
, SpouseBirthdate
, "Spouse"
FROM [YourTable]
UNION ALL
SELECT LastName
, [1stChildFirstName]
, [1stChildBirthdate]
, "Child1"
FROM [YourTable]
UNION ALL
SELECT LastName
, [2ndChildFirstName]
, [2ndChildBirthdate]
, "Child2"
FROM [YourTable]

Save that query as qNormalFamily, Then you can use this as the basis of other
queries instead of a table. One shortcoming with using a union query is that
nothing can be updated, deleted, etc from a Union query. It is read only.

Better design would be to have a familyTable where you would store information
relevant to the entire family (home phone, home address, record identifier) and
then another table for family members where you store information on each member
of the family - such as name, dob, gender, type of family member, etc.

 

[naigy via AccessMonster.com]

Thanks for your help. I have decided to go with the indivual record approach
and have a common link number which combines common addresses, phone number
etc. Thanks for your assistance.

To expand just a little on David Cox's response. The union query should look like

SELECT LastName
, [Primary FirstName] as FirstName
, Birthdate
, "Primary" as FamilyPosition
FROM [YourTable]
UNION ALL
SELECT LastName
, [SpouseFirstName]
, SpouseBirthdate
, "Spouse"
FROM [YourTable]
UNION ALL
SELECT LastName
, [1stChildFirstName]
, [1stChildBirthdate]
, "Child1"
FROM [YourTable]
UNION ALL
SELECT LastName
, [2ndChildFirstName]
, [2ndChildBirthdate]
, "Child2"
FROM [YourTable]

Save that query as qNormalFamily, Then you can use this as the basis of other
queries instead of a table. One shortcoming with using a union query is that
nothing can be updated, deleted, etc from a Union query. It is read only.

Better design would be to have a familyTable where you would store information
relevant to the entire family (home phone, home address, record identifier) and
then another table for family members where you store information on each member
of the family - such as name, dob, gender, type of family member, etc.

No, if I thought it could not be done I would have said so.

[quoted text clipped - 29 lines]