Adding rolling sums to a payroll db

[snipe]

Hey guys,

I have been redesigning the payroll program that my company uses. Currently, they do everything in excel. About half of what they do can be automated (and more accurate) if its done in access. Everything has been going great until now.

Our salespeople are 100% commission. If, during a single 2-week pay period, a sales person doesn't sell enough to make more commission than he would at minimum wage for the hours he worked, he is "on draw." For instance, if a salesperson sells $6000 of merchandise in a 80 hour pay period and gets 6% commission, he would make $360 in commission. Legally, we have to pay our sales associates at least minimum wage. 80 hours times $7.25 an hour is $580. This associate would be $220 in draw. He borrows that money from the company, and it must be paid back.

Currently, this is easy to track in excel. The farthest right column of a pay period record for a sales person is labeled "Accrued Draw" and is calculated automatically based on Total Commission and Hours worked. The next week adds that Accrued Draw value to a different running sum column "Draw Balance." That field is added to the Accrued Draw field for the next pay period. I hope I explained that clearly enough.

Each salesperson gets their own sheet in their stores excel file. This leads to 10 excel files, each with 20-30 sheets.



In Access, I have 1 master table, it includes all stores and all salespeople. By use of queries, it is super simple to input data and run reports based off this 1 table. I just can't for the life of me get the draw to work.

I was thinking of using a completely separate table and linking the 2 tables by the employee ID. But I still don't really know how to make this work.

[June7]

That doesn't make sense - they have to 'pay back'? If they have to 'borrow and pay back' then they aren't earning minimum wage. That's legal? Isn't commission supposed to be over and above minimum wage, not part of the wages? Don't answer that, don't want to debate legal interpretation but it just struck me as odd.

Yes, running sum in Access can be tricky. Textbox on report has a RunningSum property that might accommodate your need. If not, review: http://support.microsoft.com/kb/290136

[snipe]

I'll give the DSum() function a try. DLookup always gives me a hard time, so I hope this one is different.

All of our sales people are on pure commission, no base pay. They get between 5-7% commission on how much they sell. Our top writers write over $2M a year. But since we do not have a base pay, if a salesperson has a terrible week, their commission wouldn't pay them enough to earn minimum wage. We pay them their draw and when they have a week where their commission is high enough above minimum wage, they pay it back. Normally, if salesperson is on draw 2 pay periods in a row, they are on their way out the door.