Okay I'll try this again

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)

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 )

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

Thankfully my supervisor isnt going to kill me for having to redo everything

(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!

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

Hi Pete,

You are welcome

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)

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

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).

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...

Thank you for putting up with my noob questions

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)

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)
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!

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

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

you're welcome, Pete happy to help

I figured Id post the layout of the db as it stands currently. Already I see I goofed with how I set up the phone numbers. *sigh*

Ah well, back to the millstone

Hi Pete,

move

Phones to the left of People
Location to the left of Addresses

pAddresses to the left of Addresses AND People

Data diagrams should flow from left to right, as we read from left to right

break the relationship between People and Pets

add PetID to the Pets table
remove PrimaryKey index from PID in the Pets table
add Qty to the Pets table (so if they have 2 cats, you can specify that)

add the relationship back so it is 1:many

People-Pets will be a 1:many relationship because people can have more than type of pet

I don't understand why you have a relationship between usys_tables and pTypes unless you are planning to use pTypes for other tables too, in which case you would not have a relationship between pTypes and People

Your Phones table looks fine, as well as the relationship. If multiple people have the same phone number, you will have duplicate PhoneNumber fields in the Phones table. This is much easier to deal with than using a junction table between the two -- mainly because it is nice to use an InputMask for PhoneNumber and, when using an InputMask, even if you store symbols (as I do), it is tricky to pick them from a list.

You may want to add PhoTypeID to the Phones table so you can specify what type of phone number it is

PhoTypes
PhoTypeID, Autonumber
PhoType, text, 10-20

ie: fax, cell, home, work, home2, mobile, vacation, summer home

You may also want to create

PetTypes
PetTypeID, Autonumber
PetType

and store PetTypeID in the Pets table

See this is why Im not a DB programmer normally

I removed the usys relationship (that was merely guesswork on my part in the first place).

I [i[had [/b] beeb wondering how Id codify people's phone numbers (and I figured it'd be another lookup table) so that's taken care of.

Dont you mean to move Phones and Location to the RIGHT of People? They are already to the left of people . I moved everything around so the People table is on the far left and then fanned all the relationships out to the right as they link with the 1:1s on top and the 1:many's below them. (pic will follow when I am finished putting the two new tables in).

I'll spend the next few hours making sure all the descriptions are filled in (*I* know what they all mean more or less, but anyone who has to deal with this AFTER me is another matter entirely).

One quick question: when would you use a table WITHOUT a pk (if ever)?

I'm going to need to add another table to the DB for housing (separate from addresses). The information here is specific to the individual (a client can only have one set of answers to this specific set of questions). Is this correct?

Housing
PID, long integer, pk (1:1 relationship with People table)
HouType, text (Rent/Own/Other, will be filled in from a combo box on a form)
HouCond, text* (housing condition)
HouFurn, text* (housing furnishings)
HouSani,text* (housing sanitation)
HouSteps, long integer (number of steps to get into a client's home)

* these questions COULD be handled by yes/no fields (they are listed on the intake sheet as 'adequate/inadequate') or they could be filled in with a combo box on the form. I havent decided what format to use just yet. The information is for reference rather than for any sort of filter/query use (since it will almost always be marked as 'adequate').

Since we need to track whether a client smokes, could I put that in the Personal table or make its own table since we may at some point ask more smoking-related questions?

And the final question of the day (yeah Im full of questions today *sigh*) is: there are several dates we also were thinking of tracking like date a person enters the program, date they leave the program, date of the last contract with the ADS site, etc. Should there be a table specifically for all these dates?

Edited by: petecassidy on Wed Mar 8 13:39:58 EST 2006.

Hi Pete,

yes, I meant to the right ... the "1" table should be positioned on the left and the "many" table on the right

on descriptions -- that is also what is used for StatusBar text when you drag the control onto a form, so another good reason to fill them in

I always have an Autonumber ID in every table, can't think of any good reason not to.

smoke -- put the main question in the Personal table and if true, you can have another table with more questions

dates -- depends how you will collect them. if you will have a form with all the dates to fill out and nothing else, it would be good to put them in a table of their own.

Hi Pete,

I just thought of one case where you wouldn't necessarily create a pk -- in a junction table to make a many-to-many relationship

Teams
TeamID, Autonumber
TeamName

People
PID, Autonumber
LastName
Firstname

TeamMembers
TeamID, long integer
PID, long integer

Good morning Crystal

I've kinda encountered a bit of a head-scratcher with the person types (or maybe not, Im not sure). The 'people' types we track are good for one end of the data, but there's additional data to track with them that I overlooked (kinda). The thing is, there is an additional layer of description needed. Using pTypes i can designate Clients from Volunteers and so forth, but for example, we need to keep tabs on the agency that refers the client to us (their 'site station' as it were). A similar issue is while a 'person' could be an emergency contact for a client, we also want to know their relation to a client (dratted pesky clients!) From how we seem to track data, it only applies to clients and emergency contacts.

It seems like the information relates somewhat to the pTypes, but I dont know where to track it best.

Im going to go re-read that thread you posted initially. Im seem to keep getting stuck on how to separate out the things Im tracking. SOME of what you've said makes perfect sense, some of the other stuff... not so much... I TRY and group things logically, but something's not clicking in my abused little brain.

*sigh*

I'll be glad when this silly dB is finished, so I can work on the next project for the group (instructional videos, something Im MUCH more familiar with)

Hi Pete,

The real purpose of the People table is to

1. assign a Unique number to each person -- whether they are human or an artificial entity.
2. have one way (PID) to relate to Addresses, Phones, eMail, Websites, etc -- which need to be tracked for almost every type of person
3. in my AddressBook program, I also use an Anywhere Notes feature -- a note can attach to just about any table in the system and still be related back to PID. This is nice so you can consolidate notes that relate to anyone -- see notes on their Address, Personal record, Phones, etc -- all in one place ... and each note ties back to a different record in another table.

Often cases, you will have additional tables for certain types of people with more specific information

Hi Crystal,

Yeah that does make sense overall. The client 'site stations' come into play when it comes to reporting out to the grant-funders (and printing timesheets for the volunteers).

Hrm... its starting to come together actually. I sense Im getting closer to the end of the 'design' part of the dB.

Then its onto the 'fun' part - sewing it all together with forms.

Joy.

I'll post another layout for the relationships a bit later... meetings to go to

Hrm...

Crystal, when I look at the data Im trying to put INTO the system, I see that volunteers, clients and ADS sites all need the following info:

Date they joined
Date they left/retired/etc.
and in the case of ADS sites, date of last contract with our organization (those update on a more-or-less annual basis)

I dont see what else I'd attach it to (maybe the 'People' table?), whats the downside of having it as its own table? (Im usually better at figuring out things by understanding why NOT to do things).

Oh and here's the new layout:

Hi Pete,

yes! you are catching on ...

you link your ADS table to People
ADS would be a "PType"
and PTypes.tblID would identify the additional table for ADS info

Hi Crystal, its me again

Okay looking over the relationships and whatnot, I had a moment.

Why does the pTypes table have a TblID field but none of the others do?

You said at one point: "usys_tables is for assigning each table a unique ID"

How does it do that? (I think my question becomes more of a 'is there something else I should be doing with it or does Access do this for me? kind of question)

Also, Im having trouble visualizing something you mentioned in your first reply to my post. You mentioned creating a table

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

First, Im not sure how the VolID field comes into play.

And how does one attach one 'person' to another? It seems like I need another table in there somewhere to connect it together?

Also, there are different "kinds" of time we track: working time, benefit time(vacation if you will), Emergency time, In Service time, and UTO (unpaid time off). Does this neccesitate another lookup table like this?

TimeTypes
TimeTypeID, Autonumber
TimeType, text

ESPECIALLY since we need to be able to do calculations based on the kinds of hours involved. (we need to know how much time they have worked, how much benefit time they've used... etc)

Im gonna keep looking at this and see if Im overlooking something, but I do see that Im undrstanding MORE of it than I did when I first posted! YAY PROGRESS!

You are amazing btw, just thought I should mention it again

Hi Pete,

"Why does the pTypes table have a TblID field but none of the others do?"

that was my question to you -- it shouldn't be there

""usys_tables is for assigning each table a unique ID"" -- yes, that is for a feature such as my Anywhere Notes, which you do not have and if you wanted to add something like that later, you could.

"First, Im not sure how the VolID field comes into play."
Vol(unteer)ID identifies the volunteer (or, you could do PID_vol) to distinguish them because they will have a PID
PID identifies where they went -- maybe PID_meet or PID_loc or something to distinguish the PID

the TimeTypes table sounds like something you may need

"You are amazing" ... thanks, Pete

Hi Crystal, thanks for the response.

The TblID is from your first post to me (scroll back and read it) I kinda copied the initial DB structure as you laid it out. I was kinda confused by it at that point too LOL...

And your Anywhere Notes (or something like it) sounds like it might be potentially useful in future, so I'll leave the usys_tables in there

Now I KNOW there are going to be 24 pay periods annually (1-15th of the month, and 16-end of the month), could I structure tables so that rather than worrying at ALL about dates per se I could simply 'name' the time periods we're recording? Perhaps the year would also be worked into that table (or attached to it) so that it would be done by pay-period/year.

Ah well, till monday! Enjoy your weekend! (hope the weather's warm where you are too!)

Hi Pete,

Sounds like PayPeriod would be a calculation



as such, it should not be stored

~~~~~~~~

There is one use for tblID in PTypes, ... to identify the table where additional information about that Person Type is stored. ALL People could have a personal or a company record (because they would be human or an artificial entity). Beyond that, you might use a table that could be identified to the system to track extra information you need on Volunteers, Clients, ADS, Transport, Housing, etc...

Ugh... calculations... bleh...

Not exactly a fine way to start monday morning

Could you recommend a good resource for me to at least start to grasp the VB stuff?

Also, Ive always been a little fuzzy on calculating vs. storing... I know things like Age and whatnot are calculated rather than stored... but how would you be able to tell what hours a person worked in, say, the first half of January?

*sigh* Every time I think Ive got a grasp on this, something new comes up...

*chuckle* such is the life I suppose

Hi Pete,

don't be scared off by calculations...

How to learn --- Syntax --- VBA --- SQL
http://www.utteraccess.com/forums/showflat...;Number=1102351

make a Totals Query based on your table
View, Totals

field --> PayPeriod --> ( Month([datefield])-1 )*2 + 1 + ( Day([datefield]) \ 16 )
Totals --> GroupBy
criteria --> 1 (if you want to limit it to first PayPeriod)

field --> hours_field
Totals --> Sum

Okay whilst I chew on that I wanted to doublecheck that I read you right in that if I wanted to further normalize the addresses, I would put fields for CityID, StateID and ZipID in the addresses table and then make the appropriate tables accordingly. Looking at the potential data it makes no sense to store the same zip codes over and over and over again

Have a good one!

Hi Pete,

you are absolutely right ... and the same with states and cities ... you can use ID's in the Addresses table for those as well.

I do have a ZipCodes mdb file with > 42,000 records for the United States if you want to lookup city and state from zip code. It is too big to post; if you (or anyone else reading this) wants it, send me an email and I'll send it to you. It even has Latitude and Longitude in it, so you can identify cities/towns that are in the same area.

Unzipped, it is about 7M; Zipped about 2M.

Hiya Crystal,

Okay while I still digest this programming stuff (its making sense, slowly but surely), I wanted to check something with you.

There's a probation period for our volunteers (X number of hours) and once that probation is done they gain vacation time accordingly.

I know the vacation time would be handled by calculations, but the main question is would the probation itself be handled by a stored yes/no field tied to the VolID? I would imagine there's a way to automatically take someone off probation once the accumulated hours reach the threshold. My concern is how we could put volunteers who've already been with the program in as NOT being on probation.

Thanks

Hi Pete

you can store a probation end date, which you may want to refer back to in the future anyway.

on vacation, even though it is calculated, that is something that I would store as well.

I see what you are saying Crystal, but wouldnt the date be an imprecise form of making that calculation? They have to do 160 hours of 'probation' work before they come off promotion. When they come off promotion they immediately get 'benefit time' equal to 10 days (40 hours). After that (hours 161+) they earn benefit time at the rate of 5 hours (1.25 days) per every 44 hours worked. And its ENTIRELY possible that a person may cross that 160 hour threshold mid-working shift. It doesnt help that my boss uses both units (days and hours) interchangeably

Im also looking at the three vb chapters you wrote (Ive read them, re-read them, read them again, read them out loud, read the chapters in reverse order... ) and Im STARTING to see how things work together... I think the slow uptake on all of this is just due to lack of exposure to it. Its starting to come into focus, and Im starting to see how tihngs might work... just need to keep playing with the coding and stuff.

The actual timesheet/benefit time/exception time stuff isnt as bad as I IMAINGED it might be, but it certainly is making me learn all SORTS of new things about Access

Nothing like a challenge, is there?

Hi Pete,

read them out loud??? I am impressed!



when you fill the date (which you can fill with date/time if necessary), you fill the benefit -- then, if a date is there (which is locked to users), you can assume the benefit is already given ... not exactly sure what you are asking...

Hee hee, sometimes I read things out loud because sometimes I retain things better by hearing it rather than reading it.

Well having there be a date filled in WOULD INDEED mean they would no longer be on probation (and would tell us how long they were on probation in the first place). And having a date would make it easier to sort ppl who are and are not on probation.

Let me see if I can explain this better: Probation isnt tracked by DATE, its by HOURS on the job. Under ideal circumstances, a volunteer would reach the end of their probation in 8 weeks of 'working' (8 weeks x 20 hours = 160 hours, which is the 'probationary' threshold). As well all know, the reality of the situation is that a volunteer may cross that 160 hour mark somewhere DURING a workday. Once they've completed their 160th hour, they being accruing 'vacation' time (I hate the term 'benefit time'... it seems unwieldy.

So if they were going to see X client to spend 4 hours with them, and already had 158 hours under their belt, 2 hours of that would be working towards ending their probation, and 2 hours of it would be working 'normally' where you earn 5 hours of vacation per 44 hours worked.

Yes its convoluted as all get-out. I would NOT have set it up this way personally, but its what the grant was written (and funded) for.

So in short:
if volunteer hours < 160 hours = no benefit time (probation period)
if volunteer hours = 160 hours + 40 hours vacation time
if volunteer hours = 204 hours + 5 hours

and so on and so on, for every time the volunteer gains another 44 working hours.

But wait, it gets better (and perhaps simpler?)

If a volunteer USES benefit time, its treated as if they worked, so they EARN vacation while they TAKE vacation. This essentially means that every hour a volunteer gets paid for, counts towards more vacation time.

We also have other time codes too (emergency days like for inclement weather) and they too count towards vacation time calculations

I told you it was confusing

Edited by: petecassidy on Thu Mar 16 16:19:54 EST 2006.

Hi Pete,

looks like a general function to get vacation hours is in order...

put this code into a general module

Okay, that makes sense. Does that leave us with room to adjust times if we need to give someone additional hours of vacation time?

Oh and Im kinda curious about where I should attach yet another bit of data we want to track. For Volunteers only (apparently) we track whether or not they have had their physical exam for the year. I would imagine its a yes/no field, but I wonder if it could be added to the Personal table or whether it'd be its own table. It seems like adding it to the Personal table makes some sense, but its also something that ONLY applies to a specific person type, so there'd be a LOT of blank associations (or a lot of default 'no' answers).

I considered starting a table JUST for medical stuff and then be able to link it to the people (since we might want to keep a list of allergies and whatnot as well as whether theyve had the physical).

YAY for flexibility!

Hi Pete,

since they need a physical exam every year, you will have more than one value for date (which is what you should store)

Physicals
PhysicalID, autonumber
PID, long
PhysDate, date

perhaps you want tp come up with a generic term -- like Tests or Exams... anbd have one type be a Physical ... you want to make your structure flexible enough to accomodate like information

Ah and that makes sense, if you wanted to show whether a person had a recent physical you could just have it conditionally change the color of the text if its been more than a year?

Hi Pete,

absolutely!

what you are doing now... planning before you build... is the best way to approach a successful database -- you have a lot of detail that requires much thought.

When I am in the stage you are now, I modify data structures and print relationship diagrams frequently...

You are probably ready to start planning your main/sub forms for the parts of your your structure that are solid while you continue working on the rest of structure.

Rather than using the TAB control and embedding a bunch of subforms onto a main for, read about using one subform control and swapping the Source Object...

tab control vs replace SourceObject
http://www.utteraccess.com/forums/showthre...;Number=1076790

Heya Crystal,

Yeah I actually print out my relationships page every time I do a major revision and then highlight the relationships in different colors so I can see how the information propagates across the database itself.

I definitely will look into the link you posted, but are there other basic how-to's on starting your forms up that could point me to as well? I feel silly for requiring this much hand-holding, but this isnt my main area of expertise

Thanks as always -o!