Tracking Volunteer Hours Totaling hours and sub totals in areas

[davet354tfd]

Just getting back to using Access after many years of being away from it.



Set up a data base to track volunteer hours in various areas of a local historical society.

Data base has: User number(auto generated), date, name (from drop down box), Description of activity (from drop down box), Start time, End Time, total time (calculated from Start and end time)

I have input many records of good data. (created an input form for that)

Tried to create a query and report. Having a little difficulty totaling the time for each person in each area that they volunteered in and a total time.

I figured once I get that set up I would worry about doing the same report for any specific month.

That is it in a nut shell.

Bob

[Gicu]

Create a totals query in which you group by person (UderID) and area and sum the time {[EndTime]-[StartTime]). You don't need or want a TotalTime field in the table as it can get out of sync with the values it depends on (start and end times) - you can always calculate it where needed (query, report, form).

Cheers,

[davet354tfd]

Thanks that makes sense.

Now, how is all of that done?

Doing what I have done, that part is coming back. The query and reports not so much.

I will leave the total time in the table but not do anything with it for now.

I am using military time for my start and end time.

Eventually. once I get this figured out, I will want to pull individual reports, reports by month and yearly totals.

Bob

[ssanfu]

Welcome to the forum...

Data base has: User number(auto generated), date, name (from drop down box), Description of activity (from drop down box), Start time, End Time, total time (calculated from Start and end time)

I hope you do not have spaces in field names.
"Date" and "Name" are reserved words in Access and shouldn't be used for object names.

When you say "name (from drop down box)" are you meaning you set up a Look up field in the table?? (this is bad) See The Evils of Lookup Fields in Tables


Maybe you would post your dB so we can see where you are at?

[davet354tfd]

Tried to upload it and it told me it was too big.

[CarlettoFed]

You must first compress it in a zip format and then attach it

[davet354tfd]

THS Volunteer Hrs.zip

Here it is zipped. Hope I did it right attaching it.

Bob

[ssanfu]

Yes I was able to download it.


I've made a lot of changes but there is a lot more to do.

I removed spaces in object names
I removed the calculated field in the table.
I created several queries and forms.

I would have a form with start and end dates to be able to have reports filtered by dates. The filter would be set in VBA code.

Take a look and see what you think..

[davet354tfd]

Steve:

I can't thank you enough for what you did with my initial attempt at creating this data base.

I understand what you have done, just don't know how you did it.

With the report(s), I would like to add a individual total to each person on the report that you have there. How is that done?
Also, in reports, I would love to be able to get the total hours per year and per month in all the areas for each person, Be able to pull yearly reports for totals in each area.

We are a historical society and we need this info for when we apply for grants and funding.
I am willing to do the work you would just have to gently guide me along in the process of creating the reports.

Thanks

Bob

[ssanfu]

It is just like learning to ride a bike - you have to just do it.

For the report "TotalHoursReport", you built it, but I changed it somewhat.
I never use a table for the report record source - always a query.
I created a query to select the fields for the report. I set a sort order, but that was/is for me to be able to see records in the correct order. The sort order in a query has nothing to do with the sort order in a report - reports have their own sorting and grouping. (button in the ribbon)
Once you have the report layout designed, add the fields.
Then it is a matter of making sure that the results match what you calculated manually.
Creating and refining a report could take many hours...... especially if you are just starting on reports or haven't created reports in along time (referring to me).

In the attached dB, start by entering a start date of 1/1/2019 and an end date 12/31/2019
If you open the query "TotalHoursQuery" you will see all records. Compare those records to the report.
I didn't add a report total.....yet. See if you can figure it out.

If you change the start date to 10/1/2019 and end date to 10/31/2019 and open the report, you will have a monthly report. So by changing the dates you can get yearly, monthly or weekly hours.

You can design other reports (say, just the volunteer, not by activity) or be able to pull yearly reports for totals in each area.

[davet354tfd]

Steve:

I'm trying really hard to add the "Report" totals to this.
Where is the "Layout" view? It was there before but now it is gone?
When I right click on the report there used to be 3 views I could select. Now there is only 2 and the layout one is missing.

Bob

[ssanfu]

Sorry. When I configure a dB, I always disable "Layout view". IMHO, "Layout view" is a total waste of time. (I also turn off the "Wizards".)
I also make some other changes:




I make a few more changes to the dB.....

[davet354tfd]

Steve:

I understand why you do that. I feel the need for it for now until I get myself more comfortable with it.

Will try and tackle some other reports tomorrow.
We are getting a snow storm right now and it is supposed to turn to rain over night then back to snow.
Will not be going out unless I have to for a few days.

Thanks again

Bob

[davet354tfd]

Steve:

OK, It took me 2 days but I created a Query and report to pull the total hours in the individual areas. No detail, just total hours and a final total for all.
Now, How do I put this button on the one page so I can select the report?
I put the button out there but just didnt know how to associate it with the new report.

Bob

[ssanfu]

Would you post the dB with the new report?