• Time Sheet Dilema (2003 sp2)

    Author
    Topic
    #447241

    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.

    Viewing 0 reply threads
    Author
    Replies
    • #1089806

      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?

      • #1089810

        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.

        • #1089811

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

          • #1089812

            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.

            • #1089815

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

            • #1089819

              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.

            • #1089821

              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)

            • #1089823

              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?

            • #1089830

              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.

            • #1089885

              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.

    Viewing 0 reply threads
    Reply To: Time Sheet Dilema (2003 sp2)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: