Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating A Report (2003)

    I have a database that tracks sick time, vacation time, banked time. I want to create reports that provide information by employee, and department by month. I've been trying to use the report wizard, but my totals are not working. I need totals for sick time reserve, sick days used and new reserve by employee and by department for each month Jan-Mar and by month going forward. I've tried to use the report wizard, but the totals are not making sense.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating A Report (2003)

    We'd need to have some info about the structure of the table(s) you're using. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  3. #3
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating A Report (2003)

    Sorry I'm having trouble attaching the file...

  4. #4
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating A Report (2003)

    One more try....
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating A Report (2003)

    1) I don't understand your table design. The field Department occurs in EmployeeSetuptbl, CostCentertbl and in AbsenceIinputtbl, and of course in Departmenttbl. The ID field in Departmenttbl is linked to Department in CostCentertbl and in AbsenceInputtbl, but Department in AbsenceInputtbl is also linked to ID (not to Department) in CostCentertbl.

    2) The report is based on Absencebydate Query but there is no query of that name. There is a query named AbsencebydateQuery but that is based among others on a table named Department but that doesn't exist either.

  6. #6
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating A Report (2003)

    I obviously deleted the wrong things trying to reduce the size of the file. I'll try again.

  7. #7
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating A Report (2003)

    One more try.
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating A Report (2003)

    OK, I can open the report now, but I don't see an immediate problem with the totals. Can you explain? Thanks.

  9. #9
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating A Report (2003)

    If you look at page 2 (and I apologize for the cosmetics of the report--I want to get it working before I massage it) at employee number 20470. This employee had 2 sick days entries with one day off for each entry. I want to create a running total where each sick day is deducted from the available days and the total for employee, department and month reflects this declining balance.
    What I'm getting is the available days are not declining as sick days are taken and the totals are wrong. In the case of employee 20470 she has 4 days available and because she has two entries the total is showing 8 days. I know that the answer lies in the query, but I don't know how to fix it.

    Thanks

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating A Report (2003)

    Reports have a very useful feature: running sums. You can set the Running Sum property of a text box to Over All (the value in the Control Source will be accumulated over all records) or to Over Groups (the running sum will be reset to 0 at the start of each new group). So you can have a text box bound to Days Absent and set the Running Sum to Over Groups. This will cumulate the days for each employee. You can subtract this from the available days to get the number of remaining days.

    I have attached an example. I had to remove some controls from the report ti make room, so don't just copy the report. Moreover, it is not complete since I don't understand it completely, but I hope you can use it as a starting point.
    Attached Files Attached Files

  11. #11
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating A Report (2003)

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •