DataBase - total Amt Due, Taxes, Shipping, Expense and Total Amt Due on Invoi

Asked By Dennis

18-Mar-10 09:43 PM

Introduction:

This is similar to a posting I have in the Database group.  I tried to post
to two usenet groups for this question, but that did not work.

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.

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.


My Question:
My guess is I will not be able to do what I want, but I will be able to
group them using sub-report.  Even though it is not ideal, I could group my
invoice by labor, parts, and expense using sub-reports.

I will use the Work Order table for the main form and three sub-reports.
The first sub-report will use the Work Order Detail for the source.  The
second sub-report will use the Parts Detail for the source.  And the third
sub-report will use the Expense Detail as its source.

My question is how do I sub-total the different amount fields for each
sub-report and then for the final total?

The amounts for the Labor section are Time, Labor Rate, Amt Due (time *
labor rate).  I want to sub-total and total Time and Amt Due.

The amounts for the Parts sections are Quantity, Price, Extended Price,
Taxes, Total Amt Due.  I would sub-total Extended Price, Taxes, and Amt Due.
Some how I need to show total shipping charges also.

The amounts for the expense section are Expense Amt, Mileage, Mileage Rate,
and Total Expense Amt.  I would sub-total just the Total Expense Amt,
Mileage, and Expense Amount.

The bottom of the invoice would have the following totals:


Total Labor Amt Due = (total from Labor section)
Total Parts Due        = (total from Parts Extended Price)
Total Parts Taxes      = (total from Parts Taxes)
Total Shipping          = (total from Parts Shipping)
Total Expenses         = (total from Expense Amount)
Total Amt Due          = (total of the above)



Existing database structure:


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

19-Mar-10 12:31 AM

One work order can contain multiple line items.
You have 3 kinds of line items: labor, parts, expenses.

If possible, it would be ideal if you could keep all the lines in the one
WorkOrderDetail table. The fields would include something like this:
ID                  AutoNumber (primary key)
WordOrderID  Which work order this row belongs to.
EntryTypeID     Indicates if this row is for labor, parts, or expense.
EmployeeID    used only if the line is for labor.
PartID             used only if the line is for parts
Quantity          number of parts  or hours. Defaults to 1.
EachEx            Currency: price each part or each hour, without tax
TaxRate          Rate of tax to add to this row.

Now you can design a main form (for work orders) with a tab control that has
3 pages on it, to enter the labor, parts, and expenses. Each tab has a
subform bound to a query that draws only the relevant records from the
WorkOrderDetail table. The subform for labor has an EmmployeeID combo, but
no PartID combo. To the user, your interface looks like the values go into 3
different places (since that is how they think about the data), but they
actually end up on the one table. (You may have a 4th tab for shipping:
depends if you need to treat the differently.)

The advantage is that you can easily query the amounts from this one table.
In a query, you will have some calculated fields, e.g.:
Tax: CCur(Nz(Round([Quantity] * [EachEx] * [TaxRate],2),0))

Now you have the flexibility: if you want to create reports with a subreport
for each entry type, you can. But if you just want to use a main report and
use the sortin'n'grouping box to sort into sections with subtotals, you can
do it that way too.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

19-Mar-10 06:15 PM

Allen,

Ah, I see.  I should have made my WorkOrderDetail table more of transaction
table (for labor, parts, expense) than break out those three items into
different tables.

That is where I screwed up.  Thanks.  If I put all of the transactions in
one file, it make life so much easier.

Once again, thanks for you assitance.


Dennis