Using form query in report

[kronar]

I am using a query in a subform and want to us it in a subreport. table is multiple time sheets by date, subform and subreport show time accumulated by emplyee. So each time is hours summed per employee. In the report footer I want to sum all employees hours into one total. It works in the sub form but not the subreport? Any tips?
Querry used is:
SELECT J.JB_JobBidNo, J.JBT_EmpNum, E.EM_FirstName, E.EM_LastName, Sum(J.JBT_WorkHrs) AS HbE, E.EM_Wage, (Sum(J.JBT_WorkHrs)*E.EM_Wage) AS Tot
FROM EM_Emp AS E INNER JOIN JBT_EmpTime AS J ON E.EM_EmpNumber = J.JBT_EmpNum
WHERE J.JB_JobBidNo=[Forms]![frmMenu]![txtJobNum]
GROUP BY J.JB_JobBidNo, J.JBT_EmpNum, E.EM_FirstName, E.EM_LastName, E.EM_Wage
ORDER BY J.JBT_EmpNum;
The total is a text field with source of Sum(HbE), i get error? instead of the total total.

 

[dhookom]

If I understand, you could create a single record totals query like:

SELECT J.JB_JobBidNo, Sum(J.JBT_WorkHrs) AS HbE
FROM JBT_EmpTime AS J
WHERE J.JB_JobBidNo=[Forms]![frmMenu]![txtJobNum]
GROUP BY J.JB_JobBidNo;

Add this query to your report's record source query and bind a text box in the report footer to HbE.



Duane
Hook'D on Access
MS Access MVP

 

[kronar]

Thanks, ithought I remembered that Access had trouble with doing a SUM on another agregate function. How bought if I tried using Sum of the J.JBT_WorkHours in the text box in the footer. It should only pull from the same selected records wouldn't it. Or would it have to be in the original query?

 

[dhookom]

I thought you wanted your main report to display sum of values from a subreport. If the JBT_WorkHours is in your main report record source then you shouldn't have any trouble summing it in your main report.

Duane
Hook'D on Access
MS Access MVP

 

[kronar]

The main report is based on the JBT_Jobs table. On this tab I am displaying the employee time by employee number. So in the footer I want to sum all the employee time for this Job. Basically a sum of the summed employee times, for this particular Job number.

 

[dhookom]

Did you just try what I suggested in my first reply?

Duane
Hook'D on Access
MS Access MVP

 

[kronar]

Each report is only one job number. Each employee's time needs multiplied by that employee's rate, then want a sum of all the labor costs for this job.

 

[dhookom]

Your original request was for "I want to sum all employees hours into one total" hence you now need to change my totals query to include the rate.

Then do as I suggested.

Duane
Hook'D on Access
MS Access MVP

 

[kronar]

So this would be a seperate query from the one that builds the subreport? Where would I enter it?

 

[dhookom]

It's a new query that you build from in the database window->queries.
You new query might look like:

SELECT J.JB_JobBidNo, Sum(J.JBT_WorkHrs*E.EM_Wage) AS OneTotal
FROM EM_Emp AS E INNER JOIN JBT_EmpTime AS J ON E.EM_EmpNumber = J.JBT_EmpNum
WHERE J.JB_JobBidNo=[Forms]![frmMenu]![txtJobNum]
GROUP BY J.JB_JobBidNo;

Duane
Hook'D on Access
MS Access MVP

 

[kronar]

So would I call this query upon formLoad of subreport, or just reference Sum([OneTotal]) in a new text box?

 

[kronar]

Question #2: Why does this work in a subform but not in a subreport?

 

[dhookom]

Did you create the query and save it? Did you then add it to your main report's record source? Did you then add a text box in your report bound to the field [OneTotal]?

You shouldn't need to sum this field since the value displayed in the query should already be the value you want in your report.

Duane
Hook'D on Access
MS Access MVP

 

[kronar]

So you are saying that I can't get this total of hrs and labor cost from the existing query that the subform/subreport is based on, even though it works in the subform?

 

[dhookom]

You can have a total in a subreport footer and reference it from the main report. Assuming the following:
Subreport Control Name: srptMySubCtl
Control Name in subreport: txtMyTotal
Your main report would have a control in the same section as the subreport with a control source of:
=srptMySubCtl.Report.txtMyTotal

Duane
Hook'D on Access
MS Access MVP

 

[kronar]

OK, but why do I always get an error instead of total in the footer where source is "=Sum([HbE])

 

[dhookom]

Can you provide anything else about your text box or so I need to list all of the possible causes for errors in text boxes that I can think of?

Is the text box in the subreport?
Is the [HbE] a field in the report's record source?
Is the [HbE] a numeric field?
Is the name of the text box something other than the name of a field?
What section of the report contains the text box?

Duane
Hook'D on Access
MS Access MVP

 

[kronar]

Text box is in subreport
HbE is name of the Summed field in the query that the subreport is based on
HbE displays as numeric field summed for each employee in the subreport
Text box is named TotalHrs and is sum(HbE)
Text box is in the report footer (which doesn't normally display)
This works fine in the subform??

 

[PHV]

Text box is in [!]subreport[/!]
field in the query that the [!]subreport[/!] is based on
Text box is in the [!]report[/!] footer

So, the text box in the subreport as a control source like: =Sum([HbE])
and the text box in the report references the above control ?

 

[kronar]

In JobReport there is subreport timesubreport
timesubreport source is timequery
timequery sums time by employee field called HbE
in footer of subreport Field TotalHrs source is
=Sum([Hbe]) this is where I keep getting #error when I run timesubreport by itself