Sub-Report Caculation question

[Rob]

I'm sure I understand what you are after, but how does
this sound:

Create a new query to subtotal the number of hours per
employee.

Add this query as a data source into your existing query,
linking by employee.

Include the "total number of hours per employee" field in
your query output.

In the report's detail section, subtract the "hours for
this organization" field from the "total hours per
employee".


Am I understanding what you are looking for?

Rob

-----Original Message-----
Here's my setup. My report has a several sections. The

first visible section is a group header sorted by EmpName
which reports info about an Employee. That section has a
sub report to display a list of Organizations that
employee is affiliated with and the number of hours spent
monthy on each Org, and a total in the subreport's footer
showning number of Orgs and total of hours. That works
fine.

The detail section shows more info about each Org,

including hours spent on that Org only. I need to also
show (in the detail section) the number of ours spent on
OTHER orgs - - the sum of all orgs for this employee, less
hours for this Org. Problem is i keep getting totals for
ALL orgs less this org. Any advice? TIA

 

[Guest]

Rob
Thanks for the reply. Yes, I think you do understand what I want. Your solution sounds right on, but i'm not sure how to link 2 queries. Can you walk me through that? I'm fairly new and this water's pretty deep. TIA

 

[Rob]

Gary,

This is one of those times when it would be so much easier
to show you than to try to explain it, but here goes...

From the beginning: When you create or edit a query in
the Query By Example (QBE) editor, you place data sources
(i.e. tables) in the top panel and specify your output
columns in the bottom panel. (I'm guessing you know this
much already.)

The trick is, the data sources you include are not limited
to just tables - you can put a saved query in there, too.
From there, you can pretty much treat it like any other
table, as far as using fields from on table to link to
another.

In a nut shell, Access runs the save query first, stores
the data in a temp table somewhere, and then uses it just
like a regular table to complete the rest of new query
request.

There's really nothing magical about it, but it's not
exactly intuitive to the uninitiated.

Let me know if didn't explain that well enough - this is
one of those things that looks much clearer after you've
done it once or twice.

Rob

-----Original Message-----
Rob,
Thanks for the reply. Yes, I think you do understand

what I want. Your solution sounds right on, but i'm not
sure how to link 2 queries. Can you walk me through
that? I'm fairly new and this water's pretty deep. TIA

 

[Rob]

The parentheses are used just like in math, and the
brackets are used to denote the names of the fields in the
query (just like we use quotes to denote text strings with
starting and ending quotes).

Assuming you let the query name the sum field, the Control
Source for the textbox should be:

=[Sum Of OrgHrs]-[OrgHrs]

I've seen some strange things in the past where textbox
formulas would not work until controls for all of the
fields referenced in the formula have been placed on the
form, at which point these unneeded controls could be
deleted and the formula would continue to work fine. Try
that if all else fails.

Sounds like you are on the right track, though. Good job!

Rob

-----Original Message-----
Rob,
Thanks. You're explaining well. After a few false

starts, I have succeeded in created the new query, linked
it into the report query, and that query now shows total
hours per employee correctly. YEAH! Now, on my report I
created an unbound text box, but am unsure how to phrase
the source criteria. Should it be like =([Sum of OrgHrs])-
[OrgHrs]? Does that sound right? I get confused by the
brackets vs. parenthesis thing. TIA

 

[Guest]

Rob
Thanks for the encouragement. But, that control source formula only returns zeros. I'll try adding all fields to the report and making the unneeded ones invisible. Thanks.

 

[Rob]

Hum, if you are getting zeros, I'm guessing your Sum Of
OrgHours and OrgHours fields are the same?

If so, then something is likely wrong with the query that
is supposed to be subtotalling OrgHours values for
everybody.

Go back and run that query by itself and make sure it is
giving you ONE LINE PER INDIVIDUAL - it should have only
two columns output, the individual and the Sum Of Org
Hours. You will likely have to be doing something with it
to limit the date range, but make sure the Total: value
for that column is set to "Where" and not "Group By". The
output should look like this:

Person SumOf
Bob 5
Sam 2

Then go back to your second query and make sure you have
the tables linked correctly. After the query runs, you
should see the a single Sum Of OrgHours value for each
record related to an individual. It should look like this:

Person Org OrgHours SumOf
Bob Org1 2 5
Bob Org2 3 5
Sam Org1 1 2
Sam Org2 1 2


Is this what is happening?

Rob

-----Original Message-----
Rob,
Thanks for the encouragement. But, that control source

formula only returns zeros. I'll try adding all fields to
the report and making the unneeded ones invisible. Thanks.

 

[Guest]

Rob
I ran the OrgHrsSum query by itself, and it appears as you said it should. But the Total:value under EmpName is set to "Group By". When I change it to "Where", as you suggest, my result is only one line with the total of ALL hours. (?!?

I ran the report query by itself, and it apears as you said it should (along with some other fields)

And I checked the join within the query. It is set to option 1. Trying option 3 gets the same (unwanted) results. Trying option 2 stops me and tells me I have ambiguous outer joins. What am I missing? TI

 

[Guest]

See below.

-----Original Message-----
Rob,
I ran the OrgHrsSum query by itself, and it appears as

you said it should. But the Total:value under EmpName is
set to "Group By". When I change it to "Where", as you
suggest, my result is only one line with the total of ALL
hours. (?!?)

The "Where" option should only apply to a mechanism to
limit the date range of the data, if you have such a thing
in place. The Employee should be set to Group By, the
OrgHours should be set to Sum, and the date limiter or any
other criteria columns you use should be set to "Where".
You can use just about anything to limit the records that
are totaled, but a date range is just the most likely
means. If your main query is doing some limiting on the
date range, that same limit has to be defined within this
query, too. It won't happen automatically.

The key point is you need a list of employees and the
subtotal of OrgHours for each, restricted to the
particular range (date or otherwise) of interest.

I ran the report query by itself, and it apears as you

said it should (along with some other fields).

Good.


And I checked the join within the query. It is set to

option 1. Trying option 3 gets the same (unwanted)
results. Trying option 2 stops me and tells me I have
ambiguous outer joins. What am I missing? TIA

If the query result set was returning the data set you
needed (wherein the SumOfOrgHours did not match OrgHours
unless there was only one Org to report), then there was
no need to mess with the join type. It was doing its part
all along.

So then, why was SumOfOrgHours - OrgHours giving you 0?
That I don't know. Are you sure the formula was correct?

Rob

 

[Rob]

Gary,

Sorry for the delay - I had to go help somebody paint a
house.

Without seeing exactly what you are doing in your main
query, I can't tell you for certain that you would need to
duplicate all of your query criteria at both levels.

The first query that totals the hours needs to exclude any
organizations you don't want in a person's total and hours
outside any relevant date range.

The second query would again need to have these
restrictions, but you could also exclude an individual at
this level that you hadn't specifically excluded in the
other query - even though this person's total was
calcuated by the first query, it would be ignored if the
second query didn't return that individual's records.

For testing purposes, you could ignore all were clauses
and pull in everything. This would eliminate one possible
complication while trying to figure out what is going on
with the report.

If you would like, you can send me the .mdb file and I can
take a look at it. If it is too big or you don't feel
comfortable sending me the whole thing, copy the hours
data table, the two queries queries, and the report into
an empty .mdb and email that to me.

Send it to robschneiro at hotmail dot com. Then I can
take a look at it at see exactly what you are playing with.

Rob

-----Original Message-----
Rob,
I re-checked the formula: =[Sum Of OrgHrs] - [OrgHrs]

It still looks right to me. I also rebuilt the formula,
just in case. No difference. I'll re-check the report
query. Do I understand correctly that if the report query
uses awhere clause, I'll need to include the same field(s)
and where clause(s) in the "SumOrgHrs" query? TIA

 

[Guest]

Rob
Helping others seems to be your life's call. Don't worry about the delay. I SOLVED IT!!
I ended up creating a calculation field within the query to calculate SumOrgHrs minus EmpOrgHours and Voila
Thanks for all your help. I learned something new!