Invoicing and Payroll (1 Viewer)

JPA

New member
Local time
Today, 05:18
Joined
Nov 28, 2018
Messages
9
I am trying to put together a database to track both invoicing and payroll for a small business. Things have gotten a little too complicated for Excel to keep up with easily.

I've spent several weeks taking a crash course from Google University, and I think I have the basic tables I need put together. However, I'm having some trouble setting up the appropriate relationships and data entry behaviors in Access.

I would like to be able to:
  1. Create a project (Done)
  2. Add separate line items for specific sub-projects (Done)
  3. Then assign some employees to those line items
  4. Select from that employee's available billing rates to use for that specific PO line item.


At this point I'm hung up on being able to assign an employee billing rate to a line item. I've managed to assign an employee to a line item, but I can't add that extra relationship to my form.


I think the next part will be fairly easy to work out once the above is setup, but the big picture goal is to be able to:
  1. Import our weekly timesheet info (I can do this)
  2. Calculate dollars spent for each PO line item (I think I can do this once the above is sorted out)
  3. Create Invoices (I'm not there yet :eek: )

Thanks in advance for the help!!

EDIT: I attached a simplified diagram of what I think the tables and relationships should look like.
 

Attachments

  • Tables.zip
    21.2 KB · Views: 195

plog

Banishment Pending
Local time
Today, 07:18
Joined
May 11, 2011
Messages
11,611
The relationship is wrong because it creates multiple paths between Project Line Items and Employees. There should only be 1 way to traverse between 2 tables in a relationship. So one of those relationships (if not tables) has to go.

My guess is Project to Emp is unnecessary because it has all the data Project Hours does. Just put the data in there and no need for Project to Emp.

Code:
At this point I'm hung up on being able to assign an employee billing rate to a line item.

For this you should reverse the position of EmpRates and Employees in your relationship. Employees should be at the far right, related only to Emp Rates which is joined to Project Hours.
 

JPA

New member
Local time
Today, 05:18
Joined
Nov 28, 2018
Messages
9
Wow! I got so hung up on needing a many-to-many relationship that it never occurred to me that there was a different way to structure this.

Thanks for the feedback! Let me give those changes a whirl and see if I can sort out how to make all that work.
 

JPA

New member
Local time
Today, 05:18
Joined
Nov 28, 2018
Messages
9
The relationship is wrong because it creates multiple paths between Project Line Items and Employees. There should only be 1 way to traverse between 2 tables in a relationship. So one of those relationships (if not tables) has to go.

After thinking a bit about this, I originally intended to use Emp ID, Contract Code, and WE Date as the keys for the Project Hours table. The reasoning was this table would be used to import data from the timesheet application, and I wanted to be able to ensure that each entry was unique, and past entries didn't get inadvertently overwritten.

Which leads to the issue. Because I'm using the Employee ID as a key in the Project Hours table, I don't think I can create a relationship to the Emp ID in the Emp Rates table. I've attached an updated diagram.

I think I have a fundamental knowledge gap somewhere regarding relationships in Access tables.
 

Attachments

  • Tables.zip
    21.1 KB · Views: 175

plog

Banishment Pending
Local time
Today, 07:18
Joined
May 11, 2011
Messages
11,611
The way you receive data shouldn't predicate how you store data. You build your properly structured tables and then you build a process to move data from external sources into it. This is where you would write queries to ensure data isn't lost.

The latest relationship you posted is incorrect. EmpID shouldn't be in Project Hours, AutoID from Emp Rates should be. You moved the tables, but didn't update how they relate.

If one goal is to determine employee cost, and employees have multiple rates, you need a relationship more specific than Line Item to Employees. Otherwise it will be indiscernable what rate needs to go to the line item.
 

JPA

New member
Local time
Today, 05:18
Joined
Nov 28, 2018
Messages
9
The way you receive data shouldn't predicate how you store data. You build your properly structured tables and then you build a process to move data from external sources into it. This is where you would write queries to ensure data isn't lost.

I was afraid you might say this. :) Time and attendance is handled by a third party app, and I'm extremely limited on what I'll be able to do with the output. But you are correct. I will try to sort out my database and then try to solve the data import problem.


If one goal is to determine employee cost, and employees have multiple rates, you need a relationship more specific than Line Item to Employees. Otherwise it will be indiscernable what rate needs to go to the line item.

This is where I think I'm stuck. Each line item has multiple employees, each employee has multiple rates, and each employee works on multiple projects. I would like to be able to set all those items for each line item, and then calculate costs automagically. I'm pretty limited in what I know how to do in Access, so I'm not sure how to tie all those items together other than a series of one-to-many relationships.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:18
Joined
Aug 30, 2003
Messages
36,118
Post 6 was moderated, I'm posting to trigger email notifications. I'm also deleting the duplicate.
 

plog

Banishment Pending
Local time
Today, 07:18
Joined
May 11, 2011
Messages
11,611
Can you post a sample file of the external data?
 

JPA

New member
Local time
Today, 05:18
Joined
Nov 28, 2018
Messages
9
Attached is a screenshot of an example. The data is just a spreadsheet with the Week Ending Date, the Last Name, The First Name, Employee Number, Earnings Code (straight time or OT), the Contract Code, and the Hours.

There are a few other fields, but they are unrelated to anything we do.

Thanks again for the help!!!
 

Attachments

  • ExampleTime.zip
    264.4 KB · Views: 207

plog

Banishment Pending
Local time
Today, 07:18
Joined
May 11, 2011
Messages
11,611
That data would fit into the model I outlined. You would import that data into a staging table, use a query to determine the AutoID from EmpRates and then populate ProjectHours with it.

With that said, are there only 2 types of Rates for employees? Overtime and Regular? Is Pay Rate and Billing Rate a function of those rates? For example, Billing Rate is always 1.5 * [EmpRate]? Is EmpRate really necessary?
 

JPA

New member
Local time
Today, 05:18
Joined
Nov 28, 2018
Messages
9
There are three types of Rates (the last being a fixed rate).

You are correct, Emp Rates is not necessary. I think that was a copy/paste error on my part. The intent is to have a Pay Rate and a Billing Rate for each employee.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:18
Joined
Feb 28, 2001
Messages
26,996
Not that it matters much to me from a personal viewpoint, but as a matter of clarification, ...

The intent is to have a Pay Rate and a Billing Rate for each employee.

This statement at least implies that your company does not have a fixed overhead rate. OK, it's your business model. Not only that, you can certainly bill at the rate of "all the traffic will bear" and I know a couple of companies that do that. Used to work for two of them at different times in my life. Therefore, the "all the traffic" overhead rate can only be a minimum at best. BUT if the overhead is actually fixed-rate or only based on the pay type, then you only need ONE entry for pay and can compute the other critical values knowing the hours and basis (ST/OT) and overhead for that basis.

I also noted that Straight Time and Overtime appear in two columns in that zipped Excel file. Surely, one of those must be redundant. It has to be in the same table that defines how many hours were worked on that basis, and I'll bet that you can only have a maximum of two entries per person per day - one Straight Time and one Overtime. Although in some cases I know of a third possible rate. In the USA, if the work were subject to certain National Labor board regulations, you Straight Time for 8 hours a day and Overtime for the next 4 hours. Then Special Overtime for any hours after 12 in the same calendar day. But in all cases, one labor-hours entry per person per pay type per day would still apply.
 

JPA

New member
Local time
Today, 05:18
Joined
Nov 28, 2018
Messages
9
99% of the time billing rates are fixed. But we have the oddball case here and there that I'd like to be able to deal with as well. Small projects we do at cost. That sort of thing. I'm not sure we'd charge what the traffic would bear even if we could - maybe that's why we have to keep working for a living :D

Multiple pay rates are to handle promotions and raises without affecting the calculated values for previous projects at the lower rates.

I was hoping to be able to make the database flexible enough to handle even the outliers. Maybe I've unnecessarily overcomplicating this by trying to handle that <1% of the cases?

You are correct about the excel file. It has redundant columns. The description matches the ST or OT column with the exception of spaces and formatting. I'm not sure why that is, but historically that last column is ignored. Also, that table is based on weekly values. So we often have more than two entries for a person as they will often work on more than one project in a week (or in a day for that matter). I can't think of time that OT ever showed up on multiple projects, but because this is reported for a week, I would think it's possible.

That said, I think if I can get my database structured so that I can assign multiple line items to a single project, then assign multiple employees to a line item, and finally assign a rate specific to that employee to that project, I should be able to sort out everything else from there..... Maybe :eek:
 

Users who are viewing this thread

Top Bottom