423,688 Members | 1,894 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,688 IT Pros & Developers. It's quick & easy.

Access - Demographics

P: 2
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
Jul 25 '18 #1
Share this Question
Share on Google+
3 Replies


PhilOfWalton
Expert 100+
P: 1,373
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
Jul 25 '18 #2

P: 2
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.
Jul 25 '18 #3

zmbd
Expert Mod 5K+
P: 5,285
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
Expand|Select|Wrap|Line Numbers
  1. 'http://allenbrowne.com/func-08.html
  2. 'Allen Browne. Age() Function
  3. '
  4. Function Age(varDOB As Variant, Optional varAsOf As Variant) As Variant
  5.     'Purpose:   Return the Age in years.
  6.     'Arguments: varDOB = Date Of Birth
  7.     '           varAsOf = the date to calculate the age at, or today if missing.
  8.     'Return:    Whole number of years.
  9.     Dim dtDOB As Date
  10.     Dim dtAsOf As Date
  11.     Dim dtBDay As Date  'Birthday in the year of calculation.
  12.  
  13.     Age = Null          'Initialize to Null
  14.  
  15.     'Validate parameters
  16.     If IsDate(varDOB) Then
  17.         dtDOB = varDOB
  18.  
  19.         If Not IsDate(varAsOf) Then  'Date to calculate age from.
  20.             dtAsOf = Date
  21.         Else
  22.             dtAsOf = varAsOf
  23.         End If
  24.  
  25.         If dtAsOf >= dtDOB Then      'Calculate only if it's after person was born.
  26.             dtBDay = DateSerial(Year(dtAsOf), Month(dtDOB), Day(dtDOB))
  27.             Age = DateDiff("yyyy", dtDOB, dtAsOf) + (dtBDay > dtAsOf)
  28.         End If
  29.     End If
  30. 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?
Jul 25 '18 #4

Post your reply

Sign in to post your reply or Sign up for a free account.