Solved Age Deomgraphics (1 Viewer)

[JFTE]

Hi, I am trying to perform a query to find out the age demographics of the clients and their dependents in my database.
I have a clients table (ClientsT) with fields DOBC (date of birth of client), DOBD1 (date of birth Dependent 1) and DOBD2. Some clients do not have any dependents.

I can create a query and find the ages of the clients and dependents. Age for Clients, AgeD1 for Dependent 1, AgeD2 for Dependent 2. Then I have created another and imported the Age, AgeD1 and AgeD2 fields. I need to find people with an age less than 16, ages between 16 and 25, Ages over 55.

I type into a query field Under16: IIF([Age]<16 OR [AgeD1]<16 OR [AgeD2]<16,1,0)
Between16and25: IIF([Age]>=16 AND [Age]<25 OR [AgeD1]>=16 AND [AgeD1<25 OR [AgeD2]>=16 AND [AgeD2]<25,1,0)
Over55: IIF([Age]>=55 OR [AgeD1]>=55 OR [AgeD2]>=55,1,0)
I am not getting the answers I expect. I only have three records in my test so I know the formulae aren’t working.

what am I doing wrong?

 

[Gasman]

Use () to logically separate the ANDs?ORs

However really looks like your DB is not normalised. That would be simple if it was.

From your intro thread.

Just as a hint, be sure that you are comfortable with normalization for your tables. That will help prevent some of those "problems as they arise."

 

[mike60smart]

Hi, I am trying to perform a query to find out the age demographics of the clients and their dependents in my database.
I have a clients table (ClientsT) with fields DOBC (date of birth of client), DOBD1 (date of birth Dependent 1) and DOBD2. Some clients do not have any dependents.

I can create a query and find the ages of the clients and dependents. Age for Clients, AgeD1 for Dependent 1, AgeD2 for Dependent 2. Then I have created another and imported the Age, AgeD1 and AgeD2 fields. I need to find people with an age less than 16, ages between 16 and 25, Ages over 55.

I type into a query field Under16: IIF([Age]<16 OR [AgeD1]<16 OR [AgeD2]<16,1,0)
Between16and25: IIF([Age]>=16 AND [Age]<25 OR [AgeD1]>=16 AND [AgeD1<25 OR [AgeD2]>=16 AND [AgeD2]<25,1,0)
Over55: IIF([Age]>=55 OR [AgeD1]>=55 OR [AgeD2]>=55,1,0)
I am not getting the answers I expect. I only have three records in my test so I know the formulae aren’t working.

what am I doing wrong?

I agree with Gasman, best to normalise to 1 Client has Dependents.

 

[JFTE]

Use () to logically separate the ANDs?ORs

However really looks like your DB is not normalised. That would be simple if it was.

From your intro thread.

Thanks for the reply. Can you explain what you mean by normalisating the database?

 

[mike60smart]

Thanks for the reply. Can you explain what you mean by normalisating the database?

You need a table for Clients and a related table for Dependents.

 

[plog]

Something like:

DependentsT
dep_ID, autonumber, primary key
ID_Client, number, foreign key back to ClientsT
dep_DOB, date/time, date of dependent birth
other dependent fields if any

1 dependents = one record in DependentsT. That way you can independently determine how many dependents fit into each range. Right now, if any of the 3 people meet the criteria they go into that bucket just once. If both dependents are under 16 then they only get counted once because that's how your query is set up. With a properly normalized dependents table you can evalualte them each individually and then count them.

Additionally, if a dependent doesn't exist for a client you don't have empty fields that you have to account for in your database. Additionally, additionally, if a client has 5 dependents you don't need to change your table structure, you just put all five in DependentsT and it can handle it.

 

[GPGeorge]

Thanks for the reply. Can you explain what you mean by normalisating the database?

"Normalization" is the formal process of designing the tables required in a relational database application.

This video, and some others in the same playlist, would be a good starting point.

 

[The_Doc_Man]

@JFTE

When I was working on my genealogy database (a work still in progress), I found a related issue. A person can be a parent, a child, and a sibling all at the same time.

My normalized solution was to create a FAMILY table (where the family name came from one of the parents) but that table did not represent a person. Instead, I had a Family Number that was the primary key of that table.

Then I had PERSONS with a Person Number. They had birth dates and other statistics. Your statistics would be computed on persons regardless of how they fit in to anything, because... people are people.

The key was the JUNCTION table (which would be a good topic for you to look up), in which an entry was a Family Number, a Person Number, and a relationship code that could be Husband, Wife, or Child. You would have as many junction entries as needed AND a person could appear in the junction table as many times as they had relationships. So my Dad was in the table twice - once as a husband (in his own family) and once as a child (in HIS father's family.) Bachelors and Bachelorettes would still be in the tables twice - once as a child and once as either a husband or wife (without a partner or child listed.)

The trick is how you query (and JOIN) the tables. If you want to know who is in a specific family, query the junction table's Family Number field for a particular value. If you want to know all of the kids in a family, query the junction table's Family Number field AND specify "CHILD" for the relationship field.

 

[JFTE]

You need a table for Clients and a related table for Dependents.

I started with that but if a client didn‘t have a dependent then the results weren’t correct.

 

[JFTE]

@JFTE

When I was working on my genealogy database (a work still in progress), I found a related issue. A person can be a parent, a child, and a sibling all at the same time.

My normalized solution was to create a FAMILY table (where the family name came from one of the parents) but that table did not represent a person. Instead, I had a Family Number that was the primary key of that table.

Then I had PERSONS with a Person Number. They had birth dates and other statistics. Your statistics would be computed on persons regardless of how they fit in to anything, because... people are people.

The key was the JUNCTION table (which would be a good topic for you to look up), in which an entry was a Family Number, a Person Number, and a relationship code that could be Husband, Wife, or Child. You would have as many junction entries as needed AND a person could appear in the junction table as many times as they had relationships. So my Dad was in the table twice - once as a husband (in his own family) and once as a child (in HIS father's family.) Bachelors and Bachelorettes would still be in the tables twice - once as a child and once as either a husband or wife (without a partner or child listed.)

The trick is how you query (and JOIN) the tables. If you want to know who is in a specific family, query the junction table's Family Number field for a particular value. If you want to know all of the kids in a family, query the junction table's Family Number field AND specify "CHILD" for the relationship field.

Thanks for your reply. I will investigate it.

 

[mike60smart]

I started with that but if a client didn‘t have a dependent then the results weren’t correct.

Hi
Are you able to upload a copy of the database with no confidential data?

 

[JFTE]

"Normalization" is the formal process of designing the tables required in a relational database application.

This video, and some others in the same playlist, would be a good starting point.

Thank you. I will watch the video.

 

[Pat Hartman]

I started with that but if a client didn‘t have a dependent then the results weren’t correct.

You need to use a left join when the data in the right side table is optional as it would be with dependents.

 

[JFTE]

Hi
Are you able to upload a copy of the database with no confidential data?

This is what I started with. I couldn't get the age demographics to work, so I created another table with the Dependents in with the clients.

 

[GaP42]

As a new member welcome to the AWF!
The relationships diagram provided raises a number of questions, as flagged by others and comes back to modelling the data correctly. This ultimately depends on your business needs and is limited by our lack of knowledge of your specific business focus.
From what has been said/presented in this thread, and focussing on the data only:

• You have clients who may have one of more dependents. A client joins (enrols?) in your service.
• A Client (or Dependent) may have one or more Illness or Disability for which they may receive a Benefit.
• A Client (and/or Dependent) may visit your service multiple times. (not sure what NoAdults/ChildFed represents)
• A Client (and / or Dependent) may also be provided multiple "Extras" - of various types.

From your relationships diagram there are clients who may have an Illness or Disability (but may not if they are the parent/carer of a dependent who does have an illness/disability of interest to the service?)
A dependent is entered as a Client - and is (always) assigned an illness/Disability
However there is no association apparent for a dependent to a parent!?

A self-referential join of client to client (introducing a ParentID can address this. (Do you need to distinguish a Parent of a child from a Carer of a Client?). The DOBDep will then not be needed in the Dep2T table. Your query is limited to 2 dependents, however a parent may have more.
Can you have both natural parents as client records each with the same dependent records? Can a carer change over time and do you need to retain the Carer record? (this may then need a Junction table to resolve)
The repeated extras in the Extras table - are these limited to 4? Do you need to know when they were provided? (ie may they be associated to a visit
NoofDep - is a calculated value and should not be stored - unless this is the number of children in the family including those who are not in the register?
Disability/Illness and Benefit - are these all free text or do you have a need to control the entries so that the data is consistent - "coded" - to support querying of records. Can/do Benefits potentially change independent of Disability/illness over time. This might then be a separate table, and linked to a visit when it is assigned.

 

[mike60smart]

I think we need to see your database with some same data.

 

[ebs17]

I need to find people with an age ...

The simple solution would be to write clients and dependents in the SAME table. They are all people with largely the same attributes.
Normalization says: The same information belongs in the same fields (the same table).
The only necessary addition would be an additional field DependentOf, where the client's primary key is entered as a foreign key for dependents.
In the case of multi-level hierarchies, you would then use a junction table to get the two keys together.

Now you have the date of birth in exactly one field and can easily filter and evaluate based on this date of birth or a resulting age.
Since the DOB field is a table field and could/should be indexed, using it for filtering and evaluation would usually be more performant than using the calculated age.

 

[GaP42]

JFTE - I assume you are in the early stages of working this database through and would be amenable to changing the structure to make it more flexible to change - The relationships diagram seems to have a few holes, and while we (I) do not know your the scope and functions you want to include, or the sources of the data (eg Do you get Client data from a master database and can rely on that for more detailed information) a possible more robust data model would be include some additional features - resembling.

There will be a number of discussion points or things for your to consider further: eg the DepEpT record is your relationship diagram has both a Disability and an Illness able to be recorded for a DepEP -- for a Client multiple disabilities may be recorded, and multiple illnesses. However are all disabilities recorded with an illness? While the client may have a disability, is the illness recorded depend upon that disability or are they separate concepts and potentially able to be recorded independently for a client - which would then suggest 2 tables - one for ClientDisabilty and another for ClientIllness. I have "coded" them to give some standardisation to the assignment of a Disability or Illness. These may of course be coded to an ICD scheme (9,10,11) or SNOMED for eg or one you manage.
The blue "notes" are reminders that for the most part that supporting reference tables are needed. Relationships would need to be properly defined for these. Notes fields have been added.
Depending on your needs you may need some form a ContactDiary history table about communications with the client - but this may be handled by a master system we have no view of. "ProgressNotes" table is speculation on my part.

 

[JFTE]

This is what I started with. I couldn't get the age demographics to work, so I created another table with the Dependents in with the clients.

JFTE - I assume you are in the early stages of working this database through and would be amenable to changing the structure to make it more flexible to change - The relationships diagram seems to have a few holes, and while we (I) do not know your the scope and functions you want to include, or the sources of the data (eg Do you get Client data from a master database and can rely on that for more detailed information) a possible more robust data model would be include some additional features - resembling.

View attachment 112759

There will be a number of discussion points or things for your to consider further: eg the DepEpT record is your relationship diagram has both a Disability and an Illness able to be recorded for a DepEP -- for a Client multiple disabilities may be recorded, and multiple illnesses. However are all disabilities recorded with an illness? While the client may have a disability, is the illness recorded depend upon that disability or are they separate concepts and potentially able to be recorded independently for a client - which would then suggest 2 tables - one for ClientDisabilty and another for ClientIllness. I have "coded" them to give some standardisation to the assignment of a Disability or Illness. These may of course be coded to an ICD scheme (9,10,11) or SNOMED for eg or one you manage.
The blue "notes" are reminders that for the most part that supporting reference tables are needed. Relationships would need to be properly defined for these. Notes fields have been added.
Depending on your needs you may need some form a ContactDiary history table about communications with the client - but this may be handled by a master system we have no view of. "ProgressNotes" table is speculation on my part.

Thank you for your reply. I will have a very good look at your schematic. First thoughts are that this is far more complicated than I would need. I am helping out in a very small charity which at the moment has only 140 clients, most of whom we will only see once or twice but they are kept on the books for 6 months after their last visit. At that stage the records are moved to an inactive file. (all paper at the moment).

 

[JFTE]

The simple solution would be to write clients and dependents in the SAME table. They are all people with largely the same attributes.
Normalization says: The same information belongs in the same fields (the same table).
The only necessary addition would be an additional field DependentOf, where the client's primary key is entered as a foreign key for dependents.
In the case of multi-level hierarchies, you would then use a junction table to get the two keys together.

Now you have the date of birth in exactly one field and can easily filter and evaluate based on this date of birth or a resulting age.
Since the DOB field is a table field and could/should be indexed, using it for filtering and evaluation would usually be more performant than using the calculated age.

Thanks for your reply. I have put the Clients and dependents in one table and was trying to calculate age demographics from that one table as my original post stated. I couldn't get the formulae to give me the answers that I wanted.