Trouble getting SQL code written 1

[VickyC]

greetings

I am asking for help with a problem I can't crack. I'm trying to use SQL to produce tbl_B (or a query showing the same results) from tbl_T and tbl_A. I'm pretty sure I could do this with VBA, but the processing would be slow (the tables I am actually working with are considerably larger than the samples shown below).

Here's the problem. tbl_T is a TEMPLATE table. The values of t1 to t5 are always 1, 2, 3, 4, 5 in some order. These values tell us how to select values from tbl_A. In the 1st row of tbl_T, the values 5, 2, 4, 1, 3 mean that from tbl_A, we select the values of a5, a2, a4, a1, a3 IN THAT ORDER, then write these values into tbl_B as shown. For each row in tbl_T, I run through all records in tbl_A, so the final tbl_B will have 160 records.


[tt]tbl_T tbl_A
T_ID t1 t2 t3 t4 t5 A_ID a1 a2 a3 a4 a5
1 5 2 4 1 3 1 67 23 100 42 87
2 2 3 1 5 4 2 17 118 62 87 15
3 3 4 5 1 2 3 38 21 29 8 16
... etc ... etc
20 5 3 1 2 4 8 111 10 98 22 42
[/tt]


[tt]tbl_B
B_ID T_ID A_ID b1 b2 b3 b4 b5
1 1 1 87 23 42 67 100
2 1 2 15 118 87 17 62
3 1 3 16 21 8 38 29
... etc
160 20 8 42 98 111 10 22
[/tt]

I am really stuck on how to assign values from tbl_A to template values in tbl_T. I'd appreciate any clues!

Vicky

 

[SkipVought]

Wow! What's the business case for this kind of Cartesian join?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[VickyC]

I'm working on a scheduling research problem. The records in tbl_B show numbered events, and the records in tbl_T show various allowable ways to order these events.

While running the Cartesian join, my main problem is how to properly select tbl_A's values as per the order specified in tbl_T. Hope this makes sense!

 

[VickyC]

I am the original poster, but would like to narrow the focus of my question. There is only one aspect I need some help with.

I need a function to calculate the value of for b1, b2, b3, b4 and b5 in qry_B below. Even better would be one function to calculate the value of b(i) with i running from 1 to 5. I can't seem to get the syntax of this function right.

Here is one record from tbl_T and one record from tbl_A

[tt]tbl_T tbl_A[/tt]
[tt]T_ID t1 t2 t3 t4 t5 A_ID a1 a2 a3 a4 a5
8 5 2 1 4 3 12 67 42 9 17 112[/tt]


The corresponding record from the output would look like...

qry_B
[tt]T_ID A_ID b1 b2 b3 b4 b5
8 12 112 42 67 17 9[/tt]

Here's a WORD DESCRIPTION of what the function needs to do.

b1 = the t1^st value taken from a1, a2, a3, a4, a5
= the 5^th value taken from 67, 42, 9, 17,112
= 112

b2 = the t2^nd value taken from a1, a2, a3, a4, a5
= the 2^nd value taken from 67, 42, 9, 17,112
= 42

b3 = the t3^rd value taken from a1, a2, a3, a4, a5
= the 1^st value taken from 67, 42, 9, 17,112
= 67

b4 = the t4^th value taken from a1, a2, a3, a4, a5
= the 4^th value taken from 67, 42, 9, 17,112
= 17

b5 = the t5^th value taken from a1, a2, a3, a4, a5
= the 3^rd value taken from 67, 42, 9, 17,112
= 9


Any help with code for this function is really appreciated.

Vicky

 

[dhookom]

Does something like this work?

SELECT tbl_T.T_ID, tbl_A.A_ID, Choose([t1],[A1],[A2],[A3],[A4],[A5]) AS B1, Choose([t2],[A1],[A2],[A3],[A4],[A5]) AS B2, Choose([t3],[A1],[A2],[A3],[A4],[A5]) AS B3, Choose([t4],[A1],[A2],[A3],[A4],[A5]) AS B4, Choose([t5],[A1],[A2],[A3],[A4],[A5]) AS B5
FROM tbl_T, tbl_A
ORDER BY tbl_T.T_ID, tbl_A.A_ID;

I get results like:
[tt]
T_ID A_ID B1 B2 B3 B4 B5
1 1 87 23 42 67 100
1 2 15 118 87 17 62
1 3 16 21 8 38 29
2 1 23 100 67 87 42
2 2 118 62 17 15 87
2 3 21 29 38 16 8
3 1 100 42 87 67 23
3 2 62 87 15 17 118
3 3 29 8 16 38 21
[/tt]

Duane
Hook'D on Access
MS Access MVP

 

[VickyC]

dhookom - this is excellent. For some reason the Choose function didn't occur to me. I'd managed to work out a VBA solution that was clunky and slow. This is clean and fast. Thanks for taking the time.

Vicky