Report grouping question with 4 input

[jm112]

Hey guys,

What's the best way to do an "Assigned to" report with group headers for 4 variables?

A little more detail:

4 workers can work on a task. When the user enters the data, they can put the worker in any of the 4 fields.

cboWorker1
cboWorker2
cboWorker3
cboWorker4

All combo boxes containing the worker names is fed from the same table - tblWorkers and each combo box is storing the data type in a separate control - Worker1, Worker2, Worker3, Worker4, Task.

What's the best way to get this data to show in a report that display this data:

cboTask - Task1

cboWorker1 - John
cboWorker2 - Bob
cboWorker3 - Sean
cboWorker4 - Alex

cboTask - Task2

cboWorker1 - Bob
cboWorker2 - Rick
cboWorker3 - Alex
cboWorker4 - Guy

like this?

Alex

Alex, Bob, John, Sean - Task1
Alex, Bob, Guy, Rick - Task2


Bob

Bob, Alex, John, Sean - Task1
Bob, Alex, Guy, Rick - Task2


Guy

Guy, Alex, Bob, Rick - Task2


John

John, Alex, Bob, Sean - Task1


Rick

Rick, Alex, Bob, Guy - Task2


Sean

Sean, Alex, Bob, John - Task1


I looked into a query to combine the 4 combo boxes to into a multi-value string, but read that you should avoid using multi-valued data types at all costs.

Any help would be much appreciated. Been stuck on this for a couple of days. Thanks in advance!

 

[jm112]

Solved this using a union query. Not sure why I didn't think of it earlier.