• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

Reports

My report is set up from a relationship table and the below report is how it looks.

I want a calculation  to add the total staff sick for the month of March as listed below but the total calclated is incorrect.  It totals (24) when the total is only (16).  Can you help?



Absenteeism Report

      For Services Completed Between: 03/01/00 and 03/31/00

      Department      Delaporte Radio
      Emp Name      Yuri Hepburn
      Emp No      5099
      Cost Center Code      109185
      Type of Request      Vacation
      Date Requested      Start Date      End Date      Total Days
       4/ 4/00       4/10/00       4/12/00      3

                   Department      Earth Station
      Emp Name      Kendal King
      Emp No      10197
      Cost Center Code      109179

      Type of Request      Sick
      Date Requested      Start Date      End Date      Total Days
       3/17/00       3/17/00      1

      Department      Radio Transmission
      Emp Name      Antione Smith
      Emp No      6204
      Cost Center Code      109184

      Type of Request      Vacation
      Date Requested      Start Date      End Date      Total Days
       4/ 3/00       4/26/00       4/27/00      2

      Monday, April 10, 2000      Page 1 of 5

       Emp Name      Byron Toote
      Emp No      6206
      Cost Center Code      109184

      Type of Request      Sick
      Date Requested      Start Date      End Date      Total Days
       3/31/00       3/31/00      1
       3/ 7/00       3/ 8/00      2

      Type of Request      Vacation
      Date Requested      Start Date      End Date      Total Days
       3/27/00       4/ 7/00       4/10/00      2

      Emp Name      Carlisa Curtis
      Emp No      6205
      Cost Center Code      109184

      Type of Request      Vacation
      Date Requested      Start Date      End Date      Total Days
       4/20/00       4/24/00      3

      Emp Name      George Swann
      Emp No      10210
      Cost Center Code      109184

      Type of Request      Sick
      Date Requested      Start Date      End Date      Total Days
       3/31/00       3/31/00      1

      Emp Name      Hillary Bethel
      Emp No      8427
      Cost Center Code      109184

      Type of Request      Vacation
      Date Requested      Start Date      End Date      Total Days
       4/ 4/00       4/25/00       4/28/00      4

      Monday, April 10, 2000      Page 2 of 5

       Emp Name      Jeffrey Walcott
      Emp No      10514
      Cost Center Code      109184

      Type of Request      Sick
                    Date Requested      Start Date      End Date      Total Days
       3/24/00       3/24/00      1

      Type of Request      Vacation
      Date Requested      Start Date      End Date      Total Days
       3/29/00       4/19/00       4/25/00      3

      Emp Name      Marion Smith
      Emp No      2154
      Cost Center Code      109184

      Type of Request      Vacation
      Date Requested      Start Date      End Date      Total Days
       4/ 3/00       4/19/00       4/28/00      6

      Emp Name      Ormond Thurston
      Emp No      8606
      Cost Center Code      109184

      Type of Request      Paid Leave
      Date Requested      Start Date      End Date      Total Days
       3/ 3/00       3/ 6/00      2
       3/31/00       3/31/00      1

      Monday, April 10, 2000      Page 3 of 5

       Emp Name      Simeon Rolle
      Emp No      10140
      Cost Center Code      109184

      Type of Request
      Date Requested      Start Date      End Date      Total Days
      0
      Type of Request      Sick
      Date Requested      Start Date      End Date      Total Days
       3/24/00       3/24/00      1

      Type of Request      Vacation
      Date Requested      Start Date      End Date      Total Days
       4/ 3/00       4/ 5/00       4/ 7/00      3
      
                   Department      Riggers Workshop
                   Emp Name      Anthony Whylly
      Emp No      8022
      Cost Center Code      109187

      Type of Request      VACATION
      Date Requested      Start Date      End Date      Total Days
       3/15/00       3/16/00      2

      Emp Name      Brian Brown
      Emp No      8006
      Cost Center Code      109187

      Type of Request      SICK
      Date Requested      Start Date      End Date      Total Days
       3/16/00       3/17/00      2

      Monday, April 10, 2000      Page 4 of 5

       Emp Name      Hasting Rollins
      Emp No      8018
      Cost Center Code      109187

      Type of Request
      Date Requested      Start Date      End Date      Total Days

      Type of Request      VACATION
      Date Requested      Start Date      End Date      Total Days
       3/ 2/00       3/ 2/00      1

      Emp Name      Olrick Sweeting
      Emp No      8002
      Cost Center Code      109187

      Type of Request      Vacation
      Date Requested      Start Date      End Date      Total Days
       3/ 3/00       3/ 3/00      1
       4/ 3/00       4/ 4/00       4/ 4/00      1

      Emp Name      Trevor Stubbs
      Emp No      8628
      Cost Center Code      109187
      Type of Request      SICK
      Date Requested      Start Date      End Date      Total Days
       3/ 3/00       3/ 3/00      1

Total Non-Porductive Days      44
Total Non-Productive Staff      24

      Monday, April 10, 2000
0
Ria
Asked:
Ria
1 Solution
 
BelieverCommented:
It's kind of hard to tell from this layout how your data source (query) and report are arranged.  You should start by looking at the data in the query.  Chances are that although the report shows a single record for an employee, the query is probably returning multiple records.  When the sum is being done, one sick time entry for one person is showing up more than once and giving you incorrect results.
0
 
RiaAuthor Commented:
You are correct, that is exactly what is happening.

Could this problem be corrected?

Does it have something to do with my relationship?
0
 
PhoatCommented:
you need to consolidate the query to return only unique values.  this could be a problem with your query relationship.  It might need to be defined as a left or right join.  Im not sure tho since i don't know the query.  It could also be a simple matter of grouping the unique fields from the query result.  Again im not sure tho.

Phoat
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MikeRenzCommented:
I think the best way to solve this is to add a grouping to your report...on the "Emp No"

This will group the employees together.  Also add a textbox to the group_header for this grouping, with a Name of "txtEmpNoSum", control source of "=1", and a Sum of "Over All".  Make this field's visible property to "false"

In the report_footer add a textbox to show the sum...set the control source to "=[txtEmpNoSum]".

This should fix your summation.
0
 
RiaAuthor Commented:
Your sugestion started to work but the text
box in the Report Footer is not calculating.
Before the report opens I am getting an
Parameter Box (txtEmpNoSum).

0
 
RiaAuthor Commented:
Hi, Can I please get a response :-)
0
 
RiaAuthor Commented:
My query consist of all the fields in my report
and is linked from a one to many relationship of two tables.  
0
 
MikeRenzCommented:
for the textbox in the footer, make sure you have the control source as =[txtEmpNoSum]   (WITH the brackets)


0
 
RiaAuthor Commented:
Thanks, but it still doesn't work.  I know I am doing everything right that you stated.
Could there be a problem with my query.
0
 
MikeRenzCommented:
did you create the txtEmpNoSum textbox as stated above?  If so...make it visible to see it if it totaling correctly.
0
 
RiaAuthor Commented:
Yes I did create the textEmpNoSum.  It totals correctly for the Group Footer but is does not calculate for the overall for the Report Footer.  After entering my beginning date and ending date in the parameter value box I then get another prompt to enter "txtEmpNoSum"
0
 
MikeRenzCommented:
you can't have a sum of a sum.  You need a second textbox in your detail section that has a Summing for OVER ALL, where the other one will be OVER GROUP.  So make another textbox called txtEmpNoSumAll, control source '=1', summing 'over all'.  And then the same way you have your group footer textbox, make a textbox in the report footer with the control source of '=[txtEmpNoSumAll]'
0
 
RiaAuthor Commented:
Mike, I am trying your comment but my datebase still does not calculate what I want.

I guess I won't be able to  perform my calculation.
0
 
RiaAuthor Commented:
Adjusted points from 75 to 120
0
 
RiaAuthor Commented:
Can anyone help or should I give up?
0
 
MikeRenzCommented:
ria,
  if you can email me the database (or at least a scaled down version of the database with just the report and tables needed, then I can try to fix it for you:
 
    msr136@psu.edu
0
 
RiaAuthor Commented:
Hi Mike

I emailed a version of the database to you.  If you are emailing a sample back could you kindly email it to sweetlips@batelnet.bs

Thanks.
0
 
MikeRenzCommented:
hi ria,
  I got your email, and am looking at it.  What exactly are you trying to sum?  Just the days sick?  What are the 4 different summaries for?  Was this just you trying to get it to work?
0
 
RiaAuthor Commented:
Yes, I was trying to calculate the total Staff sick for the month.

When I use =Sum([Emp Name]) it totals all the transactions that the employee have.

It is adding the detail information.  For isntance of a staff has 3 rows of sick days on different times entered, the total staff counted wound be three instead of one person.
0
 
RiaAuthor Commented:
When I enter dates for the month of March 1 - March 31, 2000 and use the espression =Count([Emp Name]) I get the total 12 when there are only nine employees for the month of March.
0
 
RiaAuthor Commented:
Thanks Mike for taking extra initiative to help me with my database report calculation.  I re-entered the sample in another report and it worked perfectly.

Thanks agains.....for helping to make my job reports much easier.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now