Prev: Multiple detail/record per break to reduce pages
Next: How do I create a trendline in a Microsoft Access Pivot Chart?
From: Dennis on 18 Mar 2010 21:43
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
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



Thanks,

Dennis

From: Allen Browne on 19 Mar 2010 00:31
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's 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.


"Dennis" <Dennis(a)discussions.microsoft.com> wrote in message
news:7514F434-76DE-4A4A-9B3F-FD173F45D14C(a)microsoft.com...
> 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
> 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
>
>
>
> Thanks,
>
> Dennis
>
 | 
Pages: 1
Prev: Multiple detail/record per break to reduce pages
Next: How do I create a trendline in a Microsoft Access Pivot Chart?