Counting Record in a Report

[Nate via AccessMonster.com]

Hey

I have a report based on a query that prompts me for an employee number.
Upon entering an employee number, the report will display all work orders
completed by that employee along with the parts used with the work order.
I would like to display totals in the report header that will tell me how
many work orders an employee worked on, and the total number of hours spend
working on the work orders.

I would like to be able to display the number of work orders completed by
an employee. I have tried "=Count([RepairOrderId])" and "=Count(*)" but
these do not work. Each record has a unique id. Instead of displaying the
total number of actual records, it displays the total number of parts
associated with the work order. The tables are linked with a one-to-many.
(One work order with many parts). Is there a way to add the actual number
of work orders?

I am also trying to add the total hours an employee worked on all work
orders. The report currently displays the labor hours for each work order.
I tried to add the labor hours using "=Sum([Repair.LaborHours])" but it
adds the hours according to how many parts I used. For example if an
employee spent 1 hour working and used three parts, it will total the hours
at 3. It is adding the multiple part records.

Thanks for the help
Nate

 

[Marshall Barton]

I have a report based on a query that prompts me for an employee number.
Upon entering an employee number, the report will display all work orders
completed by that employee along with the parts used with the work order.
I would like to display totals in the report header that will tell me how
many work orders an employee worked on, and the total number of hours spend
working on the work orders.

I would like to be able to display the number of work orders completed by
an employee. I have tried "=Count([RepairOrderId])" and "=Count(*)" but
these do not work. Each record has a unique id. Instead of displaying the
total number of actual records, it displays the total number of parts
associated with the work order. The tables are linked with a one-to-many.
(One work order with many parts). Is there a way to add the actual number
of work orders?

I am also trying to add the total hours an employee worked on all work
orders. The report currently displays the labor hours for each work order.
I tried to add the labor hours using "=Sum([Repair.LaborHours])" but it
adds the hours according to how many parts I used. For example if an
employee spent 1 hour working and used three parts, it will total the hours
at 3. It is adding the multiple part records.

There are several ways to attack this issue. By far the
easiest is to use a subreport for the Parts data. This
removes the parts records from the main report's dataset so
your Count and Sum expression will work the way you want.

Another way is to modify the report's record source query to
include the calculations so the values are available for the
report to display. This usually makes your query rather
complicated and slow.

Yet another way is to use a RunningSum text box bound to the
LaborHours field in the group header or footer section. The
count would be accomplished by another RunningSum text box
with just =1 as its control source expression. Aside from a
slight complication in the report, this works well, but the
totals may have to be in the report footer.

 

[Nate via AccessMonster.com]

Thanks for the help.

I tried using a subreport for my parts list, but I still can't get my
totals to work.

It still counts the number of parts for all the work orders instead of each
work order as a whole. Also my labor hours are still being added according
to how many parts I have. When I try to use my =SUM for total labor hours,
the labor hours listed for each individual work order disappear.
Any suggestions??

Thanks
Nate

 

[Duane Hookom]

Remove the Parts details from the main report since this information is
being supplied by your subreport.

 

[Nate via AccessMonster.com]

I still trying figure this out.

As far as I can tell all part information is in the subreport only. The
only thing joining the main report to the subreport is the ID number that
links them.
My SUM and COUNT still do not work. I have tired placing them in the
subreport, and several places in the main report.
Any more suggestions??

Thanks
Nathan

 

[Duane Hookom]

Your main report should only contain employees and work orders. You should
have no more records than you have work orders.