help with coding...

[Mr. D]

Hi, I wasn't sure what to search for, so I decided to make a new post.
Search ideas are welcome as well as solutions.

Here's what I'm trying to do...Made a database for a local non-profit
organization, and they asked for a bit of an update. quick explanation of
the database:keeps track of volunteer information including hours volunteered
at that location as well as several of their partners. What they want added
is the ability to print a report listing everyone with more than say 100
hours of volunteer time.

What I currently have working - they bring up a form that allows them to
select one of their volunteers from the list, it then goes through and
calculates their total hours (via use of a query for records matching that
individual's name, then totalling all the hours in each record to return
their total time). It will then display a message box stating that "user" has
"n" hours of volunteer time.

What I'd like to do: Automate this so that they don't have to go through
every user and write down which ones have more than 100 hours of time logged.

What it looks like in my head: They click a button, it runs code/query/etc.
selecting from the volunteer info table, the name of the person, then running
a query using that name, totalling up the hours, and storing the name and
total hours volunteered into a temporary table. When it's done with that
volunteer, it will then automatically select the next one, and repeat the
process until it's done. Then, I can make a basic report off of the
temporary table. When they print it, and close the form, it will
automatically delete the table.

I know how to handle the last parts (making the report, deleting the table)I
just don't know how to populate the temporary table and cycle through list of
names.

Any ideas?

Thanks.

D

 

[Klatuu]

You don't even need to create temporary tables. Create a query that has the
volunteer names and the number of hours. Make the query a totals query.
Select Group By for the name and Sum for the hours. Your query will then
return one row for each volunteer with the total hours. Base your report on
this query.

 

[Marshall Barton]

Hi, I wasn't sure what to search for, so I decided to make a new post.
Search ideas are welcome as well as solutions.

Here's what I'm trying to do...Made a database for a local non-profit
organization, and they asked for a bit of an update. quick explanation of
the database:keeps track of volunteer information including hours volunteered
at that location as well as several of their partners. What they want added
is the ability to print a report listing everyone with more than say 100
hours of volunteer time.

What I currently have working - they bring up a form that allows them to
select one of their volunteers from the list, it then goes through and
calculates their total hours (via use of a query for records matching that
individual's name, then totalling all the hours in each record to return
their total time). It will then display a message box stating that "user" has
"n" hours of volunteer time.

What I'd like to do: Automate this so that they don't have to go through
every user and write down which ones have more than 100 hours of time logged.

What it looks like in my head: They click a button, it runs code/query/etc.
selecting from the volunteer info table, the name of the person, then running
a query using that name, totalling up the hours, and storing the name and
total hours volunteered into a temporary table. When it's done with that
volunteer, it will then automatically select the next one, and repeat the
process until it's done. Then, I can make a basic report off of the
temporary table. When they print it, and close the form, it will
automatically delete the table.

I know how to handle the last parts (making the report, deleting the table)I
just don't know how to populate the temporary table and cycle through list of
names.

Generally the key to a report is to create a query to
organize the data for the report. Most of the time, the
report then becomes very simple to create.

In this case, it sounds like you just want a Totals type
query:

SELECT personname,
personaddress,
. . .,
Sum(hoursworked) As TotalHours
FROM thetable
GROUP BY personname,
personaddress,
. . .
HAVING Sum(hoursworked) >= 100

If that kind of query gathers the correct data, then just
use it as the report's RecordSource, no need for a temp
table or all that other fooling around.

 

[Mr. D]

Ugh!!! Why didn't I think of that in the first place!!! Thanks for the help.
I have a problem where I look too deep for something, not seeing what's
right in front of my face! Works great. Thanks again.