Design Suggestion required

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I have a form where i need to enter the ages of up to 4 children and their gendr. I have set this up with a combo for each child:
Child 1 Age, Gender
Child 2 Age, gender
Child 3 Age, gender
Child 4 Age, Gender
The Age combo has Under 12mths, 1,2,3.....15
The Gender combo is of course M,F
I want to run a query where it will give me a count of how many children in the dbase are of a certain age such as under 5 nd be able to choose M,F or either but because they are in different fields (child 1,2,3,4) it is difficult. Can anyone give me an idea of how to acheive this or an idea on a better way to set it up to be able to run a query to get this info?
I basically just want a combo listing Under 12mths, under 5yrs, under 10yrs, and another for F,M, either and be able to get a count of how many.
 
If possible, normalize your table so that you have a separate table for
children with a field to link to the original table, as well as fields for
FirstName, LastName, AgeGroup, Gender. This would allow you to easily query
the table.

--
Duane Hookom
MS Access MVP


sam said:
Hi, I have a form where i need to enter the ages of up to 4 children and
Click to expand...
their gendr. I have set this up with a combo for each child:
Child 1 Age, Gender
Child 2 Age, gender
Child 3 Age, gender
Child 4 Age, Gender
The Age combo has Under 12mths, 1,2,3.....15
The Gender combo is of course M,F
I want to run a query where it will give me a count of how many children
Click to expand...
in the dbase are of a certain age such as under 5 nd be able to choose M,F
or either but because they are in different fields (child 1,2,3,4) it is
difficult. Can anyone give me an idea of how to acheive this or an idea on a
better way to set it up to be able to run a query to get this info?
I basically just want a combo listing Under 12mths, under 5yrs, under
Click to expand...
10yrs, and another for F,M, either and be able to get a count of how many.
 
Sam,

I agree with Duane, that normalizing the database is your best bet.
However, until then, try the following.

1. Create a normalizing query (qry_Normalize)

SELECT 1 as ChildNo, [Child1Age] as ChildAge, [Child1Gender] as ChildGender
FROM yourTable
WHERE NOT Child1Age IS NULL
UNION ALL
SELECT 2 as ChildNo, [Child2Age] as ChildAge, [Child2Gender] as ChildGender
FROM yourTable
WHERE NOT Child2Age IS NULL
UNION ALL
SELECT 3 as ChildNo, [Child3Age] as ChildAge, [Child3Gender] as ChildGender
FROM yourTable
WHERE NOT Child3Age IS NULL
UNION ALL
SELECT 4 as ChildNo, [Child4Age] as ChildAge, [Child4Gender] as ChildGender
FROM yourTable
WHERE NOT Child4Age IS NULL

2. Now use this query as the basis of your query to compute the number of
children under a particular age of a particular gender.

HTH
Dale

sam said:
Hi, I have a form where i need to enter the ages of up to 4 children and
Click to expand...
their gendr. I have set this up with a combo for each child:
Child 1 Age, Gender
Child 2 Age, gender
Child 3 Age, gender
Child 4 Age, Gender
The Age combo has Under 12mths, 1,2,3.....15
The Gender combo is of course M,F
I want to run a query where it will give me a count of how many children
Click to expand...
in the dbase are of a certain age such as under 5 nd be able to choose M,F
or either but because they are in different fields (child 1,2,3,4) it is
difficult. Can anyone give me an idea of how to acheive this or an idea on a
better way to set it up to be able to run a query to get this info?
I basically just want a combo listing Under 12mths, under 5yrs, under
Click to expand...
10yrs, and another for F,M, either and be able to get a count of how many.
 
I am confused as to why you put child 1,2,3 and 4 in
different fields. I would have put the child in the same
filed then added another field to designate 1,2,3 or 4.
that would be 2 fields in the table instead of 4 and
selection would be much eaiser. If me, I would consider
modifing the table like above.
 
Back
Top