Database Queries, tables and forms

[LauraPat]

Hi,

I really need some help.........

Background......I work for a small charity who support families of children with additional support needs. As they are a very small charity, they do not have funding to purchase a suitable means of recording essential information. Currently they are utilising a very basic Access database that does not hold all the required information that we hold as a charity. Therefore, information is not easily accessible to the small team (particularly since working from home).

In my wisdom I suggested that I attempt to develop a new database that collates this missing information.

I have progressed really well until now although feel like I have hit a wall and cannot progress any further until I can resolve some small issues.

Here is my issue:

I have a contacts form - to collate all personal info of each contact to our service
Within the contacts form there is a child details subform - relates to contacts form, collates information regarding contacts child
I have an enquiry form - each time a contact uses our service an enquiry form is completed

I am really struggling with how I can auto-populate information from both contacts and child details form to the enquiries form.
For instance, I would like the contacts name and childs name to be available on the enquiry form when it is loaded.

I have tried various methods to resolve this issue and have managed to get the contact details to populate, however it still shows all children from the child details table and not the child that relates to the contact. I am struggling to get my head round the query that is needed to execute this task.

Any help would be very much appreciated as my employers are very keen to begin using this new database.

 

[mike60smart]

Hi,

I really need some help.........

Background......I work for a small charity who support families of children with additional support needs. As they are a very small charity, they do not have funding to purchase a suitable means of recording essential information. Currently they are utilising a very basic Access database that does not hold all the required information that we hold as a charity. Therefore, information is not easily accessible to the small team (particularly since working from home).

In my wisdom I suggested that I attempt to develop a new database that collates this missing information.

I have progressed really well until now although feel like I have hit a wall and cannot progress any further until I can resolve some small issues.

Here is my issue:

I have a contacts form - to collate all personal info of each contact to our service
Within the contacts form there is a child details subform - relates to contacts form, collates information regarding contacts child
I have an enquiry form - each time a contact uses our service an enquiry form is completed

I am really struggling with how I can auto-populate information from both contacts and child details form to the enquiries form.
For instance, I would like the contacts name and childs name to be available on the enquiry form when it is loaded.

I have tried various methods to resolve this issue and have managed to get the contact details to populate, however it still shows all children from the child details table and not the child that relates to the contact. I am struggling to get my head round the query that is needed to execute this task.

Any help would be very much appreciated as my employers are very keen to begin using this new database.

Hi Laura
Are you able to upload a zipped copy of the database?

 

[The_Doc_Man]

If it ever occurs that both of the following two things happen, your DB is incorrectly structured:
1. You have a contact with a parent and your inquiry form needs to relate to that parent
2. You have a contact with a child (whose parent is on record) and your inquiry form needs to relate to that child

If both of those can happen, then children cannot be in a separate table because they are not always in that subordinate role. I'm not talking about the role of being a child, but rather, the role of being a person of inquiry. I.e. sometimes they are the focus. When that happens you need a structure that places them on a par with their parents or guardians.

There are many ways to handle this. The easiest is

(a) Make a table of the family members (individiually), with a code in the Members table that shows whether you are talking parent or child. Each member has his/her own PK as a person ID. Easily could be autonumber.
(b) Make a table FAMILIES where you assign a family number. Again, could be autonumber.
(c) Make a table (that we call a JUNCTION table) that has records that says, in effect, "Person 1 is in family 1" "Person 2 is in family 1" "Person 3 is in family 2." Etc. etc. Then, a simple query would tell you that if person 1 is your contact, you want to find all the members of family 1 for your list.
(d) Put ALL of the family members (parents AND children) in the sub-form. If you make father and mother as code 1 and then put children as code 2, you can sort by code in the sub-form to put parents first in the list.

The only wrinkle would be if you ever have more than one generation in a family. You might have to decide whether that is two families or 1, and in that case there are ways around the problem. I'll avoid that discussion for now because I don't know if you even like this idea.

 

[Pat Hartman]

If you would like help, I would be happy to make a charitable donation. We can discuss details. It is important to sanitize your database before you upload it here. Make a backup and then zip the backup. Rename the backup so you know it is for testing. Run an update query that changes all the last names to be just the first 2 characters. Do the same things to city and make all the states "DC". Also remove telephone numbers and if you are keeping SSN (you shouldn't be), replace that with a string of 1's.

Send me a PM to discuss what I can do.