Access database design question

[Bopadoodee]

I know this is a lot of background but I want to make sure that you have all the needed info. I am at best a “Technical User.” I am designing a database that will allow our nonprofit agency to better track and report information on programs that help low income families pay for child care. I have taken a few Access classes but I just don’t know where to go from here in my design. What I kept hearing about Access is that the smaller the "chunks" of info are the better your database will be so that is what I have tried to do. I already have 11 tables set up that all ultimately lead back to my Client including:

tblClient – basic information (name, SS#, etc)
tblStreetAddress
tblMailingAddress
tblEmployer – basic info (company, schedule, etc.)
tblIncome – amounts of specific types of income (200.00 child support)
tblSpouse – basic information (name, SS#, etc.) many clients will not have a spouse
tblEnrolledChild – basic info and a link to the child care provider that cares for the child
tblAdditionalFamily – basic info
tblProvider – basic info (name, tax ID #, contact person)
tblProviderLocationAddress
tblProviderMailingAddress

The tblProvider is linked to tblEnrolledChild, tblProviderAddresses are link to tblProvider but everything else is linked to tblClient.

I also have a tblFundingSource, tblCounty, tblRelationship (to the client - child, legal dependant etc.), tblRace that will be used as choices from a list the users will have to pick from.

Problem: Sometimes providers may charge different rates to different programs for the same situation – which should not happen. For example, a 2 year old whose parent is on program A attends fulltime, full week and the provider charges a rate of $150 per week. At the same time another 2 year old whose parent is on program B has the same attendance at the same provider but the provider charges a rate of $155 per week. While those of us that administer the programs are all part of the same agency we each have our info in separate Excel workbooks right now (each have 3 different workbooks with 1-4 worksheets in each). The goal is to put everything together in one Access database so that we can better track and discover those types of errors.

Here is my dilemma, if there was only 1 rate for a 2 year old I would be okay but… There are many possibilities for each age group (and even the age groups are not the same at all providers) such as:

“Weekly Attendance” could be one or more of the following:
0 days per week
1 day per week
2 days per week
3 days per week
1-3 days per week
4 days per week
5 days per week
4-5 days per week
1-5 days per week

Then there could be a “Care Type” of half day, full day, before school, after school or before and after school rates for each of those weekly attendances.

This is what I would like to happen, when one of our staff enters a client into the database they will pick one of the previously entered child care providers, and then pick one of that provider’s previously entered rates. That way we’ll find the discrepancies and program A and program B will be paying the same amounts. I’m figuring that I’ll need fields of age group, weekly attendance, care type, rate or would these be small tables? Would those be nested tables - if so how does that work? Can any of this even be done? How should I link the tables? ????

I think I've hit my "wall!"
Thank you in advance for any suggestions.
Becky

 

[Remou]

You may wish to read

http://www.r937.com/relational.html

You seem to have far too many tables. For example, all addesses can go in an address table and all people can go in a people table:

[tt]tblStreetAddress ) Lump these together
tblMailingAddress ) and add an extra field
tblProviderLocationAddress ) for the address type,
tblProviderMailingAddress ) mailing etc.[/tt]


[tt]tblClient – basic information ) These can also be one
(name, SS#, etc) ) table with two extra
tblSpouse – basic information ) fields, one for person
(name, SS#, etc.) many clients ) type, one for relation
will not have a spouse ) key[/tt]

It is quite possible that these can be the same table, or even part of the people table. Additional children can become enrolled children or even parents at any time.
tblAdditionalFamily – basic info
tblEnrolledChild – basic info and a link to the child care

These two tables seem very similar and could probably become one table.
tblProvider – basic info (name, tax ID #, contact person)
tblEmployer – basic info (company, schedule, etc.)

Not sure about this one.
tblIncome – amounts of specific types of income (200.00 child support)

You probably need a weekly attendance table and you undoubtably need a rates table, something like:

ProviderID
WeeklyAttendance code
Age range
Rate

You can join tables by the various IDs. For example, the people table would have:

PersonID - Primary Key
SpouseID - Foreign Key - Link back to people table
AddressID - Foreign Key - Link to address table

And so on.

It would be a good idea to look at several of the templates and sample databases that come with Access and that you can find on the Microsoft sites. I recall that about a year or so ago someone was doing something similar to your work and posted on this site. A search for welfare and / or family might throw up the posts.