1. Registered User
Join Date
Jul 2009
Posts
23

Hi,

I have 2 tables

TblTemp

z1 z2 z3 z4
0.20 0.30 0.40 0.50

TblBusType

BusinnesType C1 C2 C3 C4
A 1 2 3 4
B 2 3 4 5
C 3 4 5 6
D 4 5 6 7

Now, I need to compute the following;

(A/B)z1 x (A/B) z2 x (A/B) z3 x (A/B) z4 as result1
(A/C) z1 x (A/C) z2 x (A/C) z3 x (A/C) z4 as result2
(A/D) z1 x (A/D) z2 x (A/D) z3 x (A/D) z4 as result3
(B/C) z1 x (B/C) z2x (B/C) z3 x (B/C) z4 as result4
(B/D) z1 x (B/D) z2x (B/D) z3 x (B/D) z4 as result5
(C/D) z1 x (C/D z2 x (C/D) z3 x (C/D) z4 as result6

the above can also be represented with values as

(1/2)0.20 x (2/3)0.30 x (3/4)0.40 x (4/5)0.50 as result1
(1/3)0.20 x (2/4)0.30 x (3/5)0.40 x (4/6)0.50 as result2
(1/4)0.20 x (2/5)0.30 x (3/6)0.40 x (4/7)0.50 as result3
(2/3)0.20 x (3/4)0.30 x (4/5)0.40 x (5/6)0.50 as result4
(2/4)0.20 x (3/5)0.30 x (4/6)0.40 x (5/7)0.50 as result5
(3/4)0.20 x (4/5)0.30 x (5/6)0.40 x (6/7)0.50 as result6

Note: (A/B)z1 = (A/B) to the power z1
(A/B)z2 = (A/B) to the power z2 and so on .. Couldnt represent it in this forum the power too...
Note: This particular example is based on 4 records A, B, C & D. I will want to do it for nearly 200 combinations. Therefore, the query should accomodate more records.

Thank you very much
Last edited by dr223; 03-01-12 at 11:18.

2. Moderator
Join Date
Mar 2009
Posts
5,442
1. We'll first create a query based on [TblBusType] that will provide a numeric ranking order:
Code:
```SELECT ( Select Count([BusinnesType])
FROM [TblBusType] AS a
WHERE a.[BusinnesType]<=[TblBusType].[BusinnesType]
) AS Cnt,
TblBusType.BusinnesType,
TblBusType.C1,
TblBusType.C2,
TblBusType.C3,
TblBusType.C4
FROM   TblBusType;```
This query yieds:
Code:
```Cnt	BusinnesType	C1	C2	C3	C4
1	A		1	2	3	4
2	B		2	3	4	5
3	C		3	4	5	6
4	D		4	5	6	7```
Note: If the values of the column [BusinnesType] provided in your sample data are the actual values, this is not absolutely necessary.

2. We shall now combine this query with itself (almost a Cartesian product) but with an offset of one line. In the same query we'll perform the divisions:
Code:
```SELECT A.BusinnesType,
B.BusinnesType,
A.C1,
B.C1,
A.C1/B.C1 AS ABC1,
A.C2,
B.C2,
A.C2/B.C2 AS ABC2,
A.C3, B.C3,
A.C3/B.C3 AS ABC3,
A.C4, B.C4,
A.C4/B.C4 AS ABC4
FROM ( SELECT ( SELECT Count([BusinnesType])
FROM [TblBusType] as a
WHERE a.[BusinnesType]<=[TblBusType].[BusinnesType]
) AS Cnt,
TblBusType.BusinnesType,
TblBusType.C1,
TblBusType.C2,
TblBusType.C3,
TblBusType.C4
FROM TblBusType
) AS A,
( SELECT ( SELECT Count([BusinnesType])
FROM [TblBusType] as a
WHERE a.[BusinnesType]<=[TblBusType].[BusinnesType]
) AS Cnt,
TblBusType.BusinnesType,
TblBusType.C1,
TblBusType.C2,
TblBusType.C3,
TblBusType.C4
FROM TblBusType
) AS B
WHERE (A.Cnt<B.Cnt);```
This query yields:
Code:
```A.BusinnesType	B.BusinnesType	A.C1	B.C1	ABC1	A.C2	B.C2	ABC2	A.C3	B.C3	ABC3	A.C4	B.C4	ABC4
A		B		1	2	0,5	2	3	0,66	3	4	0,75	4	5	0,8
A		C		1	3	0,33	2	4	0,5	3	5	0,6	4	6	0,66
B		C		2	3	0,66	3	4	0,75	4	5	0,8	5	6	0,83
A		D		1	4	0,25	2	5	0,4	3	6	0,5	4	7	0,57
B		D		2	4	0,5	3	5	0,6	4	6	0,66	5	7	0,71
C		D		3	4	0,75	4	5	0,8	5	6	0,83	6	7	0,85```
Notes:
a) I trucated the decimal values in the array.
b) I kept extra (i.e. not actually used for the solution) columns to better explain the mechanism.
c) I used a simple WHERE clause, which is easier to understand, but the combination should be done using an INNER JOIN operation.

3. We can now bring the second table ([TblTemp]) into the system and perform the final computations:
Code:
```SELECT A.BusinnesType,
B.BusinnesType,
([A].[C1]/[B].[C1])^[TblTemp].[z1] AS ABC1z1,
([A].[C2]/[B].[C2])^[TblTemp].[z2] AS ABC2z2,
([A].[C3]/[B].[C3])^[TblTemp].[z3] AS ABC3z3,
([A].[C4]/[B].[C4])^[TblTemp].[z4] AS ABC4z4
FROM ( SELECT ( SELECT Count([BusinnesType])
FROM [TblBusType] as a
WHERE a.[BusinnesType]<=[TblBusType].[BusinnesType]
) AS Cnt,
TblBusType.BusinnesType,
TblBusType.C1,
TblBusType.C2,
TblBusType.C3,
TblBusType.C4
FROM TblBusType
) AS A,
( SELECT ( SELECT Count([BusinnesType])
FROM [TblBusType] as a
WHERE a.[BusinnesType]<=[TblBusType].[BusinnesType]
) AS Cnt,
TblBusType.BusinnesType,
TblBusType.C1,
TblBusType.C2,
TblBusType.C3,
TblBusType.C4
FROM TblBusType
) AS B,
TblTemp
WHERE (A.Cnt<[B].[Cnt]);```
This third query yields the desired values:
Code:
```A.BusinnesType	B.BusinnesType	ABC1z1		ABC2z2		ABC3z3		ABC4z4
A		B		0,870550563	0,885467493	0,891301229	0,894427191
A		C		0,802741562	0,812252396	0,81519311	0,816496581
B		C		0,922107911	0,917314755	0,914610104	0,912870929
A		D		0,757858283	0,759657793	0,757858283	0,755928946
B		D		0,870550563	0,8579172	0,850283	0,845154255
C		D		0,944087511	0,935248448	0,929667185	0,9258201```
Final note: On my system, the decimal separator is the comma (,).

3. Registered User
Join Date
Jul 2009
Posts
23
Thank you so much Sinndho..

But am looking for a kind of array for example within the code.. am working with Microsoft access 2003.

This is because if I have 200 businessTypes, I cant be typing all the business types and permutate there combinations, it will be just impossible..

many thanks

4. Moderator
Join Date
Mar 2009
Posts
5,442