displaying multiple dates in a table

[jonax]

any help/advice is much appreciated.... let me explain...
i have this table w/c have multiple date/time fields... corresponding to
birthdays of people. a single record of a person contains his birthday, his
spouse's name & birthday, and also his sons/daughters names & birthdays...
now i have already done a query in w/c i can get all persons birthday to
display per month (month parameter)...
i have done a query just like the one i made above, and it will display
the spouses & sons/daughters birthday corresponding to the month i typed in.
but the query also displayed all the fields including the sons/daughter
birhtday whose not born in the month i typed!
what i need help is this; is there a way to make this query wherein i type
a month ("mmm"), then the query will display ONLY the spouse's who have
birthdays on that month? w/o displaying the person's sons/daughters birthdays
who was not born on the month i typed (& vise versa in case only the person's
son was born in the month)?
many many thanx for your help in advance!!! ^_^

 

[Allen Browne]

No. What you have designed is effectively a spreadsheet, not a relational
database.

What you need is a table where each person has their own record. The table
will have fields such as:
ClientID AutoNumber primary key
Surname Text
FirstName Text
DOB Date birth date

You then create another table with a record for each household, e.g.:
HouseholdID AutoNumber primary key
HouseholdName Text e.g. "Smith Family" or whatever you want
and then a 3rd table that identifes the people in the household. Fields:
HouseholdID relates to HouseholdID in household table.
ClientID relates to ClientID in ClientTable
RoleID whether person is a parent, child, whatever in this
household.

Since each person has their own record in the Client table, you can easily
generate a report for each person who has a birthday in a particular month.
In the Field row in query design, type:
TheMonth: Month([DOB])
and then in the Criteria row, enter a number from 1 to 12.

These one-to-many relationships are the most crucial part of designing a
database. A person can belong to more than one household (e.g. a child who
spends half their time with Dad, and half with Mum), so there is a
many-to-many relationship between people and households. The 3rd table above
resolves that into a pair of one-to-many relationships. Here's another
example of that kind of thing:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html

If you understand how all that works and want something more comprehensive,
the sample database in this link to take you to the next level:
People in households and companies - Modelling human relationships
at:
http://allenbrowne.com/AppHuman.html

 

[John W. Vinson]

any help/advice is much appreciated.... let me explain...
i have this table w/c have multiple date/time fields... corresponding to
birthdays of people. a single record of a person contains his birthday, his
spouse's name & birthday, and also his sons/daughters names & birthdays...

Then your table is designed incorrectly. This might be a good spreadsheet, but
it's not a properly designed relational table.


Much better would be a table of people, with fields like PersonID, firstname,
lastname, birthdate, etc.; and a table of Relationships, with fields
PersonID1, PersonID2, Relationship. If Jane is Bill's daughter, you would have
a record in this table with Jane's ID, Bill's ID, and "Daughter" in the
relationship.

now i have already done a query in w/c i can get all persons birthday to
display per month (month parameter)...

Very easy with a properly normalized table. A reall mess with your
spreadsheet.

i have done a query just like the one i made above, and it will display
the spouses & sons/daughters birthday corresponding to the month i typed in.
but the query also displayed all the fields including the sons/daughter
birhtday whose not born in the month i typed!

Well, of course. In a relational database query either a record (an entire
record) is retrieved, or it isn't. You have a whole lot of baggage in the
record; you're retrieving the record based on one field.

what i need help is this; is there a way to make this query wherein i type
a month ("mmm"), then the query will display ONLY the spouse's who have
birthdays on that month? w/o displaying the person's sons/daughters birthdays
who was not born on the month i typed (& vise versa in case only the person's
son was born in the month)?
many many thanx for your help in advance!!! ^_^

Again... trivially easy with the normalized design. All but impossible with
yours.

John W. Vinson [MVP]

 

[John Spencer]

I agree with Allen Browne and John Vinson. The best thing you could do
would be to redesign your table structure. Allen's proposed structure would
let you handle households with any number of sons and daughters, plus if
needed grandparents and other significant individuals in the household.

If you cannot do that then you would need to build a fairly complex query
or better build a UNION query as the source for your birthday query and use
that as the source of your birthday query.

Sample Union query -
(UNION queries can only be built in the SQL view - you cannot use Design
View to build or display them.)

SELECT NameOf Primary as Person
, "Primary" as PersonType
, DOBOfPrimary as BirthDate
, HouseHoldAddress
FROM YourTable
UNION ALL
SELECT NameOf Spouse as Person
, "Spouse"
, DOBOfSPouse as BirthDate
, HouseHoldAddress
FROM YourTable
UNION ALL
SELECT NameOf Son as Person
, "Son"
, DOBOfSon as BirthDate
, HouseHoldAddress
FROM YourTable
UNION ALL
SELECT NameOf Daughter as Person
, "Daughter"
, DOBDaughter as BirthDate
, HouseHoldAddress
FROM YourTable

With that query saved, you can then use it to get persons with DOB in a
specific month.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[jonax]

I agree with Allen Browne and John Vinson. The best thing you could do
would be to redesign your table structure. Allen's proposed structure would
let you handle households with any number of sons and daughters, plus if
needed grandparents and other significant individuals in the household.

If you cannot do that then you would need to build a fairly complex query
or better build a UNION query as the source for your birthday query and use
that as the source of your birthday query.

Sample Union query -
(UNION queries can only be built in the SQL view - you cannot use Design
View to build or display them.)

SELECT NameOf Primary as Person
, "Primary" as PersonType
, DOBOfPrimary as BirthDate
, HouseHoldAddress
FROM YourTable
UNION ALL
SELECT NameOf Spouse as Person
, "Spouse"
, DOBOfSPouse as BirthDate
, HouseHoldAddress
FROM YourTable
UNION ALL
SELECT NameOf Son as Person
, "Son"
, DOBOfSon as BirthDate
, HouseHoldAddress
FROM YourTable
UNION ALL
SELECT NameOf Daughter as Person
, "Daughter"
, DOBDaughter as BirthDate
, HouseHoldAddress
FROM YourTable

With that query saved, you can then use it to get persons with DOB in a
specific month.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

wow! thanx, all of you for your answers... it was enlightening as it was
helpful.... please understand though, i'm new to making databases and using
access... i just read all the things i need from the help file & reading
boards like this one... all just self-help. ^_^
i'll try to copy the sample union query u made... thanx again, guys! ^_^