Access - Demographics

[penza80]

Hi guys

I'm designing a legal client database for internal use. Due to our funding agreement, we need to provide the government with demographics pertaining to the number of children in our clients care and the age breakdown (0-2, 3-8, 9-14, 15 and over). As this is a little out of my Access skill set, I am wondering if there is an easy way to code this into our table/form as our clients tend to have 5+ children.

Any ideas would be greatly appreciated.

Cheers
Penza

[PhilOfWalton]

Welcome to Bytes

You need to be a little clearer in your thoughts. Presumably each child is entered into your Database and their Date of Birth should be in their record.

Assuming that is correct, are we talking about their age now, or at some fixed date like January 1st or the start of the school year?

You also need to be much clearer on your age ranges. Where dose a child aged eight and a half fit. I suspect the ages should be something like 0 but less than 3, 3 but less than 9, 9 but less than 15, and 15 and over.

Please clarify

Phil

[penza80]

Thanks for your reply Phil.

The parent of the children will be entered into the database. There will be no other data recorded regarding the children other than their age demographic. As I said before, we service clients with large families and I would like the form to be able to accommodate this without being too bulky.

Government has requested the following in our funding agreement:
"Client information data for all legal services must include:
Proportion of clients with children in their care; of clients with children in their care, proportion of children by age breakdown (including pregnancy, 0-2, 3-8, 9-14, 15 and over)."

Of course, none of this data can be entered into the government sanctioned reporting system so I need to be able to record and report it.

Any ideas.

[zmbd]

There will be no other data recorded regarding the children other than their age demographic

+ Is there any reason you cannot record the birthdate? It would make your job exceptionally easier from a maintenance standpoint if you have the birthdate as you can calculate the age instead of constantly questioning the parents about the children's ages.
I have been using some VBA from Allen Brown for ages:
Allen Browne.Age() Function

'http://allenbrowne.com/func-08.html
'Allen Browne. Age() Function
'
Function Age(varDOB As Variant, Optional varAsOf As Variant) As Variant
    'Purpose:   Return the Age in years.
    'Arguments: varDOB = Date Of Birth
    '           varAsOf = the date to calculate the age at, or today if missing.
    'Return:    Whole number of years.
    Dim dtDOB As Date
    Dim dtAsOf As Date
    Dim dtBDay As Date  'Birthday in the year of calculation.
 
    Age = Null          'Initialize to Null
 
    'Validate parameters
    If IsDate(varDOB) Then
        dtDOB = varDOB
 
        If Not IsDate(varAsOf) Then  'Date to calculate age from.
            dtAsOf = Date
        Else
            dtAsOf = varAsOf
        End If
 
        If dtAsOf >= dtDOB Then      'Calculate only if it's after person was born.
            dtBDay = DateSerial(Year(dtAsOf), Month(dtDOB), Day(dtDOB))
            Age = DateDiff("yyyy", dtDOB, dtAsOf) + (dtBDay > dtAsOf)
        End If
    End If
End Function

Typically I use this as a calculated field in a query to generate the ages.

+ Are you working with an established database or are you developing this from the ground-up?

+ The partition function would be ideal for this application if the age spans were a bit more even.
ADezii has a very nice insight article covering this:
home > topics > microsoft access / vba > insights > the partition() function
There's also a link to a frequency distribution discussion at FMSInc.com in this article that you might find useful if the Partition() doesn't fill the bill.

+

Proportion of clients with children in their care; of clients with children in their care, proportion of children by age breakdown (including pregnancy, 0-2, 3-8, 9-14, 15 and over)."

By way of example, I take this to mean:
- that if you have 100 parents and only 20 of them have children then you would report that 20% of the families have children.
- That of the these families there are a total of 100 children (taking your 5/family number) and of these
-1-0-2 would be 15% (where -1 = unborn)
3-8 would be 15%
8-15 would be 15%
15-18 would be 55% - thus excluding persons over 18

Is the above correct?