Invoice database / report question.

Hi,

I’m having problems conceptualizing the correct structure for my database
and/or invoice form. I think the solution lies in both the database
structure and the approach to the invoice form. So I’m going to post in both
forums.

I’m not quite sure how to describe my issue because it is still a bit cloudy
on my part. So if I leave something out, it is because I have not connected
all of the dots yet.

My goal is to create an invoice in Access XP. The invoice will include one
or more line items for labor, parts, and expenses (mainly mileage, tolls,
etc.) We do not maintain an inventory. We purchase the parts as needed from
which ever vendor has the best price at that time. As a result, the parts
table is a little different from your norm.

Currently, I have the pieces, but I not quite sure how to put them together.


My existing database (which I’m not sure is correct) is

Work Order / Invoice Table -
The work order is the created once a customer wants something done.

Key Work Order # - automatic number
Data Customer Number – FK to Customer Table
Employee – FK to Employee Table
Call Date – date field
Resolution Date – date field
Original Issue - text
Resolution – text
Note – note field


Work Order Det Table -
There is one record for each time entry.
I’m also using this info to generate the Employee’s Time Sheet Report.
I also use this information as my notes of what was done on each job
for each customer.

Key WO Seq No – auto number
Data Work Order number - FK to Work Order Table
Work Data – Date – Work date
Hours – decimal – hours in quarter hour increments.
Issue – note field – what issue did the employee work on.
Resolution – note field – how the issue was resolved.

Expense Report Table:
Key ExpRptNo – Automatic number
Data Employee No – FK to Employee
Expense From Dt – date
Expense To Dt – dade
Comments - Notes


Exp Report Details

Key Expense Seq No – auto number
Data Work Order - FK to Work Order Table
Expense Date – Date
Expense Type – FK to Exp Type Table
Milage – decimal
Rate – milage rate
Expense Amount


Parts Table

Key Item No – automatic assigned number.
Data Work Order Number – FK to Work Order Table
Vendor No – FK to Vendor Table
Manufacturer – FK to Manufacturer table
Model No – text
Part Desc – text
Quantity
Price
Taxes
Shipping
Warranty Time Period
Notes

My goal is to have all three items (labor, parts, expense) for a given Work
Order / Invoice appear on a single invoice in some a reasonable order.
Ideally, I would have “associated� items appear together. For example, if I
have replace a power supply and a disk drive, I would like to have the labor,
part cost, and any expenses for the power supply appear together and the have
the labor, part, and any expense for the disk driver appear together and then
have a total.

Failing that, I could group my invoice by labor, parts, and expense using
subforms. Not idea, but it would be ok.


Any suggestions?


Thanks,

Dennis