Conditional Sum Using Update Query 1

[dleewms]

I am using Access 2007. I am working with a database I DID NOT design and realize that it is designed poorly. However, I am not able to recreate it at this time.

Here is my dilemma.

The Workflow table has records of work done on workorders. One workorder can have multiple repairs. There are three RepairItem fields for each workorder record in the Workflow table. The Work form is linked to the Workflow table. There are three combo boxes on the Work form that has values from the Repair table. When a value is selected from the drop down, it updates the corresponding fiels in the Workflow table. This is working well. However, I need to be able to group and total the values in the workflow table on my report.

Here is an example.
WorkOrderNo, EquipmentType, RepairItem1, RepairItem2, RepairItem3
155, Laptop, Screen, Keyboard, Screen
156, Desktop, Motherboard, Screen, Keyboard
157, Laptop, Screen, Motherboard, Keyboard

On the report, I'm grouping by Equipment type. I want to have something like this:
Laptop Screen 3 Keyboard 2 Motherboard 1
Desktop Screen 1 Keyboard 1 Motherboard 1

I know it's convoluted. I'm open to any suggestions. I've considered using DSum to update a query and add a field in the Workflow table for countScreenSoFar, CountKeyboardSoFar, CountMotherboardSoFar, but not sure how to do this or if it is the best solution. Any assistance would be greatly appreciated.

Thanks!

 

[dhookom]

You should be able to use a union query to normalize your workflow table:

SELECT WorkOrderNo, EquipmentType, RepairItem1 as RepItem
FROM WorkFlow
WHERE RepairItem1 is Not Null
UNION ALL
SELECT WorkOrderNo, EquipmentType, RepairItem2
FROM WorkFlow
WHERE RepairItem2 is Not Null
UNION ALL
SELECT WorkOrderNo, EquipmentType, RepairItem3
FROM WorkFlow
WHERE RepairItem3 is Not Null

You can then create a totals query based on the union query that groups by EquipmentType and RepItem while counting WorkOrderNo.

If you really want the horizontal layout, you can use a main report based on EquipmentType and a multiple column subreport for the RepItem and counts.


Duane
Hook'D on Access
MS Access MVP

 

[dleewms]

Thank you very much Duane. I used your suggestion and created a union query to normalize the data and totalled on that.

Again thanks!

DLee