query help

[Bisha]

I have 2 tables :

Table1

Match Score
----------------------
Matchname1 1
Matchname2 X

Table2

Match 1 x 2
------------------------------------------
Matchname1 1.2 1.3 1.1
Matcname2 1.6 1.2 1.7

Need a query that's going to give me the Match, Score and Koeficent for that
score:

Query

Match Score Koeficent
----------------------------------------------
Matchname1 1 1.2
matchname2 2 1.7


Thanks in advance

 

[Hafeez Esmail]

Your tables aren't normalized.
This is task is difficult to do because the query would need to be redone
every time a new field in Table2 is added.

Read about "normalization" on microsoft's support site
HTH
Hafeez Esmail

 

[Tom Ellison]

Dear Bisha:

The suggestion made by Hafeez is valid. I will attempt to fill that out
somewhat and to offer some options.

The design for which you should strive would actually store the data in the
way you have requested in the appearance of the results you want.

First, please consider this step in the process:

SELECT Match, 1 AS Score, [1] AS Koeficent
FROM Table1

In addition, I believe you want these results as well:

SELECT Match, 2 AS Score, [2] AS Koeficent
FROM Table1

In order to get all these results in one query, combine the above with
UNION:

SELECT Match, 1 AS Score, [1] AS Koeficent
FROM Table1
UNION
SELECT Match, 2 AS Score, [2] AS Koeficent
FROM Table1

It is recommended you redesign the table to store the information in this
way. You can then use the above query to convert the data.

Add the column [x] as desired, adding another UNION SELECT to the above as
needed.

Tom Ellison