Solved

Reports

Posted on 2000-04-10
21
212 Views
Last Modified: 2006-11-17
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
Comment
Question by:Ria
21 Comments
 
LVL 7

Expert Comment

by:Believer
ID: 2701621
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
 

Author Comment

by:Ria
ID: 2701696
You are correct, that is exactly what is happening.

Could this problem be corrected?

Does it have something to do with my relationship?
0
 
LVL 1

Expert Comment

by:Phoat
ID: 2701950
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
 
LVL 3

Expert Comment

by:MikeRenz
ID: 2702913
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
 

Author Comment

by:Ria
ID: 2704412
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
 

Author Comment

by:Ria
ID: 2708650
Hi, Can I please get a response :-)
0
 

Author Comment

by:Ria
ID: 2708669
My query consist of all the fields in my report
and is linked from a one to many relationship of two tables.  
0
 
LVL 3

Expert Comment

by:MikeRenz
ID: 2708892
for the textbox in the footer, make sure you have the control source as =[txtEmpNoSum]   (WITH the brackets)


0
 

Author Comment

by:Ria
ID: 2709083
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
 
LVL 3

Expert Comment

by:MikeRenz
ID: 2709437
did you create the txtEmpNoSum textbox as stated above?  If so...make it visible to see it if it totaling correctly.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:Ria
ID: 2712622
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
 
LVL 3

Expert Comment

by:MikeRenz
ID: 2712719
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
 

Author Comment

by:Ria
ID: 2723175
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
 

Author Comment

by:Ria
ID: 2735321
Adjusted points from 75 to 120
0
 

Author Comment

by:Ria
ID: 2735322
Can anyone help or should I give up?
0
 
LVL 3

Expert Comment

by:MikeRenz
ID: 2735637
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
 

Author Comment

by:Ria
ID: 2751238
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
 
LVL 3

Accepted Solution

by:
MikeRenz earned 120 total points
ID: 2751385
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
 

Author Comment

by:Ria
ID: 2752298
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
 

Author Comment

by:Ria
ID: 2752322
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
 

Author Comment

by:Ria
ID: 2761006
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now