Help With Grouping

[CCIDBMNG]

I'm not sure if this is the right place to place this thread but what I need to do is create a report with grouping.

The fields are:
Employee
Task
Description
Clients

The form is set up with drop down boxes for task and description and the clients is a multiselect listbox. This form was created by someone else and they have the clients being insterted with semicolons for example client1;client2;client4.

I need to create a report that groups by the client. But I need them grouped seperately so if there are 3 clients in that field then the record should be listed 3 times once for each client.

Does anyone know how to go about doing this? Please help. Or is there some other way to store the clients?

 

[WayneRyan]

CC,

What you really need to do is have:

tblEmployees:
EmployeeID - AutoNumber
EmployeeNumber
EmployeeOtherStuff

tblClients:
ClientID - AutoNumber
ClientName
ClientOtherStuff

tblTasks:
TaskID - AutoNumber
TaskName
TaskOtherStuff

tblWork:
WorkID - AutoNumber
EmployeeID - FK
ClientID - FK
TaskID - FK
WorkDate
WorkOtherStuff

Do you have the option of redesigning the tables?

It shouldn't be a difficult VBA routine to remap the data.

Wayne

 

[CCIDBMNG]

That is the way the database is set up. The table I originally described is like your table tblWork. The employee field is a number which is the employee ID field from the employee table. The task and description fields are set up the same way. The only one not set up like this is clients. The tables are setup fine. My problem is, is there are usually multiple clients which they are selecting from a mulit-select listbox. When the entry is added to the table it adds the clients like this... client1;client2;client3. I need to create a report which is grouped by all the clients. Right now if I create this report the grouping looks like this...

Client1;Client2;Client3
Employee Task Description

I need it to group like this...

Client1
Employee Task Description

Client2
Employee Task Description

Client3
Employee Task Description

 

[WayneRyan]

CC,

The only way that I can see to do this is to create a temporary
table:

Client, Employee, Task, Description

Then write some vba code to traverse the existing table and
enter new records into the temp table.

1. Delete from tblTemp
2. For each record in existing table
2A. Strip out "next" client, if none, goto step 3
2B. Write record to tblTemp
2C. Remove that client from list
3. Go to next existing record

If you need help with the VBA, let me know exact table structures.

Wayne