5 Replies Latest reply on May 23, 2016 9:12 AM by erolst

# I'm an absolute newbie at this and am developing a database for a food bank. I have fields for each head of family plus their children and their ages. How do I determine how many children of each age range we help?

I'm an absolute newbie at this and am developing a database for a food bank. I have fields for each head of family plus their children and their ages. How do I determine how many children of each age range for all families we help?

• ###### 1. Re: I'm an absolute newbie at this and am developing a database for a food bank. I have fields for each head of family plus their children and their ages. How do I determine how many children of each age range we help?

to start with, you should not store the age but the birthdate of the children, and calculate the age.

the children should be stored into a related table, of course.

In that table you define a summary field, countOf (age).

Then you create a layout with header, no body but a sub-summary part when sorted by age.

You put into that layout part the fields age and CountOf(age).

You sort the records by age and you have your report.

• ###### 2. Re: I'm an absolute newbie at this and am developing a database for a food bank. I have fields for each head of family plus their children and their ages. How do I determine how many children of each age range we help?

I think that the idea is to count not the ages themselves, but the count within ranges (0-6, 7-10, 11-14 …)

So follow siplus's advice, and once you have a calculated age, create another calculation field of type text, as

Case (

cAge /* your age calc field */ <= 6 ; "age 0-6" ;

cAge <= 10 ; "age 7-10"

; // etc; adapt conditions and results to your actual age ranges

)

Now set the sort field of your sub-summary part to that field, and sort by it.

• ###### 3. Re: I'm an absolute newbie at this and am developing a database for a food bank. I have fields for each head of family plus their children and their ages. How do I determine how many children of each age range we help?

Thanks for the info, and you are correct, I’m looking for the number of children within a range. I DID determine their ages through calculation as suggested and end up with a field for Child 1 age, child 2 age, child 3 age etc. for each family, but I’m uncertain how those fields fit in to the provided calculation formula:  Case ( test1 ; result1 {; test2 ; result2 ; ... ; defaultResult} )

Is the Child 1 age test 1? What then is the result? and similarly for the other children’s ages.

• ###### 4. Re: I'm an absolute newbie at this and am developing a database for a food bank. I have fields for each head of family plus their children and their ages. How do I determine how many children of each age range we help?

Well, it's gratifying to know I did SOMETHING correctly! I did develop the field as a calculation based on child's birthdate so that it would consistently recalculate. My concern is that I need a calculation to determine the number of all the children in different ranges.

• ###### 5. Re: I'm an absolute newbie at this and am developing a database for a food bank. I have fields for each head of family plus their children and their ages. How do I determine how many children of each age range we help?

mannaman wrote:

end up with a field for Child 1 age, child 2 age, child 3 age etc. for each family,

My suggestion was based on the assumption that you would follow siplus's suggestion: create a related table to record the individual children, incl. fields for their their birthdates and a calculated age field.

Having fields like child1birthdate, child1age, child1nameFirst, child1nameLast; child2birthdate, child2age, child2nameFirst, child2nameLast is not a sensible data model, because it is really complicated and requires constant adjusting to summarize similar fields.

In a related table, you simply have birthdate, cAge, nameFirst, nameLast (and of course you probably should record every family member in that table, not just the children). Now you can – very easily – summarize the same field across similar records, no matter how many (i.e. children).

All you have to do now is insert that one cAge field into the calculation to get one result for a child – every child in a set.

Note that this set could be all children of one family – but it could as well be all children of, say, “all families that live on 3rd St. in 2015”. The only thing you have to do is find the correct found set (children) – the summarization can remain unchanged.

Try to implement this change, then let's discuss this again.