Help building array of strings based on table entries 1

[VickyC]

hi to all,

I have a table (tblMatchValues) with 120 rows like this:

ID A B C D E F
1 0 0 1 0 -3 -5
2 1 0 3 0 -1 0
3 ...etc

After each ID, there are 6 integers. Three are always 0 (representing teams playing at home), and each the other three (representing teams on the road) are 'paired' with a 0 as follows:

In the first row, 'A=0' pairs with 'F=-5' because 'F' moves 5 to the left to play 'A'. Similarly, 'B= 0' matches with 'E=-3', and 'D=0' matches with 'C=+1'. Each row follows this pattern.

I need to populate another table (tblMatchSQL) like this:

ID strSQL
1 "(A=0 AND F=-5) OR (B=0 AND E=-3) OR (D=0 AND C=1)"
2 "(B=0 AND A= 1) OR (D=0 AND E=-1) OR (F=0 AND C=3)"
3 ... etc

I've learned a lot from this forum, but coding this is still a little beyond my reach. I'd appreciate any help.

Thank you, Vicky

 

[SkipVought]

Hi,

FIRST and most important: NORMALIZE your data!
[tt]
ID Col Value
1 A 0
1 B 0
1 C 1
1 D 0
1 E -3
1 F -5
2 A 1
2 B 0
2 C 3
2 D 0
2 E -1
2 F 0
[/tt]
2 The simple query

SELECT
  P1.ID
, P1.Col AS Tm1
, P2.Col AS Tm2

FROM
  tblPairing2 P1
, tblPairing2 P2

WHERE P1.ID = P2.ID
  AND P1.Value=0
  AND P2.Value <>0
  AND ASC(P1.COL) = ASC(P2.COL) + P2.Value

3 the result
[tt]
ID Tm1 Tm2
1 D C
1 B E
1 A F
2 B A
2 F C
2 D E
[/tt]



Skip,

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

 

[SkipVought]

I'm guessing that that is what you needed -- pairings per season game.

I'd also guess that since generating the tblParings2 table normalization will be a ONE TIME occurrence, it could be very easily done in Excel using this process:

I did yours in a matter of seconds:

faq68-5287

Skip,

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

 

[VickyC]

Skip - that's very instructive. Thank you!
Vicky