New to Access, need DB/Query advice

[dragnsteph]

I am working as a volunteer this summer, and one of my projects is moving our database of volunteers from years of Excel spreadsheets to a database structure. I've worked in Access before, but it's been some years, and some versions, and none of my projects were as complex as this one.

I've managed to get the personal information sections working as I'd like, with tables for phone numbers, addresses, emergency contact info, etc all linked to a Names table, set up in subforms and tabbed so you can flip through them as the people are entered. It seems to work well, I haven't tested it exhaustively yet but it's working with the 5 or 6 records I've put in for testing purposes.

What I'm having trouble with is the part of the DB that is supposed to calculate hours worked per month, total them, and keep records for "previous years" "current year" and "all time hours". I can't seem to get sums to work. Now, I know in Excel I'd just be able to select the cells that need to be summed, put that formula in the cell and call it a day.

I've tried writing queries but I guess I don't understand them, precisely, and I've tried using Calculated Fields but even they don't seem to show the information in the form as I expected. I understand from what I've read that queries are preferable anyway.

What I'm wondering, I guess, is if I should have set up a whole separate table for JUST the hours information, instead of having it in my Volunteer Details table as 12 fields (one for each month). My query is returning hours by month, but then I can't figure out how to sum them. I'm not very familiar with SQL or other code (I'm a graphic artist by trade), so this is all pretty confusing smile.gif

Happy to post more information or samples of the tables if that would help me get some help - any pointers appreciated.

Thanks,
Stephanie

 

[MarkK]

It's good to understand principles of what's called "database normalization". Google that and read what you find, which will help a lot.

...is if I should have set up a whole separate table for JUST the hours information, instead of having it in my Volunteer Details table as 12 fields (one for each month).

And that will answer this question too. If you want, what I recommend is do some reading, and then post your table structures on here, and ask people for a critique. If you get your tables wrong everything else about developing and using your system will be a pain. If you get your tables right, you'll be constantly surprised by how easy it to do very sophisticated things.
Hope this helps,

 

[jdraw]

A couple of suggestions.
Since it's been a while since you used Access, I'm going to suggest a tutorial on database design that you can work through in 30 - 60 minutes. It goes over the steps involved in design --tables, fields, normalization, relationships. It starts with a business description and leads you to a data model that supports all the business requirements. And it does have a solution. It will act as a refresher, I'm sure.

You can post your database or parts of it if you zip the file before posting. Normally you have to have 10 posts in order to attach a file-- but if you zip, you can post it.

OOOps: I see MarkK has responded while I was typing. I think we're on the same wavelength.

Good luck

 

[dragnsteph]

Thanks for the advice and the links - I have done a couple of tutorials so far, I learn best with videos though and our internet here in the wilderness (Denali National Park) is not always the friendliest for watching/hearing them well.

I tried to attach a screenshot of my relationships table in its current state - the "Project" stuff over to the right isn't done being thought through, but the rest is in progress. If it attaches I'd appreciate any feedback. While I can get the forms to let me enter things that all seem to be linked properly, I'm still not really understanding (despite tutorials) how to write a query that will work in my forms and eventually in my reports.

Stephanie

 

[MarkK]

1) It doesn't make sense to me that VolunteerName and VolunteerDetails are in separate tables. From the fields presented, there can only reasonably be a one-to-one relationship there, so I would put that all in one table.

2) You might want your HoursWorked table to be the join between Project and Volunteer, for instance, if a volunteer works at something, presumably it is a Project, so imagine a table like . . .

tProjectVolunteer
ProjectVolunteerID (PK)
ProjectID (FK)
VolunteerID (FK)
DateTimeStart
DateTimeEnd

See how that allows you too determine the different projects that a single volunteer has worked on. It allows you to very easily calculate the total volunteer hours a project has consumed. And so on . . .

 

[dragnsteph]

Well, I had started out by putting the Emergency Contacts and the NPS Employees in the same "Names" table, but I got too confused so I split them out. There are "Group" and "Individual" volunteers, and a Group will not have all the details that an Individual does. That's the main reason, in addition to the NPS Employees and the Emergency Contacts originally being in the Names table, that I split it out. Then once I changed my mind it seemed harder to put it all back together than just to leave it the way it was.

So you're basically saying I should add the hours worked information to the Project Details table? I guess that does make sense, the Hours Worked table is new today, and the Project stuff is from my original plan (haven't touched any of that data yet). The Many to Many thing with the join table is new to me, I'm not really sure I've structured that correctly or how I should go about adding things to it.

 

[MarkK]

I think a volunteer group and a volunteer person should be in different tables. They are a completely different thing.

What database tables do, if I can blurb out a little, is they model the relationships between things that matter to you, and then store data in that model. The challenge in the early going of writing a useful database system is to accurately model the "things" that matter, and how they are related to each other.

So if you have a VolunteerGroup and a VolunteerPerson, are they related? Like, do your known VolunteerPerson objects always belong to a known VolunteerGroup object? If so, this alters your tables as follows . . .

tVolunteerGroup
VolunteerGroupID (PK)
GroupName

tVolunteer
VolunteerID (PK)
VolunteerGroupID (FK)
FirstName
LastName

See how in this case the Volunteer can structurally be connected to a Group? There are facts and patterns like that in your data that you need to model correctly for your specific problem. Does a VolunteerPerson always work hours that are connected to a Project? Then those hours belong to the Person AND to the Project, so you would expect a link from the Hours table to both those related tables, Volunteer and Project. And all that stuff should be reasoned through and intentionally designed. And if you get that right, then your system will function properly.

Hope this helps,

 

[dragnsteph]

It helps but gives me more questions So... the short answer is no, the individuals do not always belong to a group. Sometimes they do, but that has not been tracked in the existing Excel system. The members of a group only exist in the paper in the filing cabinet. It has to do with how much paperwork they fill out, mostly. A "Group" is a shortcut for getting people into the Park and working, like if a church group comes, we just have their leader fill out the form and list the names of the members, and then they're all covered under the park's insurance when they're here. We don't, though, track their individual hours contributed UNLESS they also fill out their own individual form. Maybe we should, but this being the government paperwork is already so onerous we try not to make it worse.

Sometimes, too, there is a Project that's say over just one day (Like National Trails Day a couple of weeks ago, for example). That's 8 hours each for the 18 people who signed up and filled out individual forms. In contrast, my 2-month long job is just me, and it's going to have two hourly entries, one at the end of June and one at the end of July. It's not a "Project" exactly, but it has a job code. It just lasts two months instead of one day, and I'm the only one who gets credit for it.

So I guess if I'm understanding you right, I shouldn't connect the tables if they don't always match up? It is conceivable that members of a group could also volunteer individually, but that's not currently tracked and I don't think there's any reason it needs to be unless the person in the group specifically is concerned with documenting their volunteer hours.

Does that make sense?

Anyway, yes, a person/group ALWAYS has a project code of some kind, but those codes are used more than once. Example: Dog Walkers, there are 90 of them, they work all summer with varying hours. They all have the same job code, but will be tracked as individuals rather than as a project.

Ultimately what we need this database to do is:
1) Pull out Volunteer Hours by division (each job is within a division of the park)
2) Calculate costs by category (not related to this DB)
3) Count people participating in different aspects of the volunteer program (housing, etc)
4) Count total numbers of volunteers in a year and also call out counts with certain qualifying factors (age groups, disability, veteran status, etc)
5) Be able to report hours both total and current year for individual volunteers
6) Keep contact and emergency contact information on individuals and groups
7) Keep a record of Projects, their hours and who participated in them

I think that's it (nothing, right? should be easy! ha).

 

[MarkK]

Ok, let's take Dog Walkers. Not a project, but there are 90 of them. Where is that stored in your system?

They all have the same job code, but will be tracked as individuals rather than as a project.

Where is the JobCode stored? How are VolunteerPerson objects connected to the DogWalker Job that is not a Project? So this is the kind of thing I mean about table design. If you have a concept "Job" that is distinct from a "Project," then you probably need to Job table. Maybe one Project has many related Jobs?

 

[dragnsteph]

Job and Project are, I guess, really the same thing. A Job is just a Project with one person. The job code is a letter code that's made up of a letter for the division, then an abbreviation (there's an existing list of these codes) of the job title. For example the dogwalker one is I-KEN-DW, for Interpretation-Kennels-Dog Walker.

I had set up a table that keeps track of those codes, sometimes they have to be added to when a new volunteer position is created.

So my idea was that I would enter the dog walkers as individual volunteers, then when I am entering their hours worked, I would create a dog walking "project" (or job) with that participant that they spent however many hours on each month. A report on that individual would pull up that they spent x number of hours volunteering in that month, and in theory you should be able to pull up a tab that would say Month: X Hours, Dog Walking. then Y Hours, Other project. Total hours: X+Y. You could also pull up a report that summarizes activity for the year that says 90 people volunteered as dog walkers, with a total of however many hours.

 

[dragnsteph]

So as I think through this, maybe I just get rid of groups altogether as an idea, and when we get a group registered, since I'm still getting their names, just make sure I have who is under 18 (since that's the only group info that's relevant) and enter them as individuals with just less information? I won't have contact info and emergency info for anyone but the group lead, though. My brain is starting to get buried and my tables confused

 

[dragnsteph]

Ok. Take a look at this, if you would, and tell me if it's more what you were saying before. I'm not sure I joined the Hours table correctly, my old one had two Primary Keys, does that one need two as well?

Stephanie

 

[MarkK]

That looks better off the top, but now let's "test" it a little. A volunteer walks up, wants to work, so you take his personal info, zero or more addresses, zero or more phone numbers, zero or more emergency contacts. Fine.

I'm suspicious of the Hours table though. Your table has only Hours, Month and Year, so if Bob walks a dog for one hour on Monday, and one hour on Wednesday, and one hour next Monday, how does that get recorded? Those three records will all look exactly the same in your table, and the distinction of what day he worked on is not stored anywhere. Is that right?

Also, what does it mean that one employee can have many JobCodes? I don't get that. A JobCode, more probably you just mean a job, is a type of work, right? So what does it mean that one job is a child record of an employee? Is that employee the Supervisor of that job type? If so, you might name employees foreign key in the Job table to SupervisorID, which when linked to the Employee table, describes a fact about your data.

Feel like it's moving ahead? This is exactly what the proper process of development is in the table design phase. These are the questions you ask yourself, and run test data through these scenarios, and make sure the tables are adequately designed for the purpose.

Hope this is helping,

 

[dragnsteph]

Hours are reported monthly. So, while we might get the date an activity is performed, more likely we will get "30 hours in the month of June," if it's an ongoing activity, or "8 hours at day-long event on June 3rd". The person who came to the daylong event MIGHT volunteer again in June, but it would probably be under a different job code, and if it's not (say two separate day-long projects working on Trail Maintenance), the difference isn't important for the purposes of reporting. Just recording the hours under the proper job code. If the Dog Walker from before worked 15 hours dogwalking in June, then 8 hours at that trail maintenance event, they'd have two entries under their jobs (two job codes) and the hours they devoted to each job code. The days they performed the actual work aren't important, and there's such a lag time in getting the information turned in (it's on paper, from those NPS supervisors, usually) that even the date entered wouldn't be helpful and would probably skew the reports if they went into the next month.

The NPS Employees are more a designated contact for each job code. The Dog Walker, for example, is supervised by the Kennel Manager. She's the NPS employee responsible for that job code, and for turning in hours, and she's who my boss would call if she had questions about dog walkers in general. It's NOT important to know which NPS employee supervises each individual session of dog walking, just who we should call at any given moment to determine who is in charge of dogwalking. So if the supervisor changes, that's not a problem. I'd like for the job code to be associated with the contact info for the employee, and be able to change the supervising employee if the person leaves the park and is replaced, without breaking all the other records - it's fine if old records change to show a new employee contact, they shouldn't show a contact as a person who is no longer with the park.

Yes, this is helping, very very much! Thank you so much for all your time and input. Learning on the fly is tough and the temptation to take shortcuts is great - I have tried very hard to do the groundwork without getting too far behind.. I still have to enter all this data, too!

Stephanie

 

[MarkK]

OK, so it sounds like your tables match the purpose your have. What I would do now is start entering some of that data, and see if you run into snags. Do you have data that doesn't have a home? Do you have fields that don't get used? Do you have connections you can't represent? Do you have results you can't calculate given the current structures?

Nice work.

 

[jdraw]

Stephanie,

Good work. You might find the stump the model (or your customization of it) a useful approach as you test you model/data structure.

Good luck with your project.