Solved

Reports

Posted on 2000-04-10
21
213 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

867 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

16 Experts available now in Live!

Get 1:1 Help Now