Displaying Sum of Field Totals in a Report (1 Viewer)

nicolaasjan2005 · Feb 27, 2005

I have a table that contains 50 fields. Each field represents an employee. The employees hours are recorded each day that they work.

I need to generate a report that, at the end of the month, will generate the total number of hours, worked by all employees.

I have a query set up using the date as start and end parameters but no matter what I try, whether in the Report or Form, when I try to perform a calculation - it remains a blank field. The label shows up - but no field contents.

Example - empl1 +empl2+ ... +empl50 = Total for all employees.

steve711 · Feb 27, 2005

Try something like this for the total you want.

Create a text box and in the Control Source type in

=Sum([YourEmployeeHours])

nicolaasjan2005 · Feb 27, 2005

Hi Steve711:

Just tried it, and it too shows the label but the calculated field is a blank.

nicolaasjan2005 · Feb 27, 2005

To be more specific... what I tried was

=SUM([EMPL1]+[EMPL2]+[EMPL3]) and so on, but it still draws a blank.

RichO · Feb 28, 2005

Please clarify... Are there 50 fields (EMPL1 through EMPL50) or 50 records, one for each employee, that you are referring to?

nicolaasjan2005 · Feb 28, 2005

There are 50 fields - [Empl1] thru [Empl50]. Each day is a new record.

So, what I'd like to be able to do is generate a report that can show the total hours worked for any given day.

Then I'd like to be able to extrapolate that to show a total of hours worked in any given period of time - using the "start Date" and "End Date" parameters in a query.

RichO · Feb 28, 2005

Well, the first thing most anyone would tell you here, is that your database lacks normalization, but that's another thread...

Being that you have come this far with your current format, to total the hours of the 50 fields in a report...

Add a textbox to the report's detail section and name it "txtTotal". Click on the detail section outside the textbox and then set up an [Event Procedure] for On Format:

Code:

Dim i As Integer
Dim tot as Integer
For i = 1 To 50
tot = tot + Me("EMPL" & i)
Next i
Me.txtTotal = tot

This textbox will display the sum of the 50 employees' total hours per record.

nicolaasjan2005 · Feb 28, 2005

Thanks a million! But... as you alluded to.. how could this have been set up better?

I'm very new to Access and what I know is self-taught.

RichO · Feb 28, 2005

As an example, there should be one table set up that just contains employee info, using their SS# as a primary key field [EmployeeID]. Then if you wanted a table just to keep track of the hours per day, per employee, you could set up another table with the fields [EmployeeID], [WorkDate], [HoursWorked], linking the 2 tables using the ID field.

With those 2 tables you can accomplish the same hour calculations without having to work with 50 fields.

That must have been a tedious task to set up a table with 50 fields.

nicolaasjan2005 · Feb 28, 2005

That makes perfect sense. I may do some modifications. Thanks for the heads up! Maybe eventually I'll get the hang of this relational database after all.

I'm used to working with the MS Works flat database but I see the far greater opportunities a relational database has to offer.

Thanks for the help! I'm sure I'll be back!

Displaying Sum of Field Totals in a Report (1 Viewer)

nicolaasjan2005

Registered User.

steve711

Registered User.

nicolaasjan2005

Registered User.

nicolaasjan2005

Registered User.

RichO

Registered Yoozer

nicolaasjan2005

Registered User.

RichO

Registered Yoozer

nicolaasjan2005

Registered User.

RichO

Registered Yoozer

nicolaasjan2005

Registered User.

Similar threads

Users who are viewing this thread