Solved

Timecard Form or Report? and How?

Posted on 2004-10-29
814 Views
Last Modified: 2010-08-05
Hello All,

I'm needing a popup report/form that looks like a timecard.  Showing ClockIN-LunchOUT-LunchIN-ClockOUT data for Sunday through Saturday.

I have a weeks records in the table and a query that pulls the information.

So far I've tried making a report... but it will only show data for the dates the employee clocked in.  Meaning, if they were out on Wednesday, the report shows Sunday(if they worked) Monday(if they worked), Tuesday(if they worked), Skips Wednesday(if they didn't work), Thursday(if they worked), Friday(if they worked), Saturday(if they worked)

Another thing I didn't like is you don't have much control over the size of the report other than selecting different paper sizes.

I made the same report in a form.  This is formatted much better because I can put all the fields I need on the form.  So if they are out on Wednesday, the Wednesday ClockIN-LunchOUT-LunchIN-ClockOUT data fields should be blank.

The problem with the form is it's tied to the same query, but only shows data for the first record.

Is there a way to specify each textbox to pull it's data with a whereclause?
The textboxes for Monday should be use the data from the record where PayDate = PayPeriod-5
PayDate is the date the timeclock record was made.
PayPeriod is the date of the Saturday of the week.

I already have labels for Sunday, Monday, Tuesday, etc. that use =[PayPeriod]-6, =[PayPeriod]-5, =[PayPeriod]-4, etc.


Any ideas? or thoughts on the route I'm taking?


TIA,
Dan


0
Question by:Die-Tech
    71 Comments
     
    LVL 3

    Expert Comment

    by:cws05
    Dan,

    If you want the report to show all days of the week, Create a table with the names of the days of the week and the order of precedence ie.

    tblWeekDays:

    Day                              Order
    ----                              -------
    Monday                           1
    Tuesday                          2
    ...                                  ...
    Sunday                           7

    then, base the report on a query that includes this table along with the other information you want in the report. this table has no links to the other tables, and the field that has the persons hours worked needs to be an iif statement similar to the following: iif(tblWeekDays.Day = UrTable.Day, UrTable.Hours,) and then group the columns. This column needs to be a max... Easiest if you look at my query and adapt it to your application...

    SELECT tblWeekDays.Day, Max(IIf([tblweekdays].[day]=[tblTimecardData].[day],[tblTimecardData].[Hours])) AS HoursWorked, tblTimecardData.Employee, tblWeekDays.Order
    FROM tblWeekDays, tblTimeCardData
    GROUP BY tblWeekDays.Day, tblTimecardData.Employee, tblWeekDays.Order
    ORDER BY tblTimecardData.Emp, tblWeekDays.Order;

    I have the following columns in timecard data: Employee Number (to Group on), Hours (Number of Hours Worked), Day (The Day they worked). if your table includes a date it can be easily modified to allow for this using Format(YourDate,"dddd")


    Hope this helps


    Chris

    0
     
    LVL 58

    Expert Comment

    by:harfang
    Your table structure is not obvious, but I think I could deduce it from your question.
    The table has something like:
    * EmployeeID
    * PayDate
    * ClockIn
    * LunchOut
    * LunchIn
    * ClockOut

    If you sort that table, you will see a continuous "TimeCard", but with missing dates, right?

    Idea 1

    The problem is that Access is *record oriented*. In Excel, you could easily design the Timecard and and fill it with lookup functions. In Access, this is not that easy. The "Excel" Approach would be this:

    Create a report/form with the design you want, using "unbound text boxes" for the information that will come from the table. You can then fill each with a function. Let's say you have the EmployeeID in a combo box called cboEmployee and the PayPeriod in a combo called cboPeriod, you can find the LunchOut for Monday with:

        = DLookup("LunchOut", "tblTimeCardData", "EmployeeID = '" & cboEmployee & "' And PayDate = #" & Format(cboPeriod - 5, "m/d/yyyy") & "#")

    Do that 28 times and you have the TimeCard... Not very fast, but it woks.

    Idea 2

    The pure database approach would have you create another table containing either *all dates* or just numbers from 1 to 7 (like in the above comment). Then you can create a query that will return exacly seven lines... but it's also quite difficult.
    * Create a query that will filter the current employee, like with "WHERE EmployeeID = Forms!frmTimeCard!cboEmployee" (this assumes that the form is called frmTimeCard and that the control to select the employee is called cboEmployee).
    * Create a query that will display exaclty the seven dates you want. If you have a table tblDayNbs with a field bytDay containing seven records 0 to 6, you will write "SELECT Forms!frmTimeCard!cboPayPeriod - bytDay FROM tblDayNbs" for example.
    * Create a LEFT JOIN query linking the second to the first, and use that as record source...

    Idea 3

    This one is much more Access based (as compared to SQL based). The idea is to have the data displayed in seven subforms / subreports. I just tried it and it works well... you can even edit the data!
    1) create a form showing one row of the table, with the time information, based on the table tblTimeCardData. Remove all scroobars, navigation buttons and record selector. Also set the "Cycle" property to "current record". Let's call the form fsubOneDay
    2) create a main form with two controls to select the employee and the pay period, say cboEmployee and cboPayPeriod.
    3) Add seven fields, txtSunday through txtSaturday, with ControlSource: "= cboPayPeriod - 6" to "= cboPayPeriod - 0". You can format those to show the day of the week as well as the date, i.e. format: "dddd * d/mm/yyyy". Align these controls as a column to the left.
    4) Drag the form "fsubOneDay" to the main form, this creates the first subform, and use LinkChildField: "EmployeeID;PayDate", LinkMasterField: "cboEmployee,txtSunday".
    5) Copy and paste the subform six times, arrange them in column and change only the LinkMasterField by changing the day of the week.

    This might sound complicated, but in fact I think it is the quickest way. No coding in VB, no SQL, only basic Access form management.

    I'm sure I have given you some food for thoughts :)

    Cheers
    0
     
    LVL 44

    Expert Comment

    by:GRayL
    At the end of the week you don't care if an employee had a long lunch, just that he/she gave you 40 hours.  You really need to know:

    LaborDetails
    ========
    EmpNo - Employee Number
    Loan - may on loan from another dept
    JobNo - the job code for the work done, or Sick or any other overhead charge. We had up to nine characters.
    WkIn - DateTime In for Work
    WkOut - DateTime Out from Work.

    The form should default to most common In and Out hours.

    LaborDetails then produces the WeeklyLabor table via an Insert Query

    WeeklyLabor
    ========
    WkDate
    EmpNo
    Loan
    JobNo
    Hours

    Every week WeeklyLabor also added to the YTDLabor table (YearToDate) via an update query:

    WkEnding
    EmpNo
    Loan
    JobNo
    Hours
    DOW - day of the week.  Filled in only for selected overhead charges, especially sick which always seems to occur on Mon or Fri.

    Labor Details was emptied after successfully creating WeeklyLabor.  We kept WeeklyLabor pared down to two or three Months lest there be any HR or Contract queries that required this depth of insight.  The YTDLabor became the basis for billing and history/archiving, and contract performance for assessing budget/actual charges against the various direct charge JobNo's.

    0
     
    LVL 44

    Expert Comment

    by:GRayL
    cws05: Die-Tech wants a Sun thru Sat labor week yet you appear to propose Mon thru Sun?
    0
     
    LVL 44

    Expert Comment

    by:GRayL
    WkDate in WeekLabor is still the specific day of the week. WeekEnding in this case would be the Saturday pay date.
    0
     
    LVL 58

    Expert Comment

    by:harfang
    Yes, find PayPeriod from PayDate:

        PayPeriod: PayDate - WeekDay(PayPeriod) + 7

    But I don't see how your suggestions are related to the work at hand, GRayL. You seem to be talking about a project of yours in a similar context, but you do not help printing the required weekly TimeCard.

    Just my opinion.

    Cheers!
    0
     
    LVL 44

    Expert Comment

    by:GRayL
    harfang: I think you mean -  PayPeriod: PayDate - WeekDay(PayPeriod) + 7 - should be -  PayPeriod: PayDate - WeekDay(PayDate) + 7.

    I was mainly concerned about data flow.  I spent 17 years on a program that required some very significant changes to all forms and reports because I did not appreciate all the uses that the data would be put to.  If you get this part right, changes are easy. The timesheet has to allow for every variation possible with all those people doing all those things.  I have not seen mention of overtime, flextime, unpaid timeoff, paid timeoff, or the job description.  In addition, employees will change the job to which they are charging perhaps several times a day.  All that has to be in there.

    Die-Tech:  Could you give us an idea of how many employees and the structure of your labor tables.  At a minimum, you have an Employees table, a Jobs table, and a Labor table and probably a separate Salaries table.  It sounds like your timesheet form/report will be the basis for which people are paid.  That has to be rock solid!

    How do you intend to handle errors?  Personnel charging to the wrong job number, paid/unpaid issues, etc.  I found it required the double entry form of bookkeeping - past errors had to be negated, and the corrected records entered after the fact.

    Automating timesheets is no easy matter.  If you are trying to replace a time sheet, replicate the time sheet in a form to start off.  If you are starting out new, ask around similar types of companies and get copies of their timesheets.  They no doubt went through several corrections before getting to something relatively stable.  
    0
     
    LVL 4

    Author Comment

    by:Die-Tech
    Ok All,

    Here are my tables....

    tblEmployees - Link Table to a Human Resources database table
    The HR database is where they keep up with all this changing info

    ID                  AutoNumber
    EmployeeID            Text
    FName                  Text
    LName                  Text
    Nickname                  Text
    Address1                  Text
    Address2                  Text
    City                  Text
    State                  Text
    Zip                  Text
    Shift                  Number
    Location                  Number            'Plant Location
    Extension                  Number            'Phone Extension
    ContactPhone1            Text
    ContactPhone1TypeID            Text
    ContactPhone2            Text
    ContactPhone2TypeID            Text
    EmergencyContact            Text
    EmergencyContactName      Text
    VacationHoursAllowed            Number
    Status                  Text            'A=Active, F=Fired, L=Laid Off, Q=Quit, etc.
    BirthDay                  Number            'Day of employee's birth - used for birthday list
    BirthMonth                  Number            'Month of employee's birth - used for birthday list
    HireDate                  Date/Time
    DepartDate            Date/Time
    DepartmentTypeID            Text            'Employee's department - Production, Quality, Engineering, etc.
    ManagerID                  Text            'Employee's immediate supervisor/manager
    AllowAfterShift            Yes/No            'Allow employee to clock back in after regular shift hours
    AllowDuringShift            Yes/No            'Allow employee to clock out during regular shift hours
    ShiftStartSUN            Date/Time
    ShiftEndSUN            Date/Time
    ShiftStartMON            Date/Time
    ShiftEndMON            Date/Time
    ShiftStartTUE            Date/Time
    ShiftEndTUE            Date/Time
    ShiftStartWED            Date/Time
    ShiftEndWED            Date/Time
    ShiftStartTHU            Date/Time
    ShiftEndTHU            Date/Time
    ShiftStartFRI            Date/Time
    ShiftEndFRI            Date/Time
    ShiftStartSAT            Date/Time
    ShiftEndSAT            Date/Time

    Note: all the ShiftStart and ShiftEnd fields are for compairing when the employee clocked in or out, to when the employee was scheduled to clock in or out.  The company uses "rounding rules" and a "point system"... meaning, if the employee was scheduled to clock in at 7:00am and they clock in between 7:01am and 7:07am their clock in time is rounded up to 7:15am and they get 1/2 point (points are not good)
    If the employee clocks in between 7:08am and 7:14am their clock in time is rounded up to 7:15am and they get 1 point.
    There are the same kind of rules for clocking out early, and clocking back in from lunch late, etc.



    tblTimeClock - This table is where I am keeping the employee's punches.

    ID                  AutoNumber
    EmployeeID            Text
    Status                  Text            'In for the day, Out to lunch, In from Lunch, Out for the day, etc.
    PayDate                  Date/Time            'Date of time clock punches
    PayPeriod                  Date/Time            'Pay Period ending date (Always Saturday)
    ClockIN                  Date/Time            'Employee Clocks in for the day
    LunchOUT                  Date/Time            'Employee Clocks out for lunch
    LunchIN                  Date/Time            'Employee Clocks back in from lunch
    ClockOUT                  Date/Time            'Employee Clocks out for the day
    DuringShiftOUT            Date/Time            'If Allowed, Employee clocks out during the shift
    DuringShiftIN            Date/Time            'If Allowed, Employee clocks back in during the shift
    AfterShift                  Date/Time            'If Allowed, Employee clock in after shift hours
    AfterShiftOUT            Date/Time            'If Allowed, Employee clock out after shift hours

    The "DuringShiftOUT and DuringShiftIN" fields are for when an employee needs to clock out during the shift to run an errand, go to a doctor's appointment, etc.  The elapsed time between the two is deducted from your hours worked for the day.
    The "AfterShiftIN and AfterShiftOUT" fields are for certain employees who work their regular shift, go home and then come back later that evening to restart CNC programs




    tblClockRecord - I'm using this table to keep up with what tblTimeClock record the employee is on.
    Once the employee clocks in for the day, that record number is stored in the tblClockRecord table and is used to pull up the employee's tblTimeClock record each time they swipe their badge.

    ID                  AutoNumber
    EmployeeID            Text
    RecordNum            Number


    Die-Tech has about 150 employees.  We are a tool & die shop that does production metal stampings.
    So far I haven't even started on the "JobClock" side of things yet.
    I'm trying to get the "TimeClock" fully functional first.

    Let me know if there are any other questions you have about the setup.

    Thanks,
    Die-Tech
    0
     
    LVL 44

    Expert Comment

    by:GRayL
    Die-Tech:  Thanks, a lot of good info.  Are you able to capture the timecard punches automatically?  I imagine a card reader is able to capture the info on the cards.  It looks like you have one record per employee per day.

    I think the route you are going is a good one.  In an ideal world, the system fill out the timeclock records automatically from the punched timecards.  No other interventions should be required except a view and approval by the supervisor.  It is this process from the time an employee "punches in" until the weekly labor data is sent to HR/Payroll for processing and subsequent payment to the employee that I want to make sure I understand.  What's the difference between "Once the employee clocks in for the day,"  and "each time they swipe their badge"?  In other words are you trying to build a timesheet for supervisor's, etc. based on clocks-in and clocks-out using data that is already in tblTimeClock or are you trying to populate tblTimeClock?
    0
     
    LVL 58

    Expert Comment

    by:harfang
    Yes, I agree with GRayL, the structure seems sound.

    One could argue that the fields ShiftStartSun, ShiftEndSun, ShiftStartMon, etc... look like bad database design. But I don't supect we will see a change in the length of a week during the lifetime of this application :) So that the structure is viable :)

    For the IN/OUT field, you of course realize that there are some limitations (can't have two leaves during shift or two after hours presence) and some integrity problems, which must be checked:
    * ClockIN < ClockOUT
    * ClockIN < LunchOUT < LunchIN < ClockOUT
    * ClockIN <= DuringShiftOUT < DuringShiftIN <= ClockOUT
    * ClockIN <= DuringShiftOUT < DuringShiftIN <= LuchOUT OR LuchIN <= DuringShiftOUT < DuringShiftIN <= ClockOUT
    etc...
    One problem I see, for instance, is that the "DuringShift" leave will often be until the end of the shift, meaning there will be no "DuringShiftIN", or that you will have to add a virtual "DuringShiftIN = ClockOUT" to the data...

    If all rules are checked, I guess the total time worked is something like:
    TotaIN = Nz(ClockOUT-ClockIN) - Nz(LuchIN-LuchOUT) + Nz(AfterShiftOUT-AfterShiftIN) - Nz(DuringShiftIN-DuringShiftOUT)

    Basically, I think I would have set up employee presence as pairs of In/Out pairs, perhaps with one or two documentation flags like ("Normal", "Back from luch", "Back from leave", "Leave late entry", "After hours") and ("out to lunch", "end of day", etc.) There would be less checks and more flexibility. On the other hand, your structure allows presentation and printing of the data as a weekly TimeCard, which would be more complex with my setup...

    Anyway, the structure should not cause too many problems: users have a ways to overcome limitations of a database for those two exceptions a year... :)

    Back to the original question:

    I would go with my idea 3): seven subforms on the weekly TimeCard.

    Each subform will show the data from one day, including the status. The calculated fields showing all dates of the week ensure that you will always have Monday's data in the same place, even if the record from tblTimeClock is empty.

    Final note, the field PayPeriod can be calculated (thanks GRayL for the correction!) but you might still have it in the table if you use that field in another relationship.

    If you need more details for my "idea 3", please let me know

    Cheers!
    0
     
    LVL 4

    Author Comment

    by:Die-Tech
    GRayL,

    Die-Tech is a tool & die shop.  We also do production metal stamping.
    The database application will be running on a Win2KPro wallmounted, touchscreen pc.

    The main screen is the ScanBadge form. This form has the date, the time, a label that says "Scan Badge" and a textbox that captures the employee's EmployeeID.

    After the employee scans their badge, the TimeClock form is opened passing the EmployeeID to it.
    The TimeClock form uses the EmployeeID and looks up the employee's info from the tblEmployees table, checks to make sure it is a valid employeeID and displays the employees EmployeeID, FName, LName.

    The TimeClock form also looks up the employee's timeclock recordnumber to see if the employee has already started a timeclock record for the day (Each employee should have 1 timeclock record for each day they clock in to work).

    If so, the form checks the Status to determine which command buttons, labels and textboxes should be enabled/disabled, visible/not visible.
    If not, the cmdClockIN button is enabled and visible.

    After the employee clicks(touches) the cmdClockIN button, a new timeclock record is created for the employee, Now() is recorded as the ClockIN time, the Status is set to "In for the day" and the record number is recorded so the next time the badge is swiped, the appropriate timeclock record is opened.

    The next time the employee swipes their badge, the TimeClock form looks up the EmployeeID, checks to see if it is valid, displays the employee info, checks the Status to determine which buttons should show... in this case the Status is now "In for the day".  The cmdClockIN button is visible but disabled, the cmdLunchOUT button is visible and enabled and if the employee's AllowDuringShift is True, the cmdDuringOUT button is also visible and enabled.

    The employee now has 2 choices... clock out for lunch or clock out during shift hours.

    And it goes on and on....

    If the employee clocks out for lunch, Now() is recorded as their LunchOUT time, the cmdLunchOUT button is disabled and the cmdLunchIN button is enabled, if the employee's AllowDuringShift is True, the cmdDuringOUT button is disabled also
    (if you are already clocked out for lunch, you can't clock out during shift hours.... until you clock back in from lunch).

    I will also be using the Status to show managers/supervisors/owner etc. the status of their employees.

    I hope that answered your questions... if not, ask more.



    So far I have something working for the TimeCard form
    Here is what I came up with.

    I have 7 queries like the one below: qryTimeCardSUN, qryTimeCardMON, qryTimeCardTUE, etc.
    (one for each day of the week... sub out the MON for TUE, etc)

    EmployeeID
    MON_PayDate: PayDate
    PayPeriod
    MON_ClockIN: ClockIN
    MON_LunchOUT: LunchOUT
    MON_LunchIN: LunchIN
    MON_ClockOUT: ClockOUT
    MON_BeforeLunch: DateDiff("n",[MON_ClockIN],[MON_LunchOUT])
    MON_Lunch: DateDiff("n",[MON_LunchOUT],[MON_LunchIN])
    MON_Lunch: DateDiff("n",[MON_LunchOUT],[MON_LunchIN])
    MON_AfterLunch: DateDiff("n",[MON_LunchIN],[MON_ClockOUT])
    MON_DuringShiftOUT: DuringShiftOUT
    MON_DuringShiftIN: DuringShiftIN
    MON_DuringShift: NZ(DateDiff("n",[MON_DuringShiftOUT],[MON_DuringShiftIN]),0)
    MON_AfterShiftIN: AfterShiftIN
    MON_AfterShiftOUT: AfterShiftOUT
    MON_AfterShift: NZ(DateDiff("n",[MON_AfterShiftIN],[MON_AfterShiftOUT]),0)
    MON_HoursWorked: (([MON_BeforeLunch]+[MON_AfterLunch]+[MON_AfterShift])-([MON_Lunch]+[MON_DuringShift]))/60


    Then there is another query called qryTimeCard_INIT

    EmployeeID
    PayPeriod
    SUN_PayDate: [PayPeriod]-6
    MON_PayDate: [PayPeriod]-5
    TUE_PayDate: [PayPeriod]-4
    WED_PayDate: [PayPeriod]-3
    THU_PayDate: [PayPeriod]-2
    FRI_PayDate: [PayPeriod]-1
    SAT_PayDate: PayPeriod


    The final query that is used by the form is qryTimeCard
    This query has the qryTimeCard_INIT query, all 7 DayOfWeek queries, and the tblEmployees table.

    The qryTimeCard_INIT EmployeeID is leftjoined to the EmployeeID on each of the 7 DayOfWeek queries.

    The qryTimeCard_INIT DAY_PayDate is leftjoined to the appropriate DAY_PayDate on each of the 7 DayOfWeek queries.
    (qryTimeCard_INIT.SUN_PayDate is left joined to qryTimeCardSUN.SUN_PayDate)

    The qryTimeCard_INIT EmployeeID is also leftjoined to the tblEmployees table. This way I can get the employees FName, LName, Shift, Plant Location, Daily Start and End shift times, etc.

    Then I tied a number of fields on the form to the fields in the qryTimeCard query.
    So the form shows the employee's EmployeeID, FName, LName, Shift, Plant Location, PayPeriod, Each Day of the week, the date each day, the employee's startshift and endshift time for each day, the actual times clocked for ClockIN-LunchOUT-LunchIN-DuringOUT-DuringIN-AfterOUT-AfterIN (if applicable), hours worked each day, hours added for aftershift (if applicable) hours subtracted for duringshift(if applicable), and the total hours worked for the week.

    At first I was only able to get this to work if the employee worked all 7 days, if any day was missing the qryTimeCard query wouldn't show any records.

    After rebuilding they way I was doing the queries (to what I have above), I was able to get the TimeCard form to show the correct info for an employee that worked all 7 days and one that worked only 1 day (Friday).

    It all seems to be doing what I want it to for now..... just wait until I have to round the clock times to the quarter hour (for the point system).

    Ok... so much has happened on this so far.

    Does what I came up with sound like something one of you suggested? (so I know how to dish out the points)

    Do you see any problems with what I came up with? Other than only being able to clock out and in during the shift only one time.... same with clocking in and out after the shift.


    Thanks,
    Die-Tech
    0
     
    LVL 58

    Expert Comment

    by:harfang
    It seems sound, but too complex and too slow. You are trying to do everything with SQL, while you have Access. As you need this information "on screen", do it with forms/reports...

    Again:

    1) set up a main form linked to tblEmployees, showing all needed data and with some navigation or external filter to display the proper employee.
    2) create a selection tool (on the form or from outside) to select the current or needed PayPerios, i.e. Week.

    You now have a form with, say, txtEmployeeID and cboPayPeriod. These can be used to display the TimeCard information.

    3) Add on the form seven fields, much like your qryTimeCard_INIT, one for each PayDate.
    4) Create a form that will be display the information needed for a single day, i.e. a single row of tblTimeClock. Remove all unwanted navigation tools, scroll bars and record selectors.
    5) Click-and-drag this form to your main form seven times.
    6) For all, set the link child field to "EmployeeID,PayDate" and lin master field to "txtEmployeeID"
    7) For each, correct Link Master Field to "txtEmployeeID,txtPayDate_SUN", "txtEmployeeID,txtPayDate_MON", etc.

    The advantage of this setup is that all information is still editable (by the user or by program). It uses only tables and very simple queries. This means it will work even with large tables.

    Your solution will start to become very slow on large tables, and the resulting  information is all read-only.

    If you need to change some detail of the calculation logic (e.g. total time worked), you will have to change it once with my solution or seven times with yours.

    Go with seven subforms / subqueries, you will thank me for it later.

    Good luck in any case :)

    0
     
    LVL 58

    Expert Comment

    by:harfang
    For example, I just noticed that you used Nz() only for the AfterShift and DuringShift hours worked. If you later realize that you need Nz() for those half-time workers or even Nz() for those who don't take a lunch break, you will have to check seven queries.

    BTW: your solution was in the broad field of my "idea 2", and of some other comments (exept that you used a query to build the list of dates, and not a table). In other words it's purely SQL.
    If you stick to your idea, I don't want any points, because I will have failed :)

    Cheers!
    0
     
    LVL 44

    Expert Comment

    by:GRayL
    Dan:  An admirable undertaking!  It looks like you have it pretty well under control.  I would suggest you reduce seven queries to one and distinguish day of the week in the where clause:  where weekday(paydate)=1 - Sun,  =2 - Mon, etc. In addition, because Access is much faster calculating down records as opposed to across records, you may want to consider narrowing the table tblTimeClock to:

    EmpID
    TmlID - if more than one way of entering or exiting the plant
    IO      - I(n) or O(ut)
    DT     - DateTime

    Then an aggregate query like:

    Select DateValue(a.DT) as DateWorked,a.EmpID, Sum(TimeValue(a.DT)) as SumIn,
    Select Sum(TimeValue(b.DT)) from tblTimeClock b where b.EmpID = A.EmpID and DateValue(b.DT) = DateWorked and IO = "O") as SumOut, (SumOut-SumIn) * 1440 as MinutesWorked
    from tblTimeClock a Where IO = "I"
    Group by DateValue(a.DT), a.EmpID

    This would give you a breakdown by employee by day.  (DateValue(DT) - day(DT)) as PayPeriod would give you the summary by week.  Note that the sum of Outs minus the sum of Ins time 1440 per employee will give you the time worked per per period in minutes.  However, with the small numbers involved you are probably just as well off with what you have as you probably are dealing with a couple of your own business rules which have not been mentioned.  Good LucK!
    0
     
    LVL 4

    Author Comment

    by:Die-Tech
    GRayL,

    After some heavy thinking about the situation.....

    I came to this conclusion.
    Right now, each employee gets 1 timecard for the week to collect all their IN/OUT punches.

    So, instead of having all the queries and a timeclock record for each day for each employee.....

    I decided to change the timeclock table to include all the punches for each day of the week.
    So, this means each employee will have 1 timeclock record for each payperiod.

    This may not be the proper way of doing this... I don't know.
    It definitely is making things much easier.

    No matter what day the employee clocks in, the current date is used to calculate the ending payperiod date (which would be the date of the current week's Saturday).

    Now throughout the week, I lookup the record where the employeeID = the employee's badge number and the PayPeriodDate = the calculated payperiod date (Saturday)

    My timecard query consists of the timeclock table and it's fields (like SUN_ClockIN, SUN_LunchOUT, MON_ClockIN, etc. etc.)
    The query also is calculating the time for lunch (in minutes), the time for the day (in minutes), the time out during shift (in minutes), the time in after shift (in minutes) and the total hours worked for each day of the week.

    I'm getting the total hours worked by adding the (time for the day + the time in after shift - the time for lunch - the time out during shift)/60

    So far, this seems to be working great.

    I think the only problem I can see is if someone clocks out for the day, or clocks out after shift and it's past midnight.
    The DOW variable (3-character Day of Week e.g. Sunday=SUN, Monday=MON, etc.) would be the next day's abbreviation.
    I will probably have to put some code in there to check the previous day's ClockOUT and AfterShiftOUT to see if they are null and if so, recalculate the DOW variable to yesterday's abbreviation.

    Then if the employee clocks out for the day, or out after shift and it's past midnight on Saturday...
    The DOW variable will be SUN and the PayPeriodDate will be the next Saturday.  So the code will need to pull up the last payperiod record and make sure the ClockOUT and/or AfterShiftOUT fields are not null.

    Does that make sense?

    Thanks,
    Die-Tech
    0
     
    LVL 44

    Expert Comment

    by:GRayL
    How important is it that you know the detail of every In and Out?  Why have 14 ShiftStart and ShiftEnd records in the Employees table?  In any given week doesn't a shift start and end at the same time of day?

    With my system, the only checking you would need to do is to ensure the Ins and Outs were done in pairs, except that the shift Out and In are in reverse.  You do not want a record with nearly 60 fields.  It makes your queries cumbersome and slow.  DBs are great doing things record by record.  Putting all the info in one record and querying field by field will slow things down.

    Look again at these three tables
    tblTimeCard
    ========
    EmpID - text
    Shift  - number 1,2,3
    Type - text  -  C, L, S, A for clock, lunch, shift, and after.  I really don't think this needed.
    IO  - text  - I or O
    DT - datetime

    Shifts
    ====
    Shift - number - 1,2,3?
    Type - text  C,L,S,A
    IO - text - I or O
    DT -  datetime - the expected In or Out time

    The Shifts data should not be in the Employees file!

    Employees
    =======
    EmpID
    FName
    LName
    etc.
    Shift, number 1,2,3

    Now a query joining the three tables will give you the info you need in your form.  If an employee stays past midnight on Saturday, the form creates an Out record for 23:59:59 on that Date, and creates another In record for 00:00:00 the next day (Sun) to the point where the employee clocks Out.

    12345  3  A  I  2004-10-30 23:00:00
    12345  3  A  O 2004-10-30 23:59:59 <= 59 min 59 sec in paypd 2004-10-30
    12345  3  A  I  2004-10-31 24:00:00
    12345  3  A  O 2004 1--31 02:30:03 <= 02 hrs 30 min 03 sec in paypd 2004-11-06

    This is dead easy to catch.  The two middle records are the dummy records created by the form for any time pair spanning a Saturday midnight.
    0
     
    LVL 4

    Author Comment

    by:Die-Tech
    GRayL,

    Nothing about this company is simple..... haha

    Q: "How important is it that you know the detail of every In and Out?"
    A: I'm using this data to calculate the employee's payroll hours.

    Q: "Why have 14 ShiftStart and ShiftEnd records in the Employees table?  In any given week doesn't a shift start and end at the same time of day?"

    A: We have basically 3 shifts..... mostly running shifts 1 and 2.
    Now that being said.... many employees have different shift hours...  One person who works 1st shift might be 7:00am to 3:30pm and another one might be 8:00am - 4:30pm with yet others at 6:00am - 3:30pm  The same way with 2nd shift... one person might be 3:30pm to 12:00am and another might be 4:30pm to 1:00am

    The timeclock record has a status field in it also.  I'm updating this field as employees clock in and out.
    When the employee clocks in for the day, Status="In for the day"
    When the employee clocks out for lunch, Status="Out for lunch"
    etc.

    I'll be pulling the Status info into a form for management to see the status of any employee who is using the timeclock.

    In your section about staying past midnight on Saturday....
    It looks like part of the employees hours would go on Saturday (for this week's paycheck) and then the rest of the hours would go on Sunday (for next week's paycheck)

    I may have to ask the boss how he wants to handle issues like this one.

    I was under the assumtion that when the employee clocks in on Saturday and for some reason works past midnight, all the time should go on their Saturday pay, since most employees don't work on Sunday anyway.... some do.


    Can the above issues be handled in the tables example you gave?

    Thanks,
    Die-Tech
    0
     
    LVL 44

    Expert Comment

    by:GRayL
    Hi Dan:  In my old company the work week was Sat thru Fri midnight.  Anything after midnight Fri went on the next paycheck.  The form would detect any entry pair that spanned Saturday midnight and generate the two records accordingly.

    As to your flex time, is it organized something like Start-60, Start-30, Start, Start+30, Start+60? If that is the case it could be coded with a single letter in the Employees file and spelled out as required in the Shifts file.

    By details, I meant is it sufficient to know several in/outs over the day or must they be labeled: ClockIn, LunchOut, LunchIn, ClockOut, AfterIn, AfterOut, ShiftOut, ShiftIn.  I would prefer that they be labeled just I, O, I, O, I, O, I, O.  You would be able to tell just by looking at the record whether it was a Clock, Lunch, Atter, or Shift type.  It makes generating the record somewhat easier also.  
    0
     
    LVL 58

    Expert Comment

    by:harfang
    At the start of this post, we tried to come up with a solution for existing tables. If you are changing the data structure, let me add a general comment.

    Several possible structures have been mentioned:

    * create fields for in/out times for the entire week (8x7=56 in/out fields)
    * use one record per day with 8 in/out fields (original structure)
    * create several in/ou pairs for each day, possibly with a type for better reporting
    * create only time stamps with a flag indicating "In" or "Out"

    At the top of the list, you create *wide* tables, with less records.
    At the bottom, you create *narrow* tables with many records.

    As a general advice, you obtain a better database structure with *narrow* tables. Transforming a data structure in such a way that the tables become more narrow is sometimes called "verticalization", and is considered a good thing.
    In this particular context, I would however stop just before the drastic "time-stamp" method.

    It seems to me that the "in/out" pairs provide the most flexibility and they are easy to implement:
    * When an employee checks in, a new record is generated ("Stamp_IN" is thus required)
    * When an employee checks out, the last record is located in order to fill the "Stamp_OUT" field.
    * A query showing all fields without OUT time provide the list of employees currently IN
    * A periodic check of records without OUT time is needed for maintenance
    * At check-out time, several checks can be done: missing record for the employee, work after midnight (this can generate a new record), etc.

    If you go one step down, you will have a lot of programming to do before you can even use the data. If you go upwards, you will have more work for the interface, the reporting, the querying and, more important, for the maintenance of the application.

    Bottom line: if you change the structure, go for simple IN/OUT pairs per record.

    Cheers :)
    0
     
    LVL 4

    Author Comment

    by:Die-Tech
    OK, Harfang and GRayL.....

    You are starting to convince me to change my tables.
    One thing I don't want is for this database to be slow.
    We may end up with 3-5 pcTimeClocks by the time we implement this.

    Harfang, when you said "When an employee checks out, the last record is located in order to fill the "Stamp_OUT" field."
    I'm assuming my recordset gathering will change.  Right now I'm selecting the record based on the employeeID and the PayPeriod.
    Currently my recordset selections are made when the main timeclock form opens (after swipping your badge on the opening form) and when you click the command buttons.

    So with the new table layout, I'm guessing when the employee clicks the ClockIN command button, the code for that button's onclick event should make a new record.
    When the employee clicks LunchOUT, that should also make a new record.
    When the employee clicks LunchIN, the code should select the timecard record by EmployeeID and Type=L and the IN field is null?
    When the employee clicks ClockOUT, the code should select the timecard record by EmployeeID and Type=C and the OUT field is null?

    GRayL's table example is as follows:
     tblTimeCard
     ========
     EmpID - text
     Shift  - number 1,2,3
     Type - text  -  C, L, S, A for clock, lunch, shift, and after.  I really don't think this needed.
     IO  - text  - I or O
     DT - datetime

    If I should be going for simple IN/OUT pairs per record, shouldn't the table have fields for the IN DateTime and the OUT DateTime?
    Something more like this?
     tblTimeCard
     ========
     EmpID - text
     Shift  - number 1,2,3
     Type - text  -  C, L, D, A for clock, lunch, during, and after.
     IO  - text  - I or O
     IN - datetime
     OUT - datetime

    Or

    Should the tblTimeCard record be like GRayL said and there would be 1 record for ClockIN, 1 record for LunchOUT, 1 record for LunchIN, and 1 record for ClockOUT (at the minimum) then if the employee clocked out during shift hours there would be 1 record for DuringShiftOUT and 1 record for DuringShiftIN.... also if the employee clocked in after shift hours, there would be 1 record for AfterShiftIN and 1 record for AfterShiftOUT.

    Am I understanding you guys? or do I still sound lost?



    I'll wait until I hear back from you before making any more table changes.

    Thanks,
    Die-Tech
    0
     
    LVL 58

    Expert Comment

    by:harfang
    I would suggest this:

    a) you stay with the current idea of one record per day. This way, you can keep the current ClockIN, LunchOUT, LunchIN, etc. logic.

    b) you go with simple IN/OUT pairs.

    The table could look like this:
       tlbTimeCard
          EmpID
          TimeIN - date/time, required
          TypeIN - one letter storing which button was used (clock in, lunch in, shift in, etc.)
          TimeOUT - date/time, not required
          TypeOUT - again, which button was used

    At each entry:
    * validate that there are no records without a TimeOUT for that employee. If there is, take some action or flag for later
    * store TimeIN and TypeIN

    At each exit:
    * Locate the record where TimeOUT is Null
    * If there are none, create a new record, but flag it somehow (for example TimeIN = TimeOUT)
    * Store TimeOUT and TypeOUT

    If you go with that logic, the printing of the time card (the original question btw) will be a little more complex. One way to make things easier would be to prevent the usage of the same TypeIN or the same TypeOUT twice on the same day. This way, you can revert to the previous logic because:
       LunchOUT = TimeOUT with TypeOUT = "L" on that day
       LunchIN = TimeIN with TypeIN = "L" on that day

    I hope this is more clear.

    Good Luck
    0
     
    LVL 44

    Expert Comment

    by:GRayL
    You are reading me correctly.  Were it my project I would opt for a separate record for each punch.  Record checking would be for pairs, and anything past midnight on Saturday.  Every In has to have an Out.

    Select EmpID datevalue(DT), Count(DT) as EmpRecCt, (EmpRecCt mod 2) as Bad from tblTimeCard group by EmpID, datevalue(DT) having (EmpRecCt mod 2) = 1;

    This will list every employee with a count that is an odd number.  No records returned is good!

    Now when you start to incorportate job numbers, that is the addition of one field to your record.  Instead of In, Out, you may want to use Start, End.  Now everytime the employee works on something else, he/she generates a new record pair.  How will you handle that with the one record per day concept?
    0
     
    LVL 4

    Author Comment

    by:Die-Tech
    GRayL,

    I think you just blew me away with that select statement....

    Answer me one thing first....  Should tblTimeCard have only one field for DateTime? (like you suggested in the first place)
    I guess this would mean there should be a record for each clock in and a record for each clock out.... right?
    Not one record that has two DT's

    If it's one record for each clock in and one record for each clock out... then is the Select statement above is kind of like a duplicates query? except that it will show you records that are not "paired"?

    GRayL: "Now when you start to incorportate job numbers, that is the addition of one field to your record.  Instead of In, Out, you may want to use Start, End.  Now everytime the employee works on something else, he/she generates a new record pair."

    DT: I agree... instead od IN, OUT, I was going to use ON, OFF (that's the teminology they use around here... clock on to job MO-1234)


    GRayL: "How will you handle that with the one record per day concept?"

    DT: I was going to have another separate table for the job clock... but it looks like the new table would have the time clock and the job clock data in the same table.  Right?


    How complicated will it be to handle the issue with the employee working past midnight on Saturday?
    Would that be handled when the employee clocks out?
    Would the on click event would have to make two new records? one for the 11:59:59 to go with Saturday's clock in record? and then a new record clocking in on Sunday at 12:00:00 for the first half of the pair that goes with the actual after midnight clock out time?



    Thanks,
    Die-Tech
    0
     
    LVL 44

    Expert Comment

    by:GRayL
    I'm suggesting one datetime field per record.

    The select statement group all records by employee, counts them and if the number is odd, it includes the employee, date, and no of records for that day.

    The if you added a job field to the timeclock record, you have everything you need.  If the employee has a computer on the desk, he/she could be given access to the labor table to input job changes.  Otherwise, the changes would have to be done at the pcTimeClock.

    Saturday midnight.  The form always brings up the last record.  If the day is now Sunday and the last record was Saturday, we have the situation where two records are added before clocking OFF.  This would permit work past midnight on any other day of the week.

    I am still wrestling with the notion of not having the type C, L, S, A in the record.  It would be a different field from IO or SF or ONOFF,  This would simplify the sum function when processing the times to get minutes worked.  Can you tell me why you would want to have the type of clock action in the record?  If you do something about late arrivals, early departures, and long lunches, I can understand.  

    So with 150 employees, with an average of say 8 records per day and an average of 5.5 days per pay period, we wind up with 6600 records per pay period.  Assumes 2 different jobs and one out/in or after shift per employee per day. You probably want to keep the payperiods in separate tables. Every week they would be summed and used to update the pay, scheduling, project costing, and HR tables.  You might want to have access to this detail for 3 - 6 months, after which they could be deleted.
    0
     
    LVL 44

    Expert Comment

    by:GRayL
    In my Select statement I missed the comma after the first field.  Sorry.
    0
     
    LVL 4

    Author Comment

    by:Die-Tech
    GRayL: "Can you tell me why you would want to have the type of clock action in the record?  If you do something about late arrivals, early departures, and long lunches, I can understand."

    That is exactly why.  The boss has a point system in place that gives points to employees for clocking in late, leaving early, taking a long lunch, no call/no show, called/no show, etc.

    As far as the C, L, S, A stuff goes..... I will be using this to update a status screen.  This way the managers can see if the employee is clocked in, or at lunch, or gone for the day, etc. This could be helpful on the jobclock side of things.

    While employees are clocking on and off jobs, we need to capture what "operation" they are doing on a particular job.
    Say they are working on MO-1234 (this would be the job number), the system needs to know are they doing Setup, Labor, Rework, Tooling, etc. (this is the operation).
    Eventually I want to know EmployeeID 123 worked  X hours on MO-1234 duing Setup.


    When you say "You probably want to keep the payperiods in separate tables", what exactly do you mean?

    Thanks,
    Die-Tech
    0
     
    LVL 44

    Expert Comment

    by:GRayL
    Then add two fields, Job and Operation to the labor table. "MO-1234" goes into Job, and "Setup" or some suitable abbreviation goes into Operation.

    I believe your pay periods were weekly.  Therefore PP20041106 would be the tablename for this weeks data, PP20041113 for next week, etc.

    I can see including the clock type in your data, at least until you get going.  If at some point in the future you find that you tend to look at all the records of an employee for a given day, then perhaps you might agree that the type field is not doing much for you.  
    0
     
    LVL 58

    Assisted Solution

    by:harfang
    I see this topic has been active! I am dissapointed that my last comment was not even acknowleged... :(

    I will insist one last time... Do NOT go with the solution under discussion. It already generates very complex data management. It needs a complex checking mechanism. Let me give you some examples:

    GRayL's SQL:

       SELECT EmpID, DateValue(DT) AS PayDate, Count(DT) AS EmpRecCt, (EmpRecCt Mod 2) AS Bad
       FROM tblTimeCard
       GROUP BY EmpID, DateValue(DT)
       HAVING Count(DT) Mod 2 = 1;

    Shows employees having an odd number of records for a day. What he meant was of course:

       SELECT EmpID, DateValue(DT) AS PayDate
       FROM tblTimeCard
       GROUP BY EmpID, DateValue(DT)
       HAVING Sum( IIf(InOut = 'I', 1, 0) ) <> Sum( IIf(InOut = 'O', 1, 0) );

    Which will compare the number of In and Out records for the day.
    Still, this does not show Employees having for example "In", "In", "Out", "Out" records...

    Using the solution with In/Out pairs, the above query is trivial, and extremely fast if ClockOut is indexed:

        SELECT EmpID, ClockIN Where ClockOUT Is Null;

    You see, the "one time stamp per record" stucture has several implicit validation rules, including:
    * For each employee, "In" and "Out" records need to alternate.
    * Each "Out" record needs to have a matching "In" record in the same day.

    These need to be managed in the procedural model (i.e. by VB code), while they can easily be placed in the data model by using In/Out pairs in the same record. The "same day" is also easily managed:
    * When checking out, check DateValue(TimeOUT) = DateValue(TimeIN) and of course TimeOUT>TimeIN

    At this point, I see only disadvantages to the proposed structure, not a single advantage.
    AND It gets worse later:
    How are we going to compute hours worked? With my proposal it's trivial:

        SELECT EmpID, DateValue(TimeIN), Sum(TimeOUT-TimeIN) GROUP BY EmpID, DateValue(TimeIN)

    I would like to see GRayL's solution (involving VB, no doubt).


    Please acknolege this comment and give it some thought!

    Gook Luck in any case :)
    0
     
    LVL 58

    Expert Comment

    by:harfang
    BTW:
    > "While employees are clocking on and off jobs, we need to capture what "operation" they are doing on a particular job"

    Another good reason to use ON/OFF pairs in the same record. This allows the typing of worked hours.

    Cheers!
    0
     
    LVL 4

    Author Comment

    by:Die-Tech
    Ok harfang, you have my attention!

    I went back and reread your post.  At first I thought you were saying I should do both a) and b), then I realized you meant do a) keep what I got, or b) your suggestion.

    I follow your table design, and the logic on the IN's (btw, I don't know if this has been mentioned or not but on my timeclock form I'm using 8 command buttons - cmdClockIN, cmdLunchOUT, cmdLunchIN, cmdClockOUT, cmdDuringShiftOUT, cmdDuringShiftIN, cmdAfterShiftIN, cmdAfterShiftOUT - depending on what your status is, buttons will be visible/invisible and/or enabled/disabled i.e. If you clock out for lunch, you can't do anything until you clock back in from lunch).

    you wrote this below about the logic on the OUT's....

    At each exit:
    * Locate the record where TimeOUT is Null
    * If there are none, create a new record, but flag it somehow (for example

    TimeIN = TimeOUT)
    * Store TimeOUT and TypeOUT

    You lost me on the "If there are none, create a new record...."
    I can see the employee hitting the ClockIN button and the ClockIN logic making a new record like so.

    EmpID      TimeIN              TypeIN     TimeOUT    TypeOUT
     123  2004-11-04 07:00:00     S             null            null

    BTW, I'm thinking S for Shift (ClockIN, ClockOUT record), L for Lunch (LunchOUT, LunchIN record), D for DuringShift (DuringShiftOUT, DuringShiftIN record) and A for AfterShift (AfterShiftIN, AfterShiftOUT record)

    Then the employee wants to clock out for lunch so he hits the LunchOUT command button.
    The cmdLunchOUT logic shouldn't find the record above, right?
    Shouldn't the logic make a new record like the one below?

    EmpID      TimeIN       TypeIN           TimeOUT           TypeOUT
     123            null            null      2004-11-04 11:45:00       L

    or like this?

    EmpID             TimeIN              TypeIN     TimeOUT    TypeOUT
     123      2004-11-04 11:45:00        L             null             null


    If the record should be like the 1st one, when the TimeIN field is populated and you DateDiff (in minutes) between the TimeIN and TimeOUT wouldn't the result be a negative number?
    If so, this might be a good thing because this amount of time would be deducted from the overall total between the "S" TimeIN and TimeOUT record.  THis would also hold true for the "DuringShift" record(s), they would be negative and would be subtracted from the "S" record total.

    The employee hits the LunchIN command button, the cmdLunchIN logic should find this record

    EmpID      TimeIN       TypeIN             TimeOUT           TypeOUT
     123           null             null        2004-11-04 11:45:00       L

    and edits it to

    EmpID              TimeIN            TypeIN           TimeOUT              TypeOUT
     123       2004-11-04 12:15:00       L        2004-11-04 11:45:00         L


    Then the employee hits the ClockOUT command button, the cmdClockOUT logic should find this record
    EmpID           TimeIN             TypeIN     TimeOUT    TypeOUT
     123     2004-11-04 07:00:00      S            null              null

    and edit it to

    EmpID              TimeIN           TypeIN            TimeOUT              TypeOUT
     123       2004-11-04 07:00:00     S         2004-11-04 15:30:00         S
    You could get the DateDiff (in minutes) for the day from the "S" record above.

    The Lunch record could look like this
    EmpID              TimeIN              TypeIN           TimeOUT             TypeOUT
     123       2004-11-04 12:15:00        L        2004-11-04 11:45:00         L

    If the employee clocked out and back in during shift hours the "D" record would look like this
    EmpID             TimeIN              TypeIN            TimeOUT             TypeOUT
     123      2004-11-04 10:00:00        D         2004-11-04 09:30:00         D

    and if the employee clocked back in and back out after regular shift hours the "A" record would look like this
    EmpID              TimeIN              TypeIN           TimeOUT              TypeOUT
     123       2004-11-04 20:00:00        A        2004-11-04 21:00:00         A

    The DateDiff for the "S" record would be 510 minutes.
    The DateDiff for the "L" record would be -30 minutes.
    The DateDiff for the "D" record would be -30 minutes.
    The DateDiff for the "A" record would be  60 minutes.

    Add it all together and you get 510 minutes or 8.5 hours total for the day.

    Am I on the right track here??

    or are you saying records could look like this?
    EmpID           TimeIN                TypeIN             TimeOUT               TypeOUT
     123      2004-11-04 07:00:00        S         2004-11-04 09:30:00           D
     123      2004-11-04 10:00:00        D         2004-11-04 11:45:00           L
     123      2004-11-04 12:15:00        L          2004-11-04 15:30:00          S
     123      2004-11-04 20:00:00        A          2004-11-04 21:00:00          A

    and possibly this

    EmpID             TimeIN              TypeIN            TimeOUT               TypeOUT
     133      2004-11-04 07:00:00        S        2004-11-04 11:45:00            L
     133      2004-11-04 12:15:00        L         2004-11-04 15:30:00           S


    I think example where the TypeIN's and TypeOUT's were the same (i.e. both "S" or both "L") would work better for me.

    If that is the case, do I even need a TypeIN and a TypeOUT? couldn't I just have a Type?

    Sorry this post is so long....



    Thanks,
    Die-Tech
    0
     
    LVL 58

    Expert Comment

    by:harfang
    > "Sorry this post is so long"
    No problem, on the contrary. You show you are giving it some thought!
    (plus, I can cut and past your examples, which helps a LOT)

    > "or are you saying records could look like this?"
    EmpID           TimeIN                TypeIN             TimeOUT               TypeOUT
     123      2004-11-04 07:00:00        S         2004-11-04 09:30:00           D
     123      2004-11-04 10:00:00        D         2004-11-04 11:45:00           L
     123      2004-11-04 12:15:00        L          2004-11-04 15:30:00          S
     123      2004-11-04 20:00:00        A          2004-11-04 21:00:00          A

    YES.

    Look at the beauty of it: very easy to read, very easy to query (total time worked in a flash).
    Now the logic:

    1) Employee Clocks In:
    EmpID      TimeIN              TypeIN     TimeOUT    TypeOUT
     123  2004-11-04 07:00:00     S             null            null

    2) Employee out for Lunch: (lunch out, shift out and during shift out active)
     123      2004-11-04 07:00:00        S         2004-11-04 11:45:00           L

    3a) Employee back from Lunch
     123      2004-11-04 07:00:00        S         2004-11-04 11:45:00           L
     123      2004-11-04 12:15:00        L          Null                                 Null

    3b) Employee eats something funny, goes home, calls in sick and comes back next morning...
     123      2004-11-04 07:00:00        S         2004-11-04 11:45:00           L
     123      2004-11-05 07:15:00        S          Null                                 Null

    4) back from 3a) Fire alarm, employee exits through the window... comes back after the test.
     123      2004-11-04 07:00:00        S         2004-11-04 11:45:00           L
     123      2004-11-04 12:15:00        L         2004-11-04 12:15:00           ?             <--- time = 0:00
     123      2004-11-04 14:22:00        X          Null                                 Null

    Paycheck will not be issued until the "?" is resolved by a manager. "X" allows "special entry"

    5) another employee checks out... no IN record???
     007      2004-11-04 21:00:00        ?          2004-11-04 21:00:00          A             <--- time = 0:00

    Nothing will be paid until solved by a manager...

    As you see, this structure, by allowing only a single Null value works for all possible cases. You can of course help the data entry by disabling some buttons, but make sure the logic is sound, or leave an "override" button (always allow "special" entry and exit to be solved later).

    Now please do the same exercise with GRayL's structure. You will have some new white hair before you have something plausible.


    I hope this makes sense.

    Good Luck!
    0
     
    LVL 58

    Expert Comment

    by:harfang
    Some other scenari come to mind:
    * during shift out until lunch, back from lunch
    * after shift in, after shift out next morning... this should generate a "dummy" midnight exit and reentry. (logic: if OUT time is in the next day, close the current record at midnight, generate new one and accept exit on the new record)

    I hope I have convinced you ;)
    0
     
    LVL 4

    Author Comment

    by:Die-Tech
    harfang,


    "Look at the beauty of it: very easy to read, very easy to query (total time worked in a flash)."

    Could you give me some examples of how I would query and calculate the time from the proposed table structure?

    Remember my boss has all those rules about clocking in late, leaving early, taking a long lunch, etc...

    I don't get how I can calculate the total hours for the day, the total hours out during shift, the total hours in after shift and the total hours/minutes for lunch.

    That was why I was thinking each record would be the IN and OUT times for a particular type (S,L,D,A)
    It looks like I'll have to find the OUT time where the type is an L and the IN time where the type is an L and find the difference between the two?

    Hopefully some examples will help make more sense.



    Thanks,
    Die-Tech
    0
     
    LVL 44

    Expert Comment

    by:GRayL
    harfang: No VB - as if there was any doubt!  I showed you this in http:Q_21187889.html#12484497.   <You will have some new white hair before you have something plausible.>  Kind of an "uncalled for" coment.  You could speed your query up if you took the same approach.  


    DT:  I've simplified my previous query but it is still more complex than harfang's.

    Select a.EmpID, (a.DT-Day(a.DT) as PayPd, (Sum(TimeValue(a.DT))  - (Select Sum(TimeValue(b.DT)) as SumIn from tblTimeCards b where b.EmpID = a.EmpID and b.IO = "I") ) * 24 as HoursWorked from tblTimeCards a Where a.IO = "O"
    group by a.EmpID, (a.DT-Day(a.DT);

    I agree,  harfang's one DTstart and dTend per record looks like it will be a cleaner approach.  When you get to adding Job info, start/end extends the utility of the table by just adding Job and Phase fields.  
    0
     
    LVL 58

    Accepted Solution

    by:
    I made some tests with the following structure:

    tblClockTimes
    --------------------
       lngEmpID, Long, "Emp. ID"
          Required, Indexed
          [linked from tblEmployees]
       datPayDate, DATE/Time, "Pay Date"
          Format: yyyy-mm-dd
          Validation Rule: [datPayDate]-[datPayDate]\1=0
          Validation Text: 'Pay Date' cannot contain a time portion.
          Required, Indexed
       dtmTimeIN, Date/TIME, "Time IN"
          Format: Short Time
          Validation Rule: Between 0 and 1
          Validation Text: 'Time IN' can contain only a time.
          Required
       strTypeIN, Text(1), "IN"
          Required
          [lookup from tlkpClockTypes]
       dtmTimeOUT, Date/TIME, "Time OUT
          Format: Short Time
          Validation Rule: Is Null Or Between 0 and 1
          Validation Text: 'Time OUT' can contain only a time.
       strTypeIN, Text(1), "OUT"
          [lookup from tlkpClockTypes]
    Indexes:
       ndxKeyTimeIN: lngEmpID, datPayDate, dtmTimeIN
          Primary, Unique
       ndxKeyTimeOUT: lngEmpID, datPayDate, dtmTimeOUT
          Unique, Ignore Nulls
       ndxKeyTypeIN: lngEmpID, datPayDate, strTypeIN
          Unique
       ndxKeyTypeOUT: lngEmpID, datPayDate, strTypeOUT
          Unique, Ignore Null
    Validation Rule: [dtmTimeOUT] Is Null And [strTypeOUT] Is Null Or [dtmTimeOUT]>=[dtmTimeIN] And [strTypeOUT] Is Not Null
    Validation Text: Invalid 'Time OUT' or missing 'OUT' fields.

    Ok, I might be overdoing it a bit, but this implements many useful rules, on which the programming logic can count at all times. To be certain about certain characteristics of the data is always helpful...

    Basic query with worked time, qryClockTimems:

    SELECT tblClockTimes.*, [dtmTimeOUT]-[dtmTimeIN] AS dtmWorked
    FROM tblClockTimes;

    (tdmWorked, Caption: Worked, Format: Short Time)

    Problems to solve, qselClockProblems:

    SELECT tblClockTimes.*, [dtmTimeOUT]-[dtmTimeIN] AS dtmWorked
    FROM tblClockTimes
    WHERE strTypeIN='?' OR strTypeOUT='?' OR datPayDate<Date() AND dtmTimeOUT Is Null;

    Employees currently IN, qselEmployeesIN:

    SELECT DISTINCTROW tblEmployees.*
    FROM tblEmployees INNER JOIN tblClockTimes
    ON tblEmployees.lngEmpID = tblClockTimes.lngEmpID
    WHERE datPayDate=Date() AND dtmTimeOUT Is Null;

    Worked hours per Employee per Day, qsumEmpPaDateWorked:

    SELECT lngEmpID, datPayDate, Sum([dtmTimeOUT]-[dtmTimeIN]) AS dtmWorked
    FROM tblClockTimes
    GROUP BY lngEmpID, datPayDate;

    Worked hours per Employee per Week, qsumEmpPayPeriodWorked:

    SELECT lngEmpID, [datPayDate]-Weekday([datPayDate])+7 AS datPayPeriod,
       Sum([dtmTimeOUT]-[dtmTimeIN]) AS dtmWorked
    FROM tblClockTimes
    GROUP BY lngEmpID, [datPayDate]-Weekday([datPayDate])+7;


    And finally, how to rebuild the previous structure if needed (e.g. for the TimeCard report?)
    This needs two subqueries and one main query:

    qxtbTimeCardIN:

    TRANSFORM First(dtmTimeIN)
    SELECT lngEmpID, datPayDate
    FROM tblClockTimes
    GROUP BY lngEmpID, datPayDate
    PIVOT 'dtmIn' & [strTypeIN];

    qxtbTimeCardOUT:

    TRANSFORM First(dtmTimeOUT)
    SELECT lngEmpID, datPayDate
    FROM tblClockTimes
    GROUP BY lngEmpID, datPayDate
    PIVOT 'dtmOut' & [strTypeOUT];

    finally, using the above as subqueries, qselTimeCard:

    SELECT *,
       qxtbTimeCardOUT.lngEmpID AS lngEmpID2,
       qxtbTimeCardOUT.datPayDate AS datPayDate2
    FROM qxtbTimeCardIN INNER JOIN qxtbTimeCardOUT
    ON (qxtbTimeCardIN.datPayDate = qxtbTimeCardOUT.datPayDate)
    AND (qxtbTimeCardIN.lngEmpID = qxtbTimeCardOUT.lngEmpID);



    Hmm, looks like I still needed to defent my theory :)

    Have Fun!
    0
     
    LVL 44

    Expert Comment

    by:GRayL
    harfang:  What do you get by separating date from time besides an extra 16 bytes per record? I think the fields should be dtmDTIn and dtmDTOut.  It cleans up the record logically, you have two less fields, and save 16 bytes per record.

    In addition, I think your second strTypeIn in the structure of tblClockTimes should in fact be strTypeOut.
    0
     
    LVL 58

    Expert Comment

    by:harfang
    The advantage of separating Date and Time are numerous:
    * In almost every query, form or report, you will want to separate Date and Time (e.g. GROUP BY). This saves many, many calls to VB from SQL (DateValue(), TimeValue())
    * Filtering and selecting by date will be optimized because there is an index on lngEmpID, datPayDate (well, several actually)
    * I allows the LinkChildField/LinkMasterField mechanism to work without additional hacking...
    * It implements very naturally the rule that IN and OUT must be on the same day, but flexibly (change a validation rule to "Between 0 and 1.5" to allow OUT times until next morning at 6:00)
    * It allows to create a unique index to prohibit the same TypeIN or TypeOUT twice on the same day

    BTW, a date field uses 8 bytes. I would use a Long Integer to store the date (4 bytes) and Single for the time (4 bytes) for a total of 12 instead of 16 for two date/time fields. *I* would, but I would never *advise* someone else to do that (too many tiny problems later...)

    As for the correction, you are right of course. Thank you!

    Cheers :)
    0
     
    LVL 44

    Expert Comment

    by:GRayL
    A major problem I see with separating date and time is the fact that all times still have year, month and day associated with them. A date value always has 12:00 AM as its time.  You can never lose sight of this fact when querying your data.  When you are trying to collect records between given dates and times, the query is more cumbersome when you have them separated.  

    When you process hours worked your way, it is slower than if you process the sum of the Outs minus the sum of the Ins. ie.  Sum(dtmTimeOut) - Sum(dtmTimeIn) is faster that Sum(dtmTimeOut - dtmTimeIn).  In my case I do not need TimeValue for this portion of the query as the dates for each INOUT pair are always the same.  They will cancel.  

    In the immediate window:
    var=cdate(.25)
    ? var
    06:00 AM
    ? year(var)
    1899
    ? month(var)
    12
    ? day(var)
    30
    var=now()
    var=datevalue(var)
    ? timevalue(var)
    12:00 AM

    For the number of records involved here, and the very small overhead used when having to call datevalue() or timevalue(), and the certainty of always knowing what you are dealing with, I recommend you do not split date from time - as you never can!
    0
     
    LVL 58

    Expert Comment

    by:harfang
    Is this a joke? Missing smiley?
    Well, perhaps not.

    The notation "12:00 AM" is confusing... On a 24 hours clock, it reads "0:00", which seems logical. A date is stored internally as number, of which the integral part is the date and the decimal part the time. Thus:
    Date() + 0.25 is "6:00" today (in the morning).
    Date() - 0.041666 is "23:00" last night (eleven post meridiem)

    By the way, did you know that the value "True" is always associated with Saturday??? Do you sometimes "loose sight of this fact" in your applications?

    Bool = CDate(True)
    ? Bool
    29.12.1899
    ? WeekDay(Bool)
     6

    Ooohhhh, then you probably don't know about the undocumented date associated with any database?
    ? CDate(Len(CurrentDb.Name))

    (just joking... couldn't resist :)

    Joke apart, the fact that the MS date system starts on 0 = 30.12.1899 is the result of a bug (they thought 1900 was a leap year). They meant to choose 31.12.1899, so that day 1 would be 1st of Jan, which they thought was a Sunday... The bug was not very important as, to this day, Excel does not accept "negative" dates...

    In Access, dates range from #1 jan 100# (-657434) to #31 dec 9999# (2958465). Any number, integer or floating-point, between those values can be converted to a date.

    Finally: "overhead used when having to call datevalue()"
    True, DateValue is a VERY FAST function, also known as Int() (remove decimal part), so it will work reasonably well. Still, having to use a function at all prevents the JetEngine from using the indexes of the table. From an analytical point of view, it is always best to sort and filter only on indexes...
    And of course, this is only one of the advantages of splitting Date and Time.

    Let me add another advantage to the list:
    * The PayDate being one of the important database concepts, both for the User and for the Data Model, it should be explicitly present in the data, not derived from another field.
    (this would be different in a scientific data acquisition system interrested in TIME, not in DATES)

    Cheers!
    0
     
    LVL 44

    Expert Comment

    by:GRayL
    DT - are you there?  It's time to make a call.  

    * The PayDate being one of the important database concepts, both for the User and for the Data Model, it should be explicitly present in the data, not derived from another field.
    (this would be different in a scientific data acquisition system interrested in TIME, not in DATES)

    This would have you believe that Age should be in a personnel table.  PayDate is not a concept, it is a fact - derrived or stated. The basic guide to table design is - if it's derivable, do not store it - regardless of how important it is!  It's like storing a Balance after every transaction. Anyway you have my opinion.  Have a good one.
    0
     
    LVL 58

    Expert Comment

    by:harfang
    > "if it's derivable, do not store it"
    Of course, I totally agree. But this is not the point. If you can derive A from B and B from A, you choose one, sometimes for a reason, somtimes not:
    * If you have the choice between storing the age or the date of birth, you will go for DOB because it is *stable*.
    * If you store a temperature, you will choose between °C and °F according to local practice.

    In this case, the event "employees checks in" occurs at a certain time and date. But in the database, you never need that value. You will need the date for some things and the time for others, never the date/time of the event. It feels strange because you would never store the kilometers and the meters of a distance in separate fields.

    So it's a case where E (for event) can be derived from D and T, and both D and T can be derived from E. You would choose to store E, I chose above a structure storing D and T.

    If we go one step further, if both DateTimeIN and DateTimeOUT *have* to be on the same date, the *date portion* of one can be derived from the other... :)

    Anyway, this is splitting hair. If at any point I offended you, please forgive me. In the end, I'm sure the user wouldn't see a difference anyway...

    Cheers!
    0
     
    LVL 4

    Author Comment

    by:Die-Tech
    harfang,

    Could you explain what this part of one of your previous posts means?

    ---------------------------------------------------------------
    Indexes:
       ndxKeyTimeIN: lngEmpID, datPayDate, dtmTimeIN
          Primary, Unique
       ndxKeyTimeOUT: lngEmpID, datPayDate, dtmTimeOUT
          Unique, Ignore Nulls
       ndxKeyTypeIN: lngEmpID, datPayDate, strTypeIN
          Unique
       ndxKeyTypeOUT: lngEmpID, datPayDate, strTypeOUT
          Unique, Ignore Null
    Validation Rule: [dtmTimeOUT] Is Null And [strTypeOUT] Is Null Or [dtmTimeOUT]>=[dtmTimeIN] And [strTypeOUT] Is Not Null
    Validation Text: Invalid 'Time OUT' or missing 'OUT' fields.
    ----------------------------------------------------------------

    Is there a place in Access where you can create indexes?
    I thought you specify if a field should be indexed or not and have duplicates or not when you design the table.

    Also, I've never used Validation Rule and Validation Text before... I take it these go in the Validation Rule and Validation Text field properties for a particular field in the table?

    I also don't understand this part --->       [lookup from tlkpClockTypes]
    Are you saying there should be another table of some sort for "ClockTypes"
    What are you calling "ClockTypes"?  The S,L,D,A we have been discussing?

    Once I understand your model, I'm going to build it (in another copy of the database) and see how it functions.

    I'm still concerned with calculating things like "How long employee was clocked out for lunch", etc.

    Also, are you saying the TimeIN and TimeOUT fields should be TIME only? and not TIME&DATE?

    Here's something else that threw me off... strTypeIN, Text(1), "IN"
    What does the Text(1) mean?

    You wrote....
          dtmTimeIN, Date/TIME, "Time IN"
          Format: Short Time
          Validation Rule: Between 0 and 1
          Validation Text: 'Time IN' can contain only a time.
          Required

    Let me see if I can understand this...
    dtmTimeIN is the Field Name
    Date/TIME is the Data Type
    "Time IN" is the Caption
    Short Time is the Format (obviously, since you wrote that above)
    Validation Text is the popup error message when you enter a prohibited value.
    Validation Rule: Between 0 and 1 - I don't get it? Can you explain this?

    Sorry if I'm becoming a pain..... but I think I need to have a really good understanding of this before I abandon what I've started in favor of this new design.

    Thanks,
    Die-Tech
    0
     
    LVL 58

    Expert Comment

    by:harfang
    I'll be back in a few hours. Do not rebuild, I'll make my copy available.
    0
     
    LVL 4

    Author Comment

    by:Die-Tech
    Ok... that's great!

    Could you also answer the questions above so I can follow your logic?
    I'm all about learning how to make my applications better.

    Thanks,
    Die-Tech
    0
     
    LVL 58

    Expert Comment

    by:harfang
    First the sample database:
        ftp://www.harfang.ch/expert/TimeCard.zip
    0
     
    LVL 58

    Expert Comment

    by:harfang
    Oh, perhaps rather:
        http://www.harfang.ch/expert/TimeCard.zip
    0
     
    LVL 58

    Expert Comment

    by:harfang
    As for the explanations, it is easier now that you have a sample. Anyway, you seemed to understand almost everything, so if I don't comment, it's ok... ;)

    * Indexes

    They are defined in table designe mode in the "view / indexes" sheet. An index can contain several fields, but only set of properties (which you see on the bottom when the index name is selected).

    * Validation rules

    Field validation rules are among the field properties.
    Table validation rules, using several fields, are visible in the table properties.

    The most basic field validation rule is to set "required: yes". It is always useful to put validation rules, when they make your life easier later. Using them, you will not have to worry about e.g. negative values, out of range measues, etc. The only problem is that they can use only pure SQL, not any VB or custom function. This is because they will be enforced by the JetEngine, even without Access present...
    Examples:
    (any time) between 0 and 1: refuse any entry containing a date portion (it would not be visible depending on the format and would break the calculations)
    (a value) Is Null or ... another rule: this is needed if the field is *not* to be required. Null is often equivalent to False!
    (for the form) dtmTimeOUT > dtmTimeIN: again, this makes sure the code further down works.

    More elaborate are "unique indexes" on one or several fields. This is also a kind of validation, as it will prohibit duplicates. Also very useful when coding.
    In our example, I set a unique index for date+employee+type, so that the "Lunch" exit will only be allowed once.

    Finally, you can validate a field from another table, by creating a relationship (open relationships from the database window). This way, the data contains only valid Types and existing Employees...
    This is what I meant with " lookup from tlkpClockTypes]", although the name is not very good... :(

    * "still concerned with calculating things"

    Yes, calculations accross records is complex. This is the reason I suggested staying with your structure... Well, this is leading us further that I thought... I provided a few examples, and added two "lunch time" queries for you.

    Still, this will not be always easy, be warned.

    * "TimeIN and TimeOUT fields should be TIME only?"

    We just had a long discussion with GRayL about that. He thinks that you'd be better off with the original proposal (date and time in the fields). I changed it in my example for two main reasons: Grouping by date is easier if the date portion is already separated, and the unique index for any "ClockType" on any given day is possible only this way. In the tests, I think I made the right choice, but that is up to you...

    * "Text(1)"

    This is just a text, with 1 as length. The index is faster on very short strings.

    OK, have fun. I will certainly not do that much testing for a question very often, so I hope it will help, even if you choose not to use any of it.

    Cheers!
    0
     
    LVL 4

    Author Comment

    by:Die-Tech
    harfang,

    All I can say is WOW!
    That is some nice work!

    I plan on using your database and adding in some of the other stuff from mine.

    I have some questions... of course.

    In the tklpClockTypes table, what is the "_", "1", "2" and "3" for?
    I see on the TIME CARD form at the bottom it says (*) special times not shown here
    Could you elaborate on this?

    I like the ? buttons to allow you to edit the employees time(s) for that day.
    I could probably use the same form for the employees to see how many hours they have for the day/week by disabling the buttons (so employees can't change their own time)

    I will, no doubt, have more questions.
    I will go ahead and open new questions to ask them though... you have done more than enough to earn this 500 points!

    Thank you very much,
    Die-Tech
    0
     
    LVL 58

    Expert Comment

    by:harfang
    Additional fields in "ClockTypes" (really not a good name) were just various tests. For example "_" for continuation over midnight, "1", "2" and "3" for "special" entries, etc. In the design I added a unique index preventing the use of the same type more than once a day. Therefore, you must make sure to have enough ways to accept an employee entry even on complex days... You will use a little less, I'm sure.

    "(*) special times "
    The TimeCard report is limited to the standard types: B, S, A, L, and D (ok, I added B... removing it will be a good excercise :). If other types have been used on the day, or if there are missing OUT fields, then the (*) is displayed. So this links to the previous question...
    By the way, this is done in the cross-tab queries (qxtb...) that can have a list of expected column headers.
    This is needed for form design, else you would have an error in case a given column is left out.

    Anyway, feel free to use what I sent you any way you like.

    Cheers :)
    0
     
    LVL 4

    Author Comment

    by:Die-Tech
    Thanks again harfang....

    If I change the tblEmployees lngEmpID field to a string field am I going to run into any problems that you know of?
    Our employee numbers are manually assigned (I don't know why... it was that way when I got here) and we have employee numbers like: 004, 007, 014, etc.



    Die-Tech
    0
     
    LVL 4

    Author Comment

    by:Die-Tech
    harfang,

    Help me Mr. Wizard!

    I changed the lngEmpID field to a text field of 3 characters.
    It took some work... but I was able to change out the employeeID's of 1, 2 and 3 to 166, 025 and 115
    I re-established the relationship (the same way it was before I started).

    Now I'm getting an error and I can't figure out where it's at.
    I think it's in the queries.... somewhere.

    When I open frmTimeCard, and choose an employee I get the following message:
         "This expression is typed incorrectly, or it is too complex to be evaluated.  For example, a numeric expression may contain too many
           complicated elements.  Try simplifying the expression by assigning parts of the expression to variables."

    After I click the OK button, I get this error message:
        "Object invalid or no longer set."

    I can open this as a new question if you want.  You deserve more points for all this work.
    I just want to make sure you are the one to help me with it.

    Let me know if I should open a new question.

    Thanks,
    Die-Tech
    0
     
    LVL 4

    Author Comment

    by:Die-Tech
    BTW.... I get these errors 7 times.  First the "This expression..." error, then the "Object invalid..." error, over and over 7 times.

    0
     
    LVL 4

    Author Comment

    by:Die-Tech
    I think I may have found the problem....

    There are 3 queries, qsumParamTimeCardTOT, qxtbParamTimeCardOUT, and qxtbParamTimeCardIN
    In the SQL view, you have PARAMETERS PayPeriod DateTime, EmployeeID Long;
    I changed the "Long" to String and then the query worked when I ran just the qxtbParamTimeCardOUT query.
    I went back in to look at the SQL again and Access changed it to the following:
        PARAMETERS PayPeriod DateTime, EmployeeID Text ( 255 );

    I've made copies of the queries just in case I'm on the wrong track.

    Let me know if I'm painting myself into a corner.

    Thanks,
    Die-Tech
    0
     
    LVL 58

    Expert Comment

    by:harfang
    >  I think I may have found the problem....

    Yes! Good job. Sorry I wasn't there, but then again it was good for you.

    In query design view, you can see the properties in "Query / Properties" (or from the context menu in the top pane). I set the PARAMETERS for two reasons:

    It forces the query to look for something called [PayPeriod] before executing (it's a separate task, separated by a semi-colon). It will find this in the calling query, calling form, or it will ask for it... So it is a little like declaring variables in a module.

    For dates, it is very important that the text input from the user (or the content of a control) is converted to a date early, i.e. not in a WHERE clause. This is the main reason I used them.

    Once everything is OK, you could remove the PARAMETERS, but I would leave PayDate at least.


    Another finer point...

    Using Text(3) as key is asking for trouble. You are setting up an "employee 1000" bug for the future, exactly like the y2k bug. If employee numbers are in fact numbers, use an Integer key (or Long key, it's really not a problem). You could get away with Byte, but then you set up an "employee 256" bug :)

    When you display the number, put "000" as format. That's all. When the employee 1000 hits, you will have mangeled display problems, but nothing serious. It's easier to change formats of an application than to change field sizes.

    Good Luck

    PS No need for another question. At least while we stay in the realm of my sample database.
    0
     
    LVL 4

    Author Comment

    by:Die-Tech
    harfang: Using Text(3) as key is asking for trouble. You are setting up an "employee 1000" bug for the future, exactly like the y2k bug. If employee numbers are in fact numbers, use an Integer key (or Long key, it's really not a problem). You could get away with Byte, but then you set up an "employee 256" bug :)

    DT: This could be the case... but so far, they swear to me they will never get that big.  They assign these 3-digit employee IDs and if someone leaves, their number is retired for the rest of the year but is eligible to be re-assigned next year.
    So in other words, I'll have to delete that employee number and all it's related records before it can be re-assigned.

    This might be a big problem, but I don't think they'll change that proceedure... they've been doing it like this for almost 20 years now.

    I did mess something up... :(
    Since I changed the lngEmpID field to a text field... I also decided I should keep up with the Lezynski (spellcheck?) naming convention and change the name of the field to: strEmpID

    I had all kinds of places to swap the names.  Mostly in the queries.  I got almost all of them.
    I'm getting the following error when I try to edit the day's time (when the form is suppose to popup.. it does popup, but is asking for lngEmpID)

    ----- Error Msg -----
    The expression On Click you entered as the event property setting produced
    the following error:
    The expression you entered has a function name that Microsoft Access can't
    find.

    * The expression may not result in the name of a macro, the name of a
    user-defined function, or [Event Procedure].
    * There may have been an error evaluating the function, event, or macro.
    ----- Error Msg -----


    I think it may be something to do with the fmodEditClockTimes code.

    It also may be something with this section:
        With Forms!frmTimeCard!EmployeeID
            Me.txtEmployeeName = .Column(1)
            Me.txtEmployeeID = .Value
        End With

    Another thing I changed was... On frmTimeCard form, I wanted the Employee ComboBox to show the employeeID and the name.  So I changed the Column Widths from 0" to 0.375";1.5"

    Now having said all that.... and re-reading your post again.... should I change things back to lngEmpID and add the "000" as the format instead?


    Thanks,
    Die-Tech
    0
     
    LVL 58

    Expert Comment

    by:harfang
    > "they will never get that big"
    If they reuse numbers, then it's really no problem.

    > lngEmpID vs. strEmpID
    It's really the same. the string version is one byte shorter, that's all I can tell. It also allows for expansions such as 100 "temp" employees "T01" to "T99" or employees "to delete", etc.
    In the last case, make sure to select "Cascade Update" in the relationships if you want to be able to edit the number of an existing employee!

    > fmodEditClockTimes: "does popup, but is asking for lngEmpID"
    The only place the string "lngEmpID" is found on that form/subform is as LinkChildField (property sheet of the subform control subEditClockTimes)

    Good Luck!



    0
     
    LVL 4

    Author Comment

    by:Die-Tech
    That fixed it!... it took me a little bit to find it.

    Now it's time to add in the form where it writes the IN/OUT times to the new database.....


    Thanks,
    Die-Tech
    0
     
    LVL 58

    Expert Comment

    by:harfang
    I hope this data model will suit your needs!
    Good Luck
    0
     
    LVL 4

    Author Comment

    by:Die-Tech
    harfang,

    I've got another question....

    I'm trying to take the following code and rewrite it.
    I want to use this for another form I have but I don't want it to ask me the PayPeriod or the EmployeeID

    --------- Code ----------------
    PARAMETERS PayPeriod DateTime, EmployeeID Text ( 255 ); SELECT
    qsumParamTimeCardTOT.*, qxtbParamTimeCardIN.dtmInB,
    qxtbParamTimeCardOUT.dtmOutB, qxtbParamTimeCardIN.dtmInS,
    qxtbParamTimeCardOUT.dtmOutS, qxtbParamTimeCardIN.dtmInA,
    qxtbParamTimeCardOUT.dtmOutA, qxtbParamTimeCardOUT.dtmOutL,
    qxtbParamTimeCardIN.dtmInL, qxtbParamTimeCardOUT.dtmOutD,
    qxtbParamTimeCardIN.dtmInD FROM (qsumParamTimeCardTOT INNER JOIN
    qxtbParamTimeCardOUT ON (qsumParamTimeCardTOT.strEmpID =
    qxtbParamTimeCardOUT.strEmpID) AND (qsumParamTimeCardTOT.datPayDate =
    qxtbParamTimeCardOUT.datPayDate)) INNER JOIN qxtbParamTimeCardIN ON
    (qsumParamTimeCardTOT.strEmpID = qxtbParamTimeCardIN.strEmpID) AND
    (qsumParamTimeCardTOT.datPayDate = qxtbParamTimeCardIN.datPayDate);
    --------- Code ----------------

    I've looked at the queries and I see that the qsumParamTimeCardTOT queries does not ask for the PayPeriod or the EmployeeID.  The qxtbParamTimeCardOUT and qxtbParamTimeCardIN do though.

    I would like the query to pickup the PayPeriod from a public function I setup.
    Here is the function: PayPeriodDate = Date - Weekday(Date) + 7
    I also need the query to pickup the EmployeeID from a textbox on the form.

    So PayPeriod should equal PayPeriodDate and EmployeeID should equal Me!txtEmployeeID

    I see some other queries called qxtbTimeCardOUT and qxtbTimeCardIN that don't ask for input.
    Should the above statement use the qxtbTimeCardOUT and qxtbTimeCardIN instead?  along with some kind of where statement?

    I need the form to use the above query where the PayPeriod and EmployeeID is passed to it so I can set the control source to a number of textboxes on the form to the following: dtmInS, dtmOutS, dtmInB, dtmOutB, dtmInL, dtmOutL, dtmInD, dtmOutD, dtmInA, and dtmOutA

    Hopefully you can help me rewrite this.

    Let me know if you have any questions or don't understand what I'm asking for.
    Once again, thanks for all your help.... let me know if I should open this up as another question.

    Thanks,
    Die-Tech

    0
     
    LVL 4

    Author Comment

    by:Die-Tech
    harfang,

    What I'm after is the dtmInS, dtmOutS, etc., etc. for the current day's date.  Date()

    Maybe that clarified things?


    Thanks,
    Die-Tech
    0
     
    LVL 58

    Expert Comment

    by:harfang
    Basically, the queries:
        qxtbTimeCardIN and qxtbTimeCardOUT --> qselTimeCard
    do not use parameters and do not contain lists of required columns (query properties: Column headings)
    While the queries:
        qxtbParamTimeCardIN and qxtbParamTimeCardOUT --> fsubTimeCardPayDay.RecordSource
    Do ask for parameters: [EmployeeID] and [PayDate], and the cross-tab queries contain a list of column headings.

    If you take the query out of fsubTimeCardPayDay, let's call it qselParamTimeCard. This will ask for the above parameters.

    When you use that query on a form, you will need to provide these two things. This is why I didn't use LRNC naming convention on the combo boxes of the form frmTimeCard. For example, instead of naming the employee ID combo "cboEmpID", I named it "EmployeeID". The query will thus use that *as* parameter.

    So, to have all the fields tdmInS, dtmOutS, etc. in one row, extract the recordsource of fsubTimeCardPayDay and use that in a new subform. On the main form, create a combo for the employee ID and a text box containing today's date. Either name the controls like the parameters, or feed then to the subform in the LinkMasterField property.


    No, wait. Let's be simple...

    Use qselTimeCard, and use it as you would any table. Find the record you want using its fields datPayDate and lng[str]EmpID. If you need to be certain of the columns returned, add a "column headings" property to the cross-tab queries qxtbTimeCardIN and qxtbTimeCardOUT (see an example in qxtbParamTimeCardIN).

    Basically, the query qselTimeCard can be used in place of your original table, with all times for a day as fields of one table. The only difference is that the query is read only.

    Good Luck
    0
     
    LVL 58

    Expert Comment

    by:harfang
    As for the query...

    I have created qsumFormTimeCardTOT:

    SELECT datPayDate, strEmpID,
       Sum([dtmTimeOUT]-[dtmTimeIN]) AS dtmWorked,
       Min(Not ([strTypeIN] In ('B','S','D','L','A') And [strTypeOUT] & '' In ('B','S','D','L','A'))) AS ysnSpecial
    FROM tblClockTimes
    GROUP BY datPayDate, strEmpID
    HAVING (datPayDate Between Date()-Weekday(Date())+1 And Date()-Weekday(Date())+7)
    AND strEmpID=[EmployeeID];

    The field ysnSpecial is there to indicate columns not appearing. You might want to delete that field.
    It provides for total time worked for all days in the current week, for employee [EmployeeID] You can replace that with Forms!MyForm!strEmpID if it has to run from somewhere else (e.g. a report)...

    Then, qselFormTimeCard

    PARAMETERS EmployeeID String;
    SELECT *
    FROM (qsumFormTimeCardTOT
       INNER JOIN qxtbTimeCardIN
       ON (qsumFormTimeCardTOT.strEmpID = qxtbTimeCardIN.strEmpID)
       AND (qsumFormTimeCardTOT.datPayDate = qxtbTimeCardIN.datPayDate)
       )
    INNER JOIN qxtbTimeCardOUT
    ON (qsumFormTimeCardTOT.strEmpID = qxtbTimeCardOUT.strEmpID)
    AND (qsumFormTimeCardTOT.datPayDate = qxtbTimeCardOUT.datPayDate);

    This does not need to be a query, it can be the RecordSource. It will show total time worked, all the fields (those selected in the Column Headiings).

    I used ysnSpecial in case you display only fields ShiftIn, ShiftOut, LunchIn, LunchOut, for example, but if the data allows for other cases. Then, even if the columns are not displayed, ysnSpecial indicates that (like in the form Time Card).

    Hope this helps
    0
     
    LVL 4

    Author Comment

    by:Die-Tech
    harfang,

    I took the qselTimeCard query, made a copy called qselFormTimeCard, in design view I added my criteria then went into SQL view and copied the code, then I pasted the code below into the record source of my form.

    SELECT qxtbTimeCardOUT.datPayDate AS datPayDate2, qxtbTimeCardOUT.strEmpID AS lngEmpID2, *
    FROM qxtbTimeCardIN INNER JOIN qxtbTimeCardOUT ON (qxtbTimeCardIN.strEmpID = qxtbTimeCardOUT.strEmpID) AND (qxtbTimeCardIN.datPayDate = qxtbTimeCardOUT.datPayDate)
    WHERE (((qxtbTimeCardOUT.datPayDate)=Date()) AND ((qxtbTimeCardOUT.strEmpID)='166'));


    I get the information I want on my form now..... I need to find a way to replace the '166' with a variable.

    I have a form called frmScanBadge with a text box called txtBadgeSwipe
    When the employee scans or swipes their badge, the textbox txtBadgeSwipe is populated and the After Update event does the following:
    Private Sub txtBadgeSwipe_AfterUpdate()
        DoCmd.OpenForm "frmTimeClock", , , , , acDialog 'The code now pauses until frmPassword is made invisible
        Me!txtBadgeSwipe = ""
    End Sub

    When the frmTimeClock form opens (this is the form I'm trying to get the SELECT statement above to work on) and displays the employee's time clock buttons (cmdClockIN, cmdLunchOUT, etc).  Under each command button is a textbox that is bound to the dtmInS, dtmOutS, etc from the SELECT statement.

    I've tried swapping out the '166' with [Forms]![frmScanBadge]![txtBadgeSwipe] but it didn't work.
    I've also tried txtEmployeeID to no avail.

    Any ideas on what should replace the '166' ?


    Thanks,
    Die-Tech




    0
     
    LVL 58

    Expert Comment

    by:harfang
    Try this:

    Private Sub txtBadgeSwipe_AfterUpdate()
        MsgBox "txtBadgeSwipe contains: """ & txtBadgeSwipe & """ value type: " & TypeName(txtBadgeSwipe.Value)
        DoCmd.OpenForm "frmTimeClock", WindowMode:=acDialog
        Me!txtBadgeSwipe = ""
    End Sub

    I am triying to establish wheter it is a number or text. Your approach seems fine, if you have no pop-up message when using:
        ........ AND ((qxtbTimeCardOUT.strEmpID)= Forms!frmScanBadge!txtBadgeSwipe ));
    it means that the SQL engine finds something there. But what does it find???

    It this doesn't work, you can try another approach: Leave out the test for the employee "number" (so that the form can potentially show all employees for today) and place the filter from outside:

        DoCmd.OpenForm "frmTimeClock", _
            WhereCondition:="strEmpID = '" & txtBadgeSwipt & "'", _
            WindowMode:=acDialog

    But the result should be the same, anyway...

    I would go with the Forms!etc syntax. Must be just a small problem. Maybe you could describe how "it didn't work"?
    0
     
    LVL 4

    Author Comment

    by:Die-Tech
    harfang,

    The MsgBox said: txtBadgeSwipe contains: "166" value type: String


    When I tried this:
    SELECT qxtbTimeCardOUT.datPayDate AS datPayDate2, qxtbTimeCardOUT.strEmpID AS lngEmpID2, *
    FROM qxtbTimeCardIN INNER JOIN qxtbTimeCardOUT ON (qxtbTimeCardIN.strEmpID = qxtbTimeCardOUT.strEmpID) AND (qxtbTimeCardIN.datPayDate = qxtbTimeCardOUT.datPayDate)
    WHERE (((qxtbTimeCardOUT.datPayDate)=Date()) AND ((qxtbTimeCardOUT.strEmpID)=Forms!frmScanBadge!txtBadgeSwipe));

    I got this error message:
    Run-Time error '3070':
    The Microsoft Jet database engine does not recognize
    'Forms!frmScanBadge!txtBadgeSwipe' as a valid field name or
    expression.

    When I tried this:
    SELECT qxtbTimeCardOUT.datPayDate AS datPayDate2, qxtbTimeCardOUT.strEmpID AS lngEmpID2, *
    FROM qxtbTimeCardIN INNER JOIN qxtbTimeCardOUT ON (qxtbTimeCardIN.strEmpID = qxtbTimeCardOUT.strEmpID) AND (qxtbTimeCardIN.datPayDate = qxtbTimeCardOUT.datPayDate)
    WHERE (((qxtbTimeCardOUT.datPayDate)=Date()) AND ((qxtbTimeCardOUT.strEmpID)='Forms!frmScanBadge!txtBadgeSwipe'));

    Note: I put apostophes around the Forms!frmScanBadge!txtBadgeSwipe

    I got this error message:
    Run-Time error '2105':
    You can't go to the specified record.


    I tried leaving out the test for the employeeID and used the "filter" code.
    This seems to work, but I'm getting a #Name? in the fields that do not have a time in them.


    Thanks,
    Die-Tech
    0
     
    LVL 58

    Expert Comment

    by:harfang
    Stangest thing... I just tried a dozen ways to include a reference to Forms!etc without any luck!
    So it seems we must work with parameters after all ...

    Before the query, add:
        PARAMETERS EmployeeID Text;
        SELECT qxtbTimeCardOUT......
        ....... AND ((qxtbTimeCardOUT.strEmpID)=[EmployeeID]));

    This would ask you the employee number. However, it will be run within a form, that will contain that value!

    On frmTimeClock, simply add a text box called EmployeeID with source: =Forms!frmScanBadge!txtBadgeSwipe

    This should cheat the query into accepting the number... Look at the bright side: you can use this query in any context where something called EmployeeID is present.

    If this works, you can also use the qxtbParam... variants. Simply add another textbox called PayDate with source: =Date() and add the PARAMETER in the main query (just like on frmTimeCard). The advantage is that the filtering is done at a lower level, when the indexes are still available. So this would be faster for long tables.

    Have faith! we will work this out!
    0
     
    LVL 58

    Expert Comment

    by:harfang
    Correction: (just like on fsubTimeCardPayDay), the main form doesn't have a recordsource...

    Good Luck!
    0
     
    LVL 4

    Author Comment

    by:Die-Tech
    harfang,

    I have another question out
    http://www.experts-exchange.com/Databases/MS_Access/Q_21202334.html

    If it's something you are familiar with, I'd like your opinion on it.


    Thanks,
    Die-Tech
    0
     
    LVL 4

    Author Comment

    by:Die-Tech
    harfang,

    I'm running into problems with this.

    I was thinking the frmTimeClock form would use the query to populate the textboxes when the form opens.
    But... each new day, there is no recordset so I get the following error:

    Run-Time error '2105':
    You can't go to the specified record.

    Is there a way to ignore this error and pull up some default settings?

    or would I be better off putting the query within the code and setting the textboxes from that?

    I think this frmTimeClock form is going to end up with two subforms instead of they way I'm doing it now.
    On the left side of the form is the "Time Clock" and it's related buttons, textboxes, labels, etc.
    On the right side of the form will be the "Job Clock" side which will use your "BadgeSwipe" and give you different buttons, textboxes, labels, etc.

    Thanks,
    Die-Tech
    0
     
    LVL 4

    Author Comment

    by:Die-Tech
    harfang,

    I did some experimenting...

    I put this in the OnOpen event of the form:
    Set rsLookup = db.OpenRecordset("SELECT qxtbTimeCardOUT.datPayDate AS datPayDate2, qxtbTimeCardOUT.strEmpID AS lngEmpID2, * FROM qxtbTimeCardIN INNER JOIN qxtbTimeCardOUT ON (qxtbTimeCardIN.strEmpID = qxtbTimeCardOUT.strEmpID) AND (qxtbTimeCardIN.datPayDate = qxtbTimeCardOUT.datPayDate) WHERE (((qxtbTimeCardOUT.datPayDate)=Date()));")

                If rsLookup.EOF And rsLookup.BOF Then
                MsgBox "1"
                Else
                MsgBox "2"
                'Me!txtBeforeShiftIN = rsLookup!dtmInB
                'Me!txtBeforeShiftOUT = rsLookup!dtmOutB
                Me!txtClockIN = rsLookup!dtmInS
                Me!txtLunchOUT = rsLookup!dtmOutL
                Me!txtLunchIN = rsLookup!dtmInL
                Me!txtClockOUT = rsLookup!dtmOutS
                'Me!txtDuringShiftOUT = rsLookup!dtmOutD
                'Me!txtDuringShiftIN = rsLookup!dtmInD
                'Me!txtAfterShiftIN = rsLookup!dtmInA
                'Me!txtAfterShiftOUT = rsLookup!dtmOutA
                End If

    I had to remark out some of the lines because I was getting the following errors
       Run-Time error '3265':
       Item not found in this collection.

    Is there a way to suppress these errors?

    I tried something like this:
    If IsNull(rsLookup!dtmOutD) then
        Me!txtDuringShiftOUT = ""
    Else
        Me!txtDuringShiftOUT = rsLookup!dtmOutD    
    End If

    But I get the same Run-Time error '3265'
    Obviously dtmOutD is not null.... it doesn't even exist in the recordset.
    Is there a way to trap for that?

    Thanks,
    Die-Tech
    0
     
    LVL 58

    Expert Comment

    by:harfang
    As I said elsewhere, there is one fundamental problem with cross-tab queries: they don't always return the same columns. If you use a cross-tab query as source for a form or report, you absolutely need to specify column headings in the cross-tab query.

    In qxtbTimeCardIN, I didn't. This way, all possible used columns appear, even those with new time in/out types I was playing with.
    In qxtbParamTimeCardIn, I did, because it would produce fields I wanted on fsubTimeCartPayDate. If you open the corss-tab in design view, you will see the columns user the query property Column Headings. The problem now is that other time in/ou types no longer show in any field. This is why I also created the "ysnSpecial" calculation, also based on a list of values, to show on the form that not all data was present.

    So, in your form, you can use the qxtbParam... queries, with controls named [PayDate] and [EmployeeID] and you will get the columns I chose in the cross-tab queries. If you use qxtb[non-param]... then you will have to provide column headings.

    Another solution alltogether, a little messy perhaps but I use it a lot, is to go like this:

        With rsLookup
            If Not .EOF Then

    On Error Resume Next    ' skip non-existing fields...

                Me.txtBeforeShiftIN = !dtmInB
                Me.txtBeforeShiftOUT = !dtmOutB
                ' etc...

    On Error Goto [....]  ' restore nomal error handling (goto 0 or goto Error_Label)

            End If
        End With

    This works because you assign the values to unbound controls by code. This would not work as .RecordSource for a form...

    Cheers!
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Course: From Zero to Hero with Nodejs & MongoDB

    Interested in Node.js, but don't know where to start or how to learn it properly? Confused about how the MEAN stack pieces of MongoDB, Expressjs, Angularjs, and Nodejs fit together? Or how it's even possible to run JavaScript outside of the browser?

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    884 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

    14 Experts available now in Live!

    Get 1:1 Help Now