Query

[dr223]

Hi,

I have 2 tables

TblTemp

z1 z2 z3 z4
0.20 0.30 0.40 0.50

TblBusType

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: 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

 

[plog]

With the power of math, this is a lot simpler than it appears. Simplify your formula (I added numbers after A & B to signify which column they are from):

(A1/B1)z1 x (A2/B2) z2 x (A3/B3) z3 x (A4/B4) z4
((A1/B1) x (A2/B2) x (A3/B3) x (A4/B4)) x (z1 x z2 x z3 x z4)

Since the z values are constant we can easily calculate that portion
z1 x z2 x z3 x z4 = .012
All rows will be multiplied by .012, so lets leave that off the statement we have so far:

(A1/B1) x (A2/B2) x (A3/B3) x (A4/B4)

To multiply fractions you multiply the numerators and denominators:

(A1 x A2 x A3 x A4)/(B1 x B2 x B3 x B4)

Since all A values are a row in your table, lets call the the left parenthesis R1 and do the same for the right parenthesis. Your equation now becomes:

R1/R2

This means we can work with each row indepently before bringing them together to do a final calculation. All you need to do is multiply every field in a row together, then take all those products and divide them by one another for all your permutations.

So build a query like this and call it ProductQuery:

SELECT [C1]*[C2]*[C3]*[C4] AS Product FROM TblBusType;

Then build another query based on 2 instances of that query like this:

SELECT ([ProductQuery].[Product]/[ProductQuery_1].[Product])*0.012 AS Result
FROM ProductQuery, ProductQuery AS ProductQuery_1;

That query is a cartesian product which means you have 2 tables and there is no link. You are bumping every row in one table against every row in the other table. A consequence of this is that you will get rows that essentially bump up against themself (i.e. row A will get divided by row A, row B will get divided by row B). All of those rows will evaluate to .012 (A/A*.012, B/B*.012). Also, you will get the inverse calculations (B/A*.012, C/A*.012). If you have an id field in your TblBusType field you can work around this.

 

[dr223]

Thank you so much.. A problem I have noticed..

(A/B)z1 = (A/B) to the power of z1
(A/B)z2 = (A/B) to the power of z2 and so on....

 

[plog]

Then throw away my entire previous post.

What field are you using in TblBusType to determine the order of that table? What way do you determine which rows shouldn't be calculated (i.e. B/A, C/F, etc.)?

 

[dr223]

Sorry plog, my mistake!!

TblBusType

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


So based on the above, what we are doing really is as shown below;



So, we have 4 business types A, B, C and D. Each business type has 4 Criterias.


Result1 = (A/B)z1 x (A/B)z2 x (A/B)z3 x (A/B)z4

Note: (A/B)z1 is represented as (A/B) to the power z1 and so on

Now, this is a combination of 4 business types now think I have 100 business types.
It wont be ideal typing each and every business calculation on the query.

Thanks

 

[plog]

I understand the formula, I don't understand if you want every permutation and if not, how to determine which permutation to exclude. Let's simplify your 4 original examples:

A
B
C
D

You said you wanted:

A/B
A/C
A/D
B/C
B/D
C/D

That omits these permutations: A/A, B/A, B/B, C/A, C/B, C/C, D/A, D/B, D/C, D/D. How do you determine which permutations to omit?

 

[dr223]

Ok,

1) a business type can not divide by itself..
Therefore, A/A, B/B etc are not valid.
2) In TblBusType - you select the top business on the list working your way downwards. It's a more one way flow, the reverse is not required..

I hope I have helped in the explanation.. I cant figure how to attain this..

Thank you

 

[plog]

It didn't because there is no order in a table (no first, no last, no next, etc). Order only exists in a query when you explicitly give it an order using an ORDER BY clause.

What field in your table can be used to order your data in the manner that you want?