Results 1 to 7 of 7
  1. #1
    sjames.au is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    4

    Question Redistribution of wage costs database design and allocation expense corrections.

    Hi all.

    I would appreciate some assistance on where to start and even a recommended solution as I am stumped. I am also learning. I am trying to build a database which fills the gaps between several major systems which deal with rostering and payroll and ultimately the general ledger. These systems leave me with massive variances to correct via journal so I'm hoping to build a database which will turn days of work into hours or even minutes. ?

    I have started to build a database which imports the ledger transactions for each pay run, retains the establishment/position list and the default staff pay location and relevant percentage.

    I have the added following tables:
    Employees (EmployeeID)
    EmployeePosition (a Many to Many relationship between EmployeeID and PositionID)
    Establishment (PositionID)
    DefaultChargeCode (m2m to PositionID - which permits allocation of multiple charge codes to distribute a percentage of employee costs to different business areas)
    GeneralLedger (GLI PK, PosID)

    At the moment I am running a make table query to extract correct and incorrect charges based on the ChargeCode table. Basically the correct charges stay the same and incorrect charges are altered only to move debit charges to the credit column to reverse the transaction.

    So, the current (probably not ideal design) is for correct entries to be saved into another GLICorrect(pk) table and incorrect entries into a GLIIncorrect(pk) table (all transactions retain the original primary key number from the GeneralLedger table to avoid duplications)

    I have a FinanceJournal table which I need to house the GLIIncorrect entries and summarised corrections but because our primary systems are so inaccurate there are usually several incorrect charge lines to every correct line, so I need to leave the incorrect line as it is so the journal reverses the original charge and then sum the total reversal based on a up to six table columns which creates the account string to apply the employee charge to the correct allocated position charge code.

    The final blue sky dream outcome would be to be able in some instances to apply position based percentage splits for those roles which are shared and paid for by different areas of the business.


    Eg. for role 2877 the payroll charges will be applied to different business area charge strings.

    ChargeCodeID PosID EntID FundID CostCtrID ActivityID ProjectID InitiativeID Percentage
    573 2877 11 210 3660 1012 00000 000 25
    574 2877 11 210 3662 1012 00000 000 25
    575 2877 11 210 3664 1012 00000 000 25
    576 2877 11 210 3666 1012 00000 000 25

    The table leaves out the account code which will potentially add tens of additional lines to the charges to be distributed to those individual lines.



    Hopefully the above is clear as I am never sure if I have provided too little info or too much.

    Thanks :-)

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You may get some ideas from these at Barry Williams' site

    Accounting System facts
    Accounting System data model

    Good luck.
    Last edited by orange; 01-11-2020 at 06:27 PM. Reason: Fixed the link to the data model

  3. #3
    Mickjav is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    Margate, Kent
    Posts
    123
    Is the payroll system an access program?

    why are there: incorrect entries into a GLIIncorrect in a payroll system'

    can the payroll if access be updated instead of adding another system that will need mantaining.

    It seems to me your adding systems to correct other system maybe those systems should be audited to see if they are actually doing the job properly.

    mick

  4. #4
    sjames.au is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    4

    Duplicate System

    Quote Originally Posted by Mickjav View Post
    Is the payroll system an access program?

    why are there: incorrect entries into a GLIIncorrect in a payroll system'

    can the payroll if access be updated instead of adding another system that will need mantaining.

    It seems to me your adding systems to correct other system maybe those systems should be audited to see if they are actually doing the job properly.

    mick
    Hi Mick

    The systems are not access. They are enterprise systems, their data won’t be updated, and this system is indeed a stand alone system to correct problems separate so problems will remain as they are until the Enterprise systems are fixed (I am part of this process) BUT this will take a long time so I have decided I need a personal system to fix problem charging to allow me to get on with other work. These corrections have been done historically in excel and can take days or weeks to resolve.

    Have you got any suggestions for the issues I have with my database? I am a little stuck for the solution.

    Thx
    Simon

  5. #5
    sjames.au is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    4
    Thanks Orange

  6. #6
    Mickjav is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    Margate, Kent
    Posts
    123
    Post a copy of your spreedsheet with some sample date not live data.

    I don't really understand why you would use a system that doesn't seem to work but I'll help where I am able.

    mick

  7. #7
    sjames.au is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    4
    Thanks Mick, I’ll make a small dB will mock data if that helps later today. Cheers

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Expense database design help needed
    By hira_iftikhar in forum Access
    Replies: 2
    Last Post: 02-17-2016, 05:06 PM
  2. Replies: 2
    Last Post: 11-16-2015, 08:05 AM
  3. Replies: 3
    Last Post: 02-25-2015, 10:02 PM
  4. tracking multiple wage types
    By Helystra in forum Database Design
    Replies: 1
    Last Post: 10-31-2013, 11:23 AM
  5. How to show monthly % allocation in database
    By Aneta in forum Database Design
    Replies: 11
    Last Post: 08-25-2011, 08:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums