Query help

[Guest]

I need help! Please bear with me so that I can explain what it is I'm trying
to accomplish.

I'm using queries to create time sheets for staff by tallying the total
amount of time spent working in each of several possible programs within
several different counties. For example, worker A spends 25.25 hours for
Program XYZ in county 1, 12.75 hours for Program ABC in county 2, etc. All
time is counted except Admin time. Pretty straightforward so far.
Then I need to calculate how much of their total time was spent in each
county and program in terms of percentages, then take that percentage and
calculate the amount of Admin time to be added to the totals. This way,
Admin time is spread across counties and programs according to the amount of
time the worker spent there. So, if Staff A has 1 hour of admin time and
spent 30% of his time in county 1 for program XYZ, 30% of his total Admin
time would be added to that total.

I've had to use several queries to get the results I want, and everything is
working perfectly. Almost. The trouble I'm having is for staff who had
admin time for a particular county and program, but no other type of service.
Since the staff persons name shows up in one query, but not in the other,
when I join the two to add the admin time back into the totals, those staff
who are not in both queries are not showing up. How can I get those people
to show? I can post query structure if needed.

Sorry this is so long....Thanks for any help!

Tara

 

[Guest]

If this query is being used as the record source for a report, I would
suggest you do the math in the report. It is easier and will run faster.

If it is for a form, the same applies.

If it is an update or append query, you have a couple of options.

Use public functions in the query to calculate the values
or
Use recordset processing

 

[Guest]

Thanks for the response. As for this query being a record source for a
report, it's not. It's one in a series of queries that ultimately produce a
final query that is used for a report. I agree that the calculations might
be better in the report, and I'm sure I could cut a lot of the queries I've
used out by doing them in the report. However at this point, I'd rather
accomplish it with queries. I'll definately look into the possibility of
reworking this report in the future though. I'm not familiar with Public
functions and Recordset processing, at least not with the terminology as it
might apply to this situation. Could you clarify what you mean and how they
might help me accomplish my goal?

Thanks!

Tara

 

[Guest]

Recordset processing would not really be the way to do what you want since it
is for a report.
If you are not familiar with public functions, you are probably not familiar
with VBA coding, so that option may not work for you.

I understand your desire to accomplish it with queries; however, you will
find that performing the math operations in the report is much faster than
using queries. That is because of the number of times and the amount of data
that has to be moved accross the network.

Your best option is to do the calculations in the report.