Help needed using SQL to fill a table 1

[teach314]

hello
I'm having trouble writing SQL to do the following...

Given qryRawData:

SET TH TA
1 A C
1 B F
1 E D
2 D B
2 F A
etc...

This query has 3 rows for each SET (1 to 120), giving 360 rows.
Each SET will use the distinct letters A to F, three from column TH and three from column TA.

Using this query, I need to write SQL to fill the entries of a table (tblOrderedData) like this...

PK is Autonumber

PK SET GRP Code
1 1 A 0
2 1 B 0
3 1 C -2
4 1 D 1
5 1 E 0
6 1 F -4
7 2 A 5
8 2 B 2
etc...

The rules are:
a) in the table, there are 6 rows for each SET value.
b) the GRP values are always A to F, in ascending order.
c) if GRP came from the query's TH field, Code = 0.
if GRP came from the query's TA field, Code = ASC(TH)-ASC(TA).

I can do all of this in VBA, but I'd really like to see an SQL solution. It's a bit beyond my skill level.

Thank you

 

[PHV]

What about this ?

INSERT INTO tblOrderedData (SET,GRP,Code) SELECT * FROM
(SELECT SET, TH AS GRP, 0 AS Code FROM qryRawData
UNION SELECT SET, TA, Asc(TH)-Asc(TA) FROM qryRawData) U

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[teach314]

hi PHV

Your solution was perfect! For my own education, I'd like to ask a few followups:

a) Your solution shows the values of GRP in the table to be listed in the correct order (A to F, ascending) for each value of SET. But, I don't understand why this is so (I would have thought you'd have to use an ORDER BY SET, GRP line). The non-ordered results of the 2 parts of the subquery were 'UNIONed', yet the output is ordered properly.

b) Your SQL ends by calling the subquery 'AS U'. I didn't think this was necessary, but when I took it out, another Alias is assigned automatically. Why is that?

Much thanks for an efficient and fast solution to my original question.