Table Values

[Rozza]

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

 

[tomschafer]

Add this to the criteria in your where statement....

instr(Transaction.REC_SEAN, Department.DepartmentID)>0

 

[Rozza]

Thanks for posting Pezamystik, sorry to be a pain but can you explain what the instr function does and how the line you posted works?

Thanks Alot!

Paul

 

[RiverGuy]

"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."


It's a logic issue. If you want to use Department ID as a foreign key, then use only 150.

The transaction table would be your junction table between two other tables.

Say you have Department # 5. You have transactions from Dept. #5. But the transactions can be from Business #1, or Business #2, or whatever.

Just do your views or queries to encompass specifically what you want to see.

It basically comes down to the fact that you need a separate transaction record for each department, or anytime another field is changed that is a foreign key.

To find one transaction, you would pull it up by transaction ID. To find all transactions with the same Dept. #, then all transactions with a specific Dept. #

 

[tomschafer]

Instr is a built-in function that returns the position of the first occurence of one string from another. i.e.

InStr("Pezamystik", "z&quot would return 3 and
InStr("bob", "b&quot would return 1 as the first occurence of b in bob is the first letter.

The reason this works for your problem is that it looks for Department.DepartmentID(75) inside Transaction.REC_SEAN(150/75/29). Which would be 5, which is greater that 0.

Make Sense?

 

[Rozza]

OK Pezamystik, I think I'm nearly there. When I run the query to find all transactions in a particular business, I search by "businessid" in the business table. You have stated that I need to insert the statement "instr(Transaction.REC_SEAN, Department.DepartmentID)>0" in the where statement, but I don't quite follow as I am basically doing a basic query specifying what to search for in the Criteria area of the field. Where does the "Where" statement fit in and which field does this apply to?

Once again thanks for your post!

Paul

 

[tomschafer]

Please post your SQL

 

[Rozza]

OK Here it is:

SELECT TRANSACTION.TRANSACTION_ID, Business1.BusinessID, TRANSACTION.SHOP_ID
FROM (Business1 INNER JOIN WorkGroups ON Business1.BusinessID = WorkGroups.BusinessID) INNER JOIN (Departments INNER JOIN [TRANSACTION] ON Departments.DepartmentID = TRANSACTION.REC_SEAN) ON WorkGroups.GroupID = Departments.GroupID
WHERE (((Business1.BusinessID)=1) AND ((TRANSACTION.SHOP_ID)=4));

Just disregard the transaction.shop_id, it enables me to search by a particular branch.


Cheers

Paul

 

[tomschafer]

Something like this should work....


SELECT
Department.DepartmentID,
TRANSACTION.TRANSACTION_ID,
Business1.BusinessID,
TRANSACTION.SHOP_ID
FROM
(Business1 INNER JOIN WorkGroups ON Business1.BusinessID = WorkGroups.BusinessID) INNER JOIN (Departments INNER JOIN [TRANSACTION] ON Departments.DepartmentID = TRANSACTION.REC_SEAN) ON WorkGroups.GroupID = Departments.GroupID,
Department
WHERE
instr(Transaction.REC_SEAN, Department.DepartmentID)>0 and
(((Business1.BusinessID)=1) AND
((TRANSACTION.SHOP_ID)=4));

 

[Rozza]

Thanks, I'll try it out on Monday!!

Cheers

Paul

 

[Rozza]

Hi Pezamystic,

Sorry to be a pain, but I have a problem. The statement that you gave me last week I understand completely, but it is not operating how I would have expected. It still only finds records where REC_SEAN exactly matches the Departmentid, when you run the query the "Instr" expression always returns the value of 1. Eg if The business ID is 2, then this relays that one of the values that department id could be is 91, then it only finds those records where REC_SEAN is 91 and not any other combination e.g 110/87/91 or 34/91/19 etc, etc.

Also there may be a flaw in the statement for example if I wanted to find all records where REC_SEAN was 10 then 110/46/29/84 would return a valid response as it would find the 10 within 110. This is not correct as this should be interpreted as 110, and would give an incorrect result.

I'm sorry to keep banging on about this particular problem, but I am now completely stuck!!

Best Regards

Paul Rowling

 

[tomschafer]

I kinda figured that'd happen. You really have no choice but to change your schema. And add in a table that will have a many to one relationship with both the Dpartment and Transaction tables.

 

[Rozza]

I thought that might be the case, thanks for all your advice though. I'll have to go back to the drawing board with this one!!!

 

[Guest_imported]

Hi. Try :

SELECT
Department.DepartmentID,
TRANSACTION.TRANSACTION_ID,
Business1.BusinessID,
TRANSACTION.SHOP_ID
FROM
(Business1 INNER JOIN WorkGroups ON Business1.BusinessID = WorkGroups.BusinessID) INNER JOIN (Departments INNER JOIN [TRANSACTION] ON Departments.DepartmentID = TRANSACTION.REC_SEAN) ON WorkGroups.GroupID = Departments.GroupID,
Department
WHERE
instr(Transaction.REC_SEAN, "\" & Department.DepartmentID & "\&quot>0 and
(((Business1.BusinessID)=1) AND
((TRANSACTION.SHOP_ID)=4));

 

[Guest_imported]

Oops, change the "\" to "/" in the instr part of the function. Should read :

instr(Trim(Transaction.REC_SEAN), "/" & Trim(Department.DepartmentID) & "/&quot>0 and

Regards

 

[Rozza]

Hi Mr Big,

Thanks for your post, I have tried it but it now doesn't find any matching records at all. The sql looks as follows:

SELECT Departments.DepartmentID, TRANSACTION.transaction_id, Business1.BusinessID, TRANSACTION.shop_id
FROM (Business1 INNER JOIN WorkGroups ON Business1.BusinessID = WorkGroups.BusinessID) INNER JOIN (Departments INNER JOIN [TRANSACTION] ON Departments.DepartmentID = TRANSACTION.rec_sean) ON WorkGroups.GroupID = Departments.GroupID
WHERE (((Business1.BusinessID)=1) AND ((TRANSACTION.shop_id)=4) AND ((InStr(Trim([Transaction].[REC_SEAN]),"/" & Trim([Departments].[DepartmentID]) & "/&quot)>0));

Any help would be greatly appreciated.


Cheers

Paul