Results 1 to 4 of 4

Thread: Query

  1. #1
    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 much
    Last edited by dr223; 03-01-12 at 12:18.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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 (,).
    Have a nice day!

  3. #3
    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

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The 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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •