Hi,
Here's the scenario:
I have 4 tables - Departments, Workgroups, Business and Transactions. Departments link to Workgroups using a "GroupId" field, Workgroups link to Business using "BusinessId" field. So for example:
"BEAUTY" is a business. It's BusinessID is 2
"BODY" is a Workgroup within Beauty. It's GroupId is 45
"MUSCLE MELT" is a Department within Body. It's departmentID is 113 and that is the number that appears in the "REC_SEAN" field in the main Transactions Table.
So if you know the DepartmentID you can analyse by Dept, Group Or Business.
The problem is that the DepartmentId in the department table is a single value e.g 150. Whereas the REC_SEAN field in the transaction table that the departmentid is joined to, can contain numerous department id's seperated by forward slashes eg 150/150/150 etc.
So when I run a query it will only find records that contain one instance of 150 and not those records which contain 150/150/150. Also the REC_SEAN field may contain different department id's eg 150/75/29 and I need to preserve this data so I can't just strip off all the values after the first department id.
Does anyone know how I can get round this??
Best Regards
Paul
Here's the scenario:
I have 4 tables - Departments, Workgroups, Business and Transactions. Departments link to Workgroups using a "GroupId" field, Workgroups link to Business using "BusinessId" field. So for example:
"BEAUTY" is a business. It's BusinessID is 2
"BODY" is a Workgroup within Beauty. It's GroupId is 45
"MUSCLE MELT" is a Department within Body. It's departmentID is 113 and that is the number that appears in the "REC_SEAN" field in the main Transactions Table.
So if you know the DepartmentID you can analyse by Dept, Group Or Business.
The problem is that the DepartmentId in the department table is a single value e.g 150. Whereas the REC_SEAN field in the transaction table that the departmentid is joined to, can contain numerous department id's seperated by forward slashes eg 150/150/150 etc.
So when I run a query it will only find records that contain one instance of 150 and not those records which contain 150/150/150. Also the REC_SEAN field may contain different department id's eg 150/75/29 and I need to preserve this data so I can't just strip off all the values after the first department id.
Does anyone know how I can get round this??
Best Regards
Paul