Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Time Sheet Dilema (2003 sp2)

    The attached database is used to track the time and attendance of employees in my department and especially to calculate remaining vacation, approved absence, personal day, and award day(s), hours. These balances are displayed in the time sheet report. It has worked fine for years except when it gets to the end of the year when vacation time for example, reaches zero. At that point the report displayed the vacation hours held at the beginning ot the year. I thought I had this fixed in qryBalanceReport3, until I ran into a new issue with Approved Absence hours, alias sick time. Most employees use their sick time during the year, but a few go for perfect attendance.

    My past expression in qrybalanceReport3 was RemA: IIf([SumOfRemA]=0,[AAHrs],[SumOfRemA]) which worked for the perfect attenders but not the users. My new expression RA: IIf([RemA]=[AAHrs] And Format(Date(),"mm")>9,0,[RemA]) works for the users and not the perfect attenders. Obviously I need something that works for both.
    Attached Files Attached Files
    Thanks
    chuck

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

    Re: Time Sheet Dilema (2003 sp2)

    The queries are rather convoluted, and you have provided attendance data for only one employee, so I have no idea where the discrepancy between expected and actual results is. Could you provide specific examples?

  3. #3
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Time Sheet Dilema (2003 sp2)

    hmmm... (In the Discription column for the queries I noted the sequence of my logic so that qryBalanceReport3, the final query, is 7b).

    Okay, the time sheet currently shows 4 for my Approved Absence hours which is correct. Open tblAttendance, filter on "A" in [Code] and change all [Ohrs] to 0. Then open frmSelectEmployee, choose Charles Rau, then click the Open Time Sheet button, you will see for Approved Absence I have 0, when I should have 48. If I use sick time and place hours in the [OHrs] my query expression calculates as expected. If I don't use any sick time my expression returns 0 rather than 48 after September.

    On a day I work [Code] would hold "S". (I removed all those to shrink the database size). My queries need [Code] to hold an "A" for example, for the query to calculate anything. In the beginning of the year if an employee attended a full two weeks the time sheet would show 0 for Approved Absence hours rather than the actual hours avalable which would be 48.

    At the beginning of the year I need the report to show all available time, when time is used, remaining time, and when time is exhausted, 0.
    Thanks
    chuck

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

    Re: Time Sheet Dilema (2003 sp2)

    The expression for RA makes no sense to me.
    Can you explain with specific examples why RemA doesn't do what you want?

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

    Re: Time Sheet Dilema (2003 sp2)

    What if you use SumOfRemA itself instead of an expression based on it?

  6. #6
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Time Sheet Dilema (2003 sp2)

    RemA in qryBalanceReport2 returns 48 if all [OHrs] =0. That's good if this were the beginning of the year, (no time used) or the end of year for a perfect attender. But if [OHrs] totaled 48, (time exhausted) RemA in qryBalanceReport2 would return 48 rather than 0.

    RA is my attempt at fixing the end of year scenerio. Most sick time users will have used time by September so I stuck that in there thinking RemA would not hold 48 (which equals hours alloted each year) unless the time was exhausted. But of course it fails to consider the perfect attender whose RemA holds 48 and time is not exhusted.
    Thanks
    chuck

  7. #7
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Time Sheet Dilema (2003 sp2)

    I have attached an image to show the results with SumOfRemA used in qryBalanceReport3.

    RA shows the correct balances.

    I didn't realize a pasted image shows after you post. The attachment can be removed.

    I probably is not fair to continue this since there is no way I can give a database with the data needed to show the results. You do not need to give this anymore of your time.
    Attached Images Attached Images
    Thanks
    chuck

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

    Re: Time Sheet Dilema (2003 sp2)

    I have created a new version of qryCodeVAZPF. It's the crosstab query qryCodeVAZPF2.
    I've also created a new version of qryBalVA: qryBalVA_New.
    See the attached version; I hope this does what you want.
    (Note: I removed all forms, reports and macros from this copy)
    Attached Files Attached Files

  9. #9
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Time Sheet Dilema (2003 sp2)

    I didn't think to just give a database with tables and queries. I can give you more data now.

    Are you suggesting your queries replacing mine in sequence or as final queries?
    Attached Files Attached Files
    Thanks
    chuck

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

    Re: Time Sheet Dilema (2003 sp2)

    In the attached version, I have added calculations for P, F and Z to the query qryBalanceReport_New; this query can be used as record source for the subreport (you'll have to change some of the control sources in the subreport to match the field names in the query). qryBalanceReport_New is based on qryCodeVAZPF2 and qrySumZDay; it replaces most of the other queries.
    Attached Files Attached Files

  11. #11
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Time Sheet Dilema (2003 sp2)

    Well that worked beautifully. I have seen crosstab queries but admit I do not understand them. In this case it made all the difference.

    You are always an education and much appreciated.
    Thanks
    chuck

Posting Permissions

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