DSum and DCount ?

[indyaries]

Greetings,

I am using Access 97 SR2. I want to take an unbound form and create summary information using unbound text boxes for calculations. I'm thinking I need to use DSum and DCount.

I have 24 work organizations that I'm required to track their total work hours and total overtime hours by month.

The important fields are:
ofc_org_cd = Organization
type_hr_cd = Hour Code
qty_mtd_prod = Month-To-Date Productive Hours
qty_ytd_prod = Year-To-Date Productive Hours

To count the records by Organization I used:=DCount("[qty_mtd_prod]","t_DBH","[Ofc_Org_Cd]Like'ISE'")
To sum the MTD hours I used: =DSum("[qty_mtd_prod]","t_DBH","[Ofc_Org_Cd]Like'ISE'")
I did the same for YTD sum.

QUESTIONS:

1. How can I extract the overtime hours by Organization? I'm guessing it would look something like this:
=DSum("[qty_mtd_nonprod]","t_DBH","[Ofc_Org_Cd]Like'ISE'And[Type_Hr_Cd]Like'O*'")
Note that all Overtime Codes begin with the letter O.
However, this does not work. In this example, it should return 219 OT hours.

2. Once I can get the OT hours, I also need to arrive at the percentage of OT used by Organization. Using the Organization ISE as an example:
In Oct 2002 the total hours worked was 2102.75
The total overtime hours for ISE in Oct was 219.0
The percentage of OT hours for the hours worked in ISE is 10.41%
The overall percentage of OT hours for ISE with the total overtime hours for all organizations is 11.7% (1864.5 total OT hours divided by 219.0 ISE overtime hours).

Any and all assistance is appreciated.

Regards,

Bob in Indy

 

[Fizzio]

Is there no way you can perform the same calculation in a query and bind this to the form?

The alternative is to create a query just to perform the calculations and use the aggregate functions on that.

 

[indyaries]

Hmmmm.....

Perhaps using a calculated text box in a report..???

I've created a report grouped on Organization Code.

...............Type.........................Tot...........OT..........Percent
Org.......... Hr Cd....... Hrs...........Hrs.......... Hrs.........OT
IIEB......... LH............0.00
IIEB......... ND............0.00
IIEB..........OS...........24.25
IIEB..........OZ...........0.00
IIEB..........RG...........144.00......168.25.....24.25.....14.41%

The total for Overtime Hours is the sum of all Hrs with a Type Hr Cd beginning with the letter O. The above is how this would look in Excel.

I would like to do this in Access.

Thanks again !!

 

[Rich]

Use a crosstab query