Results 1 to 4 of 4
Thread: Query

030112, 08:12 #1Registered User
 Join Date
 Jul 2009
 Posts
 23
Unanswered: Query
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 muchLast edited by dr223; 030112 at 11:18.

030112, 16:29 #2Moderator
 Join Date
 Mar 2009
 Posts
 5,442
Provided Answers: 141. 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;
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
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);
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
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]);
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
Have a nice day!

030212, 11:16 #3Registered 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..
any ideas please
many thanks

030212, 12:46 #4Moderator
 Join Date
 Mar 2009
 Posts
 5,442
Provided Answers: 14The query won't change if there are more rows in the [BusinnesType] table, it will simpy return more rows. You would need to change the query only if the number of columns vary.
As far as getting an array is concerned, this is not very difficult once you have the query: Open a Recordset on it and use the GetRows method.Have a nice day!