Timesheet & Invoicing Database

[manybeats]

Hi - I am trying to create a timesheet and invoicing database. Projects will be identified and then employees will enter their time and expenses, which will be associated with these projects. An invoice will then be created and will draw all the time and expenses based on a certain timeframe. I've set it up so that the employee enters their hours and the manager then enters the hours he wants to invoice for - "invoiced hourse" (which may be different if he runs out of budget and needs to invoice for less hours than the employee actually works).

My problem comes in because project types will either be lump sum contracts or time and materials contracts. I think the time and materials type is covered off, as noted in the above paragraph. But I'm not sure how to deal with lump sum contracts. Even if a project is lump sum, the time and expenses still need to be tracked so the company can see if it is making a profit on these projects.

I've thought about adding a field to my Project table identifying a lump sum amount, which would be zero for time and materials contracts. When the manager reviews the timesheets for lump sum contracts, he will put zero hours into the "invoiced hours" field and the invoice will simply pull up the lump sum amount as opposed to line items from the timesheets.

I'm wondering if there is a better way to deal with this, but can't think of how and have had no luck finding an answer on the forums.

I'm self-taught and working in Access 2010. I'm just planning this on paper now and have about 20 tables so far. Any help would be greatly appreciated.

Thanks!

 

[jdraw]

I think you have to get your tables and relationships sorted before you deal with data entry. If you try writing a 2-3 line description of things, you'll soon figure out what's different with "lump sum" and Timesheet totals etc.

Get the business rules sorted out before you get into real trouble.

There are some free data models at databaseanswers.org
that may be helpful

http://www.databaseanswers.org/data_models/customers_and_invoices/index.htm

 

[braden]

Good to know about your approach. However, we had been using Replicon's hassle free timesheet software solution which is indeed a easy to use and quick to implement cloud based application.

 

[manybeats]

braden - I took at look at what Replicon offers, but I don't think it will suit the needs I am looking for. Thanks anyhow!

 

[TheTerminator]

I have developed a similar system. What I did was...

All time and expenses are totalled up, from the expenses table, by a calc routine and the total stored in the main job table.

My main job job table has an additional field for Quoted Amount, which is usually left at zero.

In the case of a fixed quote job, the fixed quote amount is entered into the appropriate field.

At invoice time the program code invoices the fixed quote amount, if it is greater than zero, else it invoices the time and expenses amount.

Hope this helps and gives you something to think about.

 

[manybeats]

I've set my lump sum amount as you did your quote amount. I'm hoping it won't be too difficult to pull the correct information come time to produce the invoice. I'm pretty new to Access and every step seems to be a struggle these days!

I've been thinking about whether and how to store the total invoiced amount and your idea of storing it in the main job table makes a lot of sense. I think I will go that route.

Thanks for the help!

 

[TheTerminator]

What I've done in my system is invoice all items in full. For example, it totals up to a larger amount than the fixed quote amount.

I then calculate the difference, with a simple formula something like:

if nz(rsJob![QtAmt]) > 0# And nz(rsJob![QtAmt]) < rsInv![TAMOUNT] then
nDiff = rsJob![QtAmt] - rsInv![TAMOUNT] '(the invoice total minus the quoted amount as a negative amount)
endif

I then add an extra line to invoice which reads Discount allowed, Fixed Quote. for the negative amount.

Good public relations! The customer sees the total cost of the job and how much they saved be being charged their quoted amount.