How to Gather TimeSheet Data to Create Invoice

[J. Trucking]

Hello,

I am building an employee timesheet database that will also be
utilized as an invoicing database. Whtat I have is a table/form setup
in which a user enters in an employees time. The employee will spend
the day on various different projects and will write their time sheet
accordingly. The time sheet table/form stores the Employee Name
(EmployeeID), the job they were working on (ProjectID), the hours
worked on that project (HoursWorked), and the cost code for the time
spent (CostCode....their are 50 different cost codes depending on what
the employee was doing such as calculations, construction surveying,
meetings, etc.). What I also have is an 'Invoice' Table/Form in which
a user can create an invoice for any period of time (such as a month)
and bill the client. I started figuring out how I would like to build
this (thinking a subform would work well) but I am running into a road
block. Here's what I'd like to accomplish:

A user creates an invoice and enters in a specific date range over
which they would like to bill. The invoice form will populate with
the appropriate Employee time sheet entries (showing the date worked,
the amount of time, the code, and the billing rate - which comes from
a field in the table that stores the Employee information) for that
project and time period. I am not sure how I would accomplish this as
the Time Sheet table doesnt store the billing rates because they can
frequently change.

Does anybody have any ideas on what would be an effective way to bring
up the data in a way that I can alter it and create an informative
invoice without damaging the TimeSheet records. What about running a
query and then populating a subform which would store the results from
the query in a totally new table... I have no idea how I would do it
but would it work?

Thanks in advance for any advice or help. I would also like to take
this oppurtunity to thank all of those who post to this group. Your
help is greatly appreciated.

John

 

[Allen Browne]

Hmm. Big question.

Part of the issue here is keeping track of which timesheet entries have been
previously invoices, and therefore which are the new ones still be to be
invoiced. To do this means you will need to tie specfic timesheet entries to
specific invoices.

One way to do that is to place an extra field in the timesheet detail table
to identify the invoice it belongs to. This field is blank until you create
the invoice, and then you fill in the InvoiceID.

At the end of the period, you ask for a closing date only (typically the
last day of the month), and select all the ininvoiced timesheets up to that
date (i.e. those where the InvoiceID is null.) Group them by the ClientID
for the ProjectID, and create one new record in the invoice table for each
client. Get this new InvoiceID, and assign it to all the timesheets for
projects of that client.

You now have the invoices, where the line items are actually the timesheet
rows, so you can easily prove the basis of the invoice's charges (i.e. who
worked on what project when.)

To achieve this, you will need to be familiar with VBA. Typically you
OpenRecordset() and create the invoices with AddNew and Update so you can
get the new invoice number. You can then exeucte an Update query to update
the timesheets with that invoice number. Assign a batch number to the the
invoices you created in one of these runs, so you can "undo" the invoice
creation (i.e. reset the foreign key InvoiceID to Null in the timesheets,
and then delete all the Invoice records in the batch.) You can use
cascade-to-null to achieve this:
http://allenbrowne.com/ser-64.html

 

[J. Trucking]

Thanks Allen,

I'll try working on that.

John