Solved Age Deomgraphics

[JFTE]

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

Thanks for your reply. I tried to post the database last night but the site told me it was too large to post.

 

[JFTE]

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.

Thanks for your in depth reply and welcome. I curtailed some of the fields in the Client table such as illness and disability and benefits but forgot to do the same with the dependents table. We use the term client for the person who turns up at the charity. They then inform us about their dependents. This information is kept on the clients form.
In the Extras table, ExtraD is the date on which 'extras' were given out.
I take you point about the number of dependents being a calculated field.
I obviously need to rethink what I want to do with this database.

 

[ebs17]

I couldn't get the formulae to give me the answers that I wanted.

Formulas...that's Excel thinking.

You have a table tblGroups with the fields GroupName, AgeFrom and AgeTo. Simplified to represent the idea:

SELECT
   C.ClientID,
   DateDiff("yyyy", C.DOB, Date()) + (Format(Date(), "mmdd") < Format(C.DOB, "mmdd")) AS Age,
   G.GroupName
FROM
   tblClients AS C,
   tblGroups AS G
WHERE
   DateDiff("yyyy", C.DOB, Date()) + (Format(Date(), "mmdd") < Format(C.DOB, "mmdd")) BETWEEN G.AgeFrom
      AND
   G.AgeTo
      AND
   G.GroupName IN ("A", "F", "G")

You can see: An if cascade can be broken down very clearly using a table with the variants and a link to them.
This is also very easy to maintain and change using table data.

 

[GaP42]

Thanks for your in depth reply and welcome. I curtailed some of the fields in the Client table such as illness and disability and benefits but forgot to do the same with the dependents table. We use the term client for the person who turns up at the charity. They then inform us about their dependents. This information is kept on the clients form.
In the Extras table, ExtraD is the date on which 'extras' were given out.
I take you point about the number of dependents being a calculated field.
I obviously need to rethink what I want to do with this database.

Happy to review your progress and provide some reflections / views. The model I provided needs to be carefully reviewed - I have made some adjustments including the one re ExtraD. The form for Client allowing Dependents to be entered does not dictate that Dependents need to be managed through a separate table (I would still advocate for a common Client table with the ability to identify who is the "parent" or "carer" of the client.) Queries can handle the views needed to support subforms for any number of dependents.
Does "Fed" in VisitT refer to meals provided. Again, if so, and visit is recorded for a client and their dependents, it would be simple to record a meal was provided to the client and each dependent on a visit, and the count of meals can be calculated - to clients and dependents (Dependents being those that do have a DependentOfID)

 

[JFTE]

Formulas...that's Excel thinking.

You have a table tblGroups with the fields GroupName, AgeFrom and AgeTo. Simplified to represent the idea:

SELECT
   C.ClientID,
   G.Group
FROM
   tblClients AS C,
   tblGroups AS G
WHERE
   C.Age BETWEEN G.AgeFrom
      AND
   G.AgeTo

You can see: An if cascade can be broken down very clearly using a table with the variants and a link to them.
This is also very easy to maintain and change using table data.

Thanks I will give it a try.

Happy to review your progress and provide some reflections / views. The model I provided needs to be carefully reviewed - I have made some adjustments including the one re ExtraD. The form for Client allowing Dependents to be entered does not dictate that Dependents need to be managed through a separate table (I would still advocate for a common Client table with the ability to identify who is the "parent" or "carer" of the client.) Queries can handle the views needed to support subforms for any number of dependents.
Does "Fed" in VisitT refer to meals provided. Again, if so, and visit is recorded for a client and their dependents, it would be simple to record a meal was provided to the client and each dependent on a visit, and the count of meals can be calculated - to clients and dependents (Dependents being those that do have a DependentOfID)

Thank you. The Client is either a single parent or they would be the carer \ parent of any dependents. Fed does refer to meals provided. We keep a running total of the number of individuals that are fed at each session. I split the dependents from the clients because the client table would large and since some clients do not have any dependents would be wasting space.
At the moment this is a pet project with no guarantee that it would ever be used, especially if I left the charity!

 

[Gasman]

Thanks for your reply. I tried to post the database last night but the site told me it was too large to post.

You compact and then zip your db. Upload the zip file.

 

[JFTE]

You compact and then zip your db. Upload the zip file.

Thanks.

 

[GaP42]

Thanks I will give it a try.

Thank you. The Client is either a single parent or they would be the carer \ parent of any dependents. Fed does refer to meals provided. We keep a running total of the number of individuals that are fed at each session. I split the dependents from the clients because the client table would large and since some clients do not have any dependents would be wasting space.
At the moment this is a pet project with no guarantee that it would ever be used, especially if I left the charity!

Re running total for meals provided - these are calculations and should not be stored. Your current data structure does not allow you to know which dependents were provided with a meal if at least one dependent did not attend (as one apparent limitation - as your client attends a visit, whereas the dependents are assumed to attend and each has a meal). You can easily queries/forms to dynamically provide the running total for a period, or from commencement of enrolment.
The number of client records would need to be very large. Normalising your data structures minimises data redundancy and hence reduces data duplication/wasted space ... the internal management of data space/volumes is managed by the DBMS - unused fields for a record do not mean the DBMS allocates/reserves the full space in anticipation of an entry.
(I am also involved in supporting a charity - and use an access db to help