Sub Reports

[pon]

I am joining 4 tables(workorder, charges, parts, labor) to get the results.
The results are as follows

WO1, CHR1, Part1, labor1
WO1, CHR1, Part1, labor2
WO1, CHR1, Part1, labor3

WO2, CHR2, Part1, labor1
WO2, CHR3, Part1, labor1
WO2, CHR4, Part1, labor1

WO3, CHR5, Part2, labor1
WO3, CHR5, Part3, labor1
WO3, CHR5, Part4, labor1

I want the results to be displayed by workorder but don't want the charges
or parts or labor to be repeated.

For WO1 I can use group by to get the results.

WO1, CHR1, Part1,
labor1, labor2, labor3 - Repeats for lobor

For WO2
WO2, CHR2,CHR3, CHR4 - Repeat for charge
Part1, labor1

For WO3
WO3, CHR5,
Part2, part3, part4 - Repeat for Part
labor1

I am not sure how to get these results. Please help

pon

 

[Allen Browne]

There is probably a better way to build these tables.

One work order may have many line items. This means there's you need 2
tables: one to store the header information about the job, and the second to
store the line items in that job.

You will therefore want tables like this:

a) WorkOrder table (the header information), with fields:
- WOID AutoNumber primary key
- ClientID relates to whoever this entry is for
- WODate Date/Time when this work was completed
- WODescrip Text or Memo what work was done

b) WorkOrderDetail table (the line items), with fields:
- WODetailID AutoNumber primary key
- WOID relates to an entry in the WorkOrder table
- Quantity Number (Double)
- PartID relates to an entry in your Parts table. Blank for
labor.
- EmpID relates to an entry in your Employee table. Blank for
parts.
- PriceEach Currency
In table design view, open the Properties box, and enter this beside the
Validation Rule in the Properties box:
([PartID] Is Null) XOR ([EmpID] Is Null)
This ensures that an entry is either for a part of for labor. (Be sure to
use the rule in the properties box: not the one in the lower pane of table
design, which refers to just one field.)

Now create a query using the second table. Type this expression into the
Field row:
Charge: [Quantity] * [PriceEach]
In the Criteria row under the EmpID field, enter:
Is Null
Save the query with a name such as qryWOParts

Create another query, with the same expression for Charge, and the same
criteria under the PartID field. Save as qryWOLabor.

Now you can create a main form bound to the WorkOrder table, and 2 subforms
bound to the 2 queries. These subforms will be in Continuous Form view, so
they display one item per line. In the first one, use a combo box for PartID
and omit the EmpID (since this subform is for parts.) In the other subform,
use a combo for EmpID, and omit PartID.

In the Form Footer section of each subform, add a text box and set its
Control Source to:
=Sum([Charge])
This gives the total for parts in the first subform, and the total for labor
in the second subform.