Beginner Advice for a Family Database

[newaccess user]

Hello and thank you for taking the time to read this rather long winded thread.

I am quite new to using Access 2003. I have inherited a flat database of families that my job requires me to maintain. It is an overview of all things about the family.

At the moment each family has 1 record containing (amongst other detail) Husband first and last name, Spouse, Anniversary, Home No, Mobile No, email address address, child 1 name, Date of Birth and the same for child 2,3,4 or 5, husband nationality, spouse nationality and if the spouse drives or works.

As you can see, this is far to much information for 1 record, especially when I have around 240 families.

one of the many tasks I am required to complete is to produce a quarterly report that lists families by certain address area and how many children live in that area aged between 0-3 then 4-12, then 13-18 and 19-25.

Monthly I produce a 1 pager newsletter and without quoting the date I will wish any child under 13 a happy birthday (its not cool to have your name in the newsletter if you are 13 or over) if it falls during the month my newsletter is produced for.

I have searched through the available industry data modules and could find nothing.

I am kind of thinking my issue would not be to bad if I fully understood relationships (in Access) and how to separate the information out to get the right results (I am in the process of learning):banghead:.

I hope you sort of get my problem and I would appreciate any advice as long as it is aimed at ankle level for me to understand.

I would be more than willing to provide any other information.

kind Regards.

 

[GohDiamond]

There is a database template called Contact Management that may have been installed on your computer when MS Office was installed or you have the option to download templates from MS Office downloads on the net. This template can offer many good suggestions as to how to design your database or it may be "AS IS" pretty much what you're looking for. This would be a good place to start if you want to learn some of the basics of designing the type of database you're interested in.

Each person in the heirarchy should have a "profile" so that you can address them with queries to pull the selected criteria, otherwise getting to the related data such as just the "spouse" info or just the "child by age" info could become unnecessarily problematic.

Hope that helps
Cheers!
Goh

 

[Mihail]

I would be more than willing to provide any other information.

For the beginning, CREATE a COPY of your DB, remove all information (all records) from this copy and upload it.
If you can to keep few records (maybe 10) and to remove only sensitive information from this records will be the best.

PS
You must ZIP the DB before posting

 

[newaccess user]

Thank you for your responses.

I will sanitise the Db - zip it and then upload, but it will have to wait until tomorrow.

Thanks again.

 

[Geotch]

Since you are starting out, I'd just create 5 queries, one for each child field with the DOB. It would be a lot faster than recreating the database or moving the data (which you should do once you fully understand databases). I can upload a sample of a solution if you need it.

 

[newaccess user]

Good evening,

As requested, I have sanitised the Database and made up 3 names with a version of the correct information.

My ultimate aim is to view in a form (with sub forms if required) a Families details by selecting the husband.

A report listing all children that live in Paderborn between the ages of 0-3.

Can this be done with the data currently laid out as it is, or do I need to do some table work with the right relationships??

Thanks for any help in advance.

 

[Mihail]

Tomorrow, I'll try to design for you a properly DB.

Give me some information about the army structure:

ONE unit have MANY subunits
ONE subunit have MANY companies.
ONE company have MANY soldiers

Is that true ? I missed something between units and soldiers ?

 

[newaccess user]

To clarify:

ONE Unit to MANY Sub Units.

ONE Sub Unit to Many Soldiers.

Sub Units and Companies are the same thing in the organisation.

Thank you for your support, I look forward to seeing the DB and gaining some knowledge from it.

 

[Mihail]

This is the first look of your DB.
For sure we have to do more.

Take a look to the tables and especially to the relationships window (Menu bar -> Database Tools -> Relationships)

Think to the databases exactly as to the army structure:
In the real life, if you know the soldier number you know (in fact, you can find out) everything about that man (by reading papers).
The same in DBs. The difference is that is not you who read (it is Access) and the papers are named tables.

I don't understand what you intended to store in Married Quarter Index table.

The main table in my design is tblSoldiers.
I leave yours Welfare Database table in the DB in order to easily check if I catch all the information in my DB.

DO NOT try to use the DB "as is" . The user NEVER NEVER EVER should be allowed to interact directly with the tables or queries.
The users should interact ONLY with forms.
And the forms should be designed ONLY after the tables are OK.

Let me know your comments.

 

[Mihail]

@AB
I have had a lot of troubles in the past because the using of the Caption property.
After a while (sometimes until tomorrow) I forget that what I see is not the field name itself => troubles in designing queries, filter's clauses and so on.

In my opinion this "change" in the field names should be done only at form level by changing the labels as necessary.

 

[newaccess user]

Mihail, AB,

Thank you for your input, I have had a good look at the Relationships screen, and my key take away point, is get to grips with relationships. I was not aware that you could have 2 versions of the same table in the relationships window!, how much of a mongo to I feel.

I will look at each table in detail today and provide any feedback you require.

Thanks again.

 

[newaccess user]

Good evening,

After a good look at the DB i have the following points for your consideration:

1. Married quarter Index is a reference used to locate an address quickly on a Married Quarter Map. If I am required to go to Soldier A's House for any reason I can look at his address and the Married Quarter Index reference and find it quickly. It is a bit like an area code and saves me the time of scouring over a map for hours to try and find the street.

All of the tables are spot on, apart from:

2. tblSoldiers, I have re ordered the fields for my purpose and I do not require DateOccupied, this belongs in the tblSoldiersAddresses and tells me when a family occupied the address.

3. ID_Nationality_Dependant needs to be in the tblSoldiersChildren and could be renamed to Nationality_Child to avoid any confusion.

4. Contact Details: I only require a Home Number, Soldier Mobile and Spouse Mobile. based on the understanding that i may need to get hold of the spouse if something happens to the Soldier or i may need to get hold of the Soldier if anything happens to his Spouse or children.

5. Email addresses are fine.

I have to thank you for your help. It is vastly different from what I imagined and just goes to show I have a long way to go. But I will get there.

 

[Mihail]

Is no need to rearrange the fields in the table. This will be done in the forms (I don't say that is wrong).
Try to fill the tables with some realistic data (not real - I know that should be secret) then upload your (new) DB in order to debug and to go to the next step.

It is vastly different from what I imagined

Yes. This is always the first thing that cross a newbie mind. But is not very hard to understand/learn.

 

[newaccess user]

As requested I have populated the DB fully, with 5 families fictitious information.

Whilst conducting this process i have identified that the tblSpousesAddresses is not required as I only deal with married Soldiers and as a result the Soldiers Address will be his spouses address to.

I hope I have provided enough information for the next step.

Regards,

 

[Mihail]

One more step in your DB design.

There are two tables, from your original DB, that I can't understand where fit in your logic:
1) tblMonths
2) tblOffOr

I don't create a form for table tblSoldier.
It is a wasted time if you have something new to add or something to modify.

Except table tblSoldier, you should be able to fill all other tables via a form.
As I said, the user should never interact with the tables or the queries.
Even for us (you and me) will be impossible to manipulate a large amount of data by using the tables.

Take a new look at what we have.
Be aware: Should be the last time when you do that.
After you will say OK, will be very hard to make changes.

 

[newaccess user]

Hello,

tblmonths has been deleted, not required.

tblOffrOr: Ranks are split between an Officer and Other Rank. Pte-WO1 are OR (Other Ranks) and 2Lt - Lt Col are Officers. I am required to know how many OR's and Offrs occupy houses. The current table was produced to enable me to select this detail in a combo list.

Married Quarter Index: This Table is missing and is required to support the tblSoldiersAddresses, The structure of the Married Quarter Index table is not very good and from what you have produced, i am thinking that i should split the table like, tblMQIndexSennelager, tblMQIndexPaderborn etc?

Other than that, i am sure this meets all of my requirements.

 

[Mihail]

tblOffrOr: Ranks are split between an Officer and Other Rank. Pte-WO1 are OR (Other Ranks) and 2Lt - Lt Col are Officers. I am required to know how many OR's and Offrs occupy houses.

From what I understand, the soldiers can be Officers (higher ranks) and not Officers (Other ranks).
If so, tblOffOr is no more necessary but we should amend the tblRanks, by adding a new field where to store (show) if a certain rank is Officer or not.
Confirm (or not) what I understand: a soldier IS or IS NOT officer.

Married Quarter Index: This Table is missing and is required to support the tblSoldiersAddresses

Still not understand what you expect from this table to do.
Give me an example: I wish to know THIS and I think that the MarriedQuarterIndex is the solution. Replace the blue THIS with an example like you wish to see in the DB.

BTW: Try to use as less words as you can. The English is not my native language. So, much words = much doubts for me.

i am thinking that i should split the table like, tblMQIndexSennelager, tblMQIndexPaderborn etc?

.
Looks like you wish to create some tables for Areas.
If so then NO. Forever. There are other tools that can show us this information.

BTW (again). Can you create a list with the questions to your DB should answer ?
Something like this:
For each soldier I like to know how many children he has, how I can contact his/her wife/husband, the rank, nationality ....., how many phones have ......
For each company I like to know how many soldiers contain, how many soldiers from each nationality are, how many male/female, how many officers, to see a list with the soldiers from each group like this ..........
The same for the unit ...... or more...... or less.
For each nationality I like to know how many mobile phones exist......
etc.

I inform you that your DB is able to answer NOW to all this questions and more but the list of question will help us to design the necessary queries.
BTW(3) At this time your DB is able to calculate how many wife are named Jane or Samantha etc, but I don't think that this is a necessary information for you

I am waiting for your answer.

 

[newaccess user]

1. tblRanks: Rank_ID 1-8 = OR and 9 - 13 = Offr.

2. Married Quarter Index: I wish to know who lives in which index location, for example "HOW MANY FAMILIES LIVE IN PB 1"

3. Questions:
a. Total Families, b. Total Offrs and addresses, c. Total OR's and addresses, d. total families by area (tblArea), e. total children aged 0 - 2 in each area, f. total children aged 3 - 5 in each area, g. total children aged 6 - 12 in each area, h. total children aged 13 - 15 in each area, i. total children aged 16 - 18 in each area, j. total children aged 19 - 25 and over in each area, k. Total families by Married Quarter Index (PB 1, PB 2, PB 3, SE 1, etc, l. total children for each soldier, m. Soldier and spouse contact details (Soldier phone, email, spouse phone, email), n. Total soldiers by nationality.

4. You are correct, I do not need to know how many wives are named Jane or Samantha etc

again i hope i have provided enough information.

kind regards.

 

[Mihail]

After a bit of Google I understand that the AREA's are, in fact, parts of the Paderborn (first area). Isn't it ?
So, your table tblAreas is NOT normalized because contain 2 different things.

As you will see, the program can calculate the Quarter based on the street.
Is not the same for the Area ? Each street is an unique area ?

Take a look to the attached DB and try to find any bug in it.

More filters and reports tomorrow.

Have a good week-end !!

 

[Mihail]

@Everyone
In this DB I have a report rptChildrenByArea based on query qryChildrenByArea (see Reports -> Child/Ages/Areas) that is hard coded.
I hate to do this (to hard code) but I don't know other approach.
Are you so kind to take a look and to give me some solutions/ideas ?
Thank you !

@NewAccess
Verify one more time.
Your DB request one more operation: to be split (after you will say OK)

Answers to your Questions:
I wish to know who lives in which index location, for example "HOW MANY FAMILIES LIVE IN PB 1"
Solved - Filters(Military) -> Quarter

a. Total Families
Already solved - see main form: frmSoldiers
What happen if 2 soldiers (male, female) are married each to other ? => 2 soldiers / 1 family

b. Total Offrs and addresses,
Solved - see Filter(Military) -> Officers

c. Total OR's and addresses,
Solved - see Filters(Military) -> Officers

d. total families by area (tblArea),
Solved - Filters(Civilian) -> Area

e. total children aged 0 - 2 in each area,
f. total children aged 3 - 5 in each area,
g. total children aged 6 - 12 in each area,
h. total children aged 13 - 15 in each area,
i. total children aged 16 - 18 in each area,
j. total children aged 19 - 25 and over in each area,
Solved - Reports -> Child/Ages/Areas

k. Total families by Married Quarter Index (PB 1, PB 2, PB 3, SE 1, etc,
Solved - Reports -> Families by Quarter

l. total children for each soldier,
Solved - see Reports -> Children

m. Soldier and spouse contact details (Soldier phone, email,spouse phone, email),
Already solved - see main form: frmSoldiers

n. Total soldiers by nationality.
Solved - see Reports -> Nationalities