2 Replies Latest reply on Jul 13, 2010 7:11 PM by eibcga

    Payroll Fraud



      Payroll Fraud


      I need a small database for a fraud investigation for payroll regarding an employee.  Under normal business rules, one paycheck can only be related to one time card, and one time card can only be related to one paycheck.  When processing payroll, one paycheck can have many different pay days (e.g., seven days a week), but one particular pay day can only have one paycheque (Note: I am ignoring exceptions to this which have valid explanations — e.g., two paychecks are issued for the same pay day, one for regular hours only, another for overtime hours only).  So, normally there's a one-to-many relationship between paychecks and time cards.  It would be a mistake to simply summarize the non-normalized data by making a flat file or spreadsheet listing all paychecks, one per row, and time card details on each row of each paycheck, with a column for each pay day and paycheck detail, as it would cause problems when doing finds or reporting.  FileMaker should be used instead.

      What if fraud is involved for a particular employee?  A paycheck could relate to more than one time card, and a time card could relate to more than one paycheck, a many-to-many relationship.

      I resolve the many-to-many relationship by creating two, one-to-many relationships, using a join table called PAYDAYS or TIMECARD_DETAIL.  A paycheck can relate to many pay days, but a pay day can only relate to one paycheck.  As well, a time card can relate to many pay days, but a pay day can only relate to one time card.  I have this ERD:

      PAYCHECKS::_kpPayCheckID  l-----<  TIMECARD_DETAIL:_kfPayCheckID

      TIMECARDS::_kpTimeCardID  l-----<  TIMECARD_DETAIL::_kfTimeCardID

      Then I can run a report, or add a TIMECARD_DETAIL portal on each of the TIMECARDS and PAYCHECKS layouts, add the relevant fields to the portals, and see, among other things, which pay days (if any) have more than one paycheck, and which paychecks (if any) are paying for the same pay days.  As I mentioned above, the results could reveal that no fraud is occurring at all, or to what extent it is.  A very big task when all paycheques spanning over several years need to be investigated.

      Am I on the right track?  Thanks in advance.

        • 1. Re: Payroll Fraud

          You're on the right track as far as examining the data from both perspectives - paycheck & timecard.  A calculated field in each table, that counts the numbers of related records in the other table should help.  Of course, how does your method spot fraud when two timecards are used to pay someone for the same set of workdays?

          • 2. Re: Payroll Fraud

            I figured doing a Find on which pay days (if any) have more than one paycheck, would answer that, since every paycheck is related to a time card.  I can sort the results by pay day, and see if there are any duplicate pay days.

            Thanks for the tip.  I will have to test it with sample data.