Okay I'll try this again (IMG:http://www.utteraccess.com/forum/style_emoticons/default/laugh.gif)

Here's the situation:

I am trying to get a sense of how I should be approaching this DB creation.

We are a NFP has Senior Citizens that get paid a small stipend to volunteer helping other Seniors (shut-ins, alzheimers people, etc). This is the data Im keeping

Volunteers - the Seniors we have serving in home clients and Adult day services
In-home Clients - Clients who the vounteers visit in their own homes
Adult Day Services - ADS centers where the volunteers 'work' at.
Time each Volunteer spends at each client/ADS on a semi-monthly basis.

Where it gets confusing for me is that each volunteer may have up to 5 sites/clients they visit (this is a rarity, but it does happen. 5 IS the maximum tho). And while an in-home client only sees one volunteer an ADS potentially has more than one of our volunteers visit.

The way all this information interacts is what's stumping me. I have tables for the Volunteers, Clients, and the ADS sites, but I dont know how I should go about 'connecting' them... let alone how Im going to track the volunteer's hours at each site...

I wish they had it in their budget to go send me to a class or two (or several) (IMG:http://www.utteraccess.com/forum/style_emoticons/default/tongue.gif)

combine the Clients and the ADS sites into one table so that you can have one Autonumber field to represent places a volunteer may spend time

People
PID, Autonumber
Lastname, text -- ADS Center name or lastname of client
Firstname, text -- firstname if client
pTypeID, long integer

pTypes
pTypeID, Autonumber
pType, text -- In-home Client, ADS Center, Personal contact
TblID, long integer -- this is where more information on this site will be stored

usys_Tables
TblID, Autonumber
TblName, text -- Clients or ADSsites

Addresses
AddrID, Autonumber
Address, text
City, text
State, text
Zip, text

you could further normalize the addresses by storing CityID, StateID, ZipID and having tables for Cities, States, and Zips. Then you would just store ZipExt in the addresses table if filled out.

pAddresses
pAddrID, Autonumber
PID, long integer
AddrID, long integer

Since the information in People will be humans and companies, you use pType to determine the table where additional information is stored. All addresses will link to the People table. btw, in law, a "person" represents a human or an artificial entity such as a company.

VolTime
VTimeID< Autonumber
VolID, long integer
PID, long integer
TimeIn, date -- date/time of visit or appt
TimeOut, date -- date/time visit end -- or you can store Length of visit in minutes or hours

hopefully this gives you a good starting point!

Here is a post with information on setting up data efficiently:

A new guy figuring out how to search -- discussion on normalizing data and designing tables
http://www.utteraccess.com/forums/showflat...p;Number=619663

about halfway through the post, there is a lengthy discussion on data structure, naming, etc ... and about 1/3 from the end is instructions for documenting the relationships and table structures -- this is an invaluable reference as you are building your database

Okay I THINK I see where I should be going with this.

Now based on this there's also some other denotations I need to make. I also need to keep emergency contact information on our volunteers and clients. According to what you are saying Id simply list them under the 'people' table and then further denote in the pTypes table that they are emergency contacts (and associate them with their repsective People)?

Also I need to track additional health/home information for the Clients (mostly a series of yes/no questions) and some additional information about the Volunteers (transportation information & some medical info) Do I just create tables with this information and link it to the above?

Thanks, this is just now STARTING to make some sense (of course Ive been going about this all the wrong way (IMG:http://www.utteraccess.com/forum/style_emoticons/default/laugh.gif) )

Oh and I should add: we are also storing addresses and whatnot for the companions, those would be put in the addresses table too right? (where do I put in Dates of birth? we keep them for everyone except ADSes)

Edited by: petecassidy on Wed Mar 1 9:53:41 EST 2006.

DOB -- I usually create a seperate table for personal information but to make it easier on yourself, you can put it in People and, oviously, make it invisible when pType represents a company -- but if there is other personal information, create

Personal
PID, long integer -- unique index -- this will be a 1:1 relationship with People
DOB, date
etc

You may want to put this field in pTypes (consider future expansion and other uses):
IsCompany, yes/no

That way, you will know to make Firstname invisible for a company

You may have a table for additional Company information as well

Would it break anything if I simply added the Contact Person for the ADS as another Person? You wouldnt assign a volunteer to that person, but on a form (or data acces page, which Im still trying to fathom) you could have that person also associated with the ADS?

Sorry, these are probably needlessly obtuse questions, but I figure its better I ask NOW than later (IMG:http://www.utteraccess.com/forum/style_emoticons/default/laugh.gif)

Thankfully my supervisor isnt going to kill me for having to redo everything (IMG:http://www.utteraccess.com/forum/style_emoticons/default/laugh.gif)

(oh and the autonumbers ARE the pk's for each table right?)

And thank you for every bit of help, I am ever so grateful for the assist! (IMG:http://www.utteraccess.com/forum/style_emoticons/default/love.gif) (IMG:http://www.utteraccess.com/forum/style_emoticons/default/thanks.gif)

Edited by: petecassidy on Wed Mar 1 15:14:53 EST 2006.

Hi Pete,

You are welcome (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)

every person -- whether they are human or a company, would have a record in People. The you would use cross-reference or junction tables to relate them.

You can add more records to pTypes for the different types of people that you have -- you may even want to add another field to that table to categorize them for drop-lists (comboboxes) in case you want a way to Group your types.

"(oh and the autonumbers ARE the pk's for each table right?) " Yes

Heh..

whats funny about all this is that it makes SENSE once you step back and look at it objectively, but almost every online 'tutorial' tells you to do it a more... linear (or 2D) way.

I need to take copious notes on this tho. I know anyone who comes after me in this position will probably scratch their heads at the layout of the DB (unless they have a better foundation in Access than I do) (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)

The simplest way is often the hardest to see... tutorials are notorius for using unrealistic examples to teach the useage of Access -- it they set up data with all the relationships that it truly has, no one would be able to take basic courses without studying data structure first.

To make it easier to visualize, you can put names for human people in the personal table and company names in the companies table -- just use the People table to assign ID's -- but that is another link to make everytime you want to see a name ...

Actually once you explain it to people it makes sense. Its just not what people would do instinctively apparently (IMG:http://www.utteraccess.com/forum/style_emoticons/default/laugh.gif)

Im trying to sort the data we need to collect beyond the simple contact information. Ive made a sort of chart with what data we collect for each type of client/volunteer/ADS/etc. Im trying to see what overlaps (dates of birth,sex, phone numbers, stuff like that). Would it be better to create a separate table for phone numbers though? Seeing as we're tracking several per 'person'. Also I was contemplating creating tables for the unique variables we're tracking for the individual types of 'person' (for example: we keep tabs on whether a CLIENT has pets but not volunteers, and we track whether a volunteer has a driver's licence, but not a client... etc). Do I create separate tables for the unique sets of data collected?

Now when it comes to something like race/sex, I was planning on using combo boxes in a form to enter the data. I recall reading that people advise not to do so in the table itself?

(oh and once all this database setup is done, I'm going to need to make a form to put it all together with wont I?)

Edited by: petecassidy on Fri Mar 3 14:35:26 EST 2006.

the reason to seperate phone numbers is for flexibility -- people may have one, they may have more. They may have 2 cell phones and no land line...

each data set would be in a table structure. If you track pets, you would have

Pets
PetID, Autonumber
PID, long integer
PetTypeID, long integer
PetName, text
etc

PetTypes
PetTypeID, Autonumber
PetType, text -- dog, cat, horse, etc

don't use lookup fields in the table design -- the correct place is combo or listboxes on forms
http://www.mvps.org/access/lookupfields.htm

Okay so if Im following you, by putting the different fields in a form, when you fill out the form the relationships created will cause all the data to be ID numbered and then 'attached' automatically?

I also see what you are talking about with regards to comboboxes (and that would be a BIG ol pain in the rump later on since we do need to query out data *shudder*).

Oh, while I remember it, what function does the usys_table serve (and why is it a hidden table?) Im glad I figured out how to see it (to make sure it was there). (IMG:http://www.utteraccess.com/forum/style_emoticons/default/laugh.gif)

it is not hard to put the additional tables up on a query that you need to show the text values for the ID fields, you will find much more of a pain trying to sort or filter on Lookup fields since things won't work as you expect them to ... in fact, some things won't work at all if you are using fields defined to be Lookups in the table design.

usys_tables is for assigning each table a unique ID -- my "Anywhere notes" feature uses it. A note from any table in the system is

(1) linked to PID
(2) TID specifies the table it belongs to
(3) LinkID is the autonumber value of the the record in that table to attach a note to

Okay while I was trying to set up the relationships, I came across something I dont quite understand:

A) Which relationships will be one-to-one and which ones will be one-to-many?

B) What is the rationale behind having the pAddresses table (as opposed to having simply the Addresses table? Or maybe its that I wonder why there isnt an intermediary table like this for the other tables Ive created.

I havent added ANY data to this table setup, so I could probably squish it down and attach it for reference... (IMG:http://www.utteraccess.com/forum/style_emoticons/default/frown.gif)

Thank you for putting up with my noob questions (IMG:http://www.utteraccess.com/forum/style_emoticons/default/notworthy.gif)

you're welcome, Pete

A) all the lines in my relationship diagram are labeled. The only links that are 1:1 happen because I made the foreign key a primary key (even though it is not an AutoNumber field), which also creates a UNIQUE index. This was done before the relationship was created and Access knows that PID will have just one occurance in each table. Here are the 2 cases that happens for

between People and Personal (there will only be one Personal record per human)
between People and Companies (there will only be one Companies record per company)

B) pAddresses is a cross-reference (junction) table between People and Addresses so that people can have >1 address

Okay Ive included the db as far as Ive gotten.

I have the feeling Im not 'getting' all this, tho I seem to THINK I see where Im going here.

I didnt include the pets table yet. We only want to track whether a client has pets, if so how many and what type. Do I actually need a cross reference table for it?

no, you can put the PID (PersonID) into the pets table and link the pets to an owner

the reason I did not do addresses that way is because more than one person can have the same address

I will not have time to look at your database for a couple days, sorry

actually that clarifies things a bit I think.

I understand yer probably pretty busy (which means the DB will have undergone a few revisions before you actually did get to it) (IMG:http://www.utteraccess.com/forum/style_emoticons/default/frown.gif)

The boss just added yet another condition to track, but its a simple yes/no box and it is based on whether a client/facility falls into a certain location. Before I work that into the database I need to clarify from her HOW this data is being used. From what Im gathering, its based on addresses, so I may add it to the Addresses table (while multiple addresses would fall into the category, each address may either is or isnt in the specified area). Would it be better normalization to use the AddrID field as foreign key in another table with the results of a yes/no box? like this:

NoPennUW (its short for North Penn United Way area, WE'll understand that) (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)
NPUWID, pk (autonumber)
AddrID, fk, (Long integer)
Yes/No (yes no field)

Hi Pete,

Do not create another table for the Yes/No field, there is no reason to seperate it and it will only complicate things for you.

do not call it Yes/No -- don't use anything but letters, numbers, and underscores in names and do not let a name contain special characters such as /

read the "new guy" link I gave you above

create something like this:

IsLocationA, yes/no, DefaultValue --> false

Would it not be better, however, to set up a Locations table and store the LocationID? In the future, you may want to expand its functionality.

For now, you may decide just to put the one Location in your Locations table and if the address is in that location, use a combobox on a form to fill LocationID. If it isn't leave it blank

the foreign key would be

LocationID, long integer, defaultValue --> Null

Whoops, that was me typing fast before I left the office for the day! (IMG:http://www.utteraccess.com/forum/style_emoticons/default/laugh.gif)

The yes/no field was going to be "PennArea" and a yes/no field. I actually AM bright enough not to use anything but letters and numbers (and the occasional underscore)for my field names *chuckle*.

it would probably behoove me to add the extra table you suggest tho, seeing as the grant provider could up and ask for what areas everyone falls into in our county at some later date (wouldnt shock me).

I also went through this morning and rechecked all my long integer fields and made sure they were nulls rather than 0 (like you recommend) I found one that I had missed earlier (IMG:http://www.utteraccess.com/forum/style_emoticons/default/frown.gif)

Edited by: petecassidy on Tue Mar 7 10:02:03 EST 2006.

you're welcome, Pete (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif) happy to help