Help with UNION query?
Help with UNION query?
(OP)
hi
I have a huge table like the one at LEFT. For each ID, Rank runs from 0 to 4. The ID increments by 1 after each set of 5 records.
I want to insert a new record at the beginning of each group of 5 records. This is shown at RIGHT. For each of these new records, the last 3 fields are -1, 0, 0.
I'm guessing this involves a UNION query, but I'm rusty enough that I cant get it to work.
Any help is appreciated
Vicky
I have a huge table like the one at LEFT. For each ID, Rank runs from 0 to 4. The ID increments by 1 after each set of 5 records.
I want to insert a new record at the beginning of each group of 5 records. This is shown at RIGHT. For each of these new records, the last 3 fields are -1, 0, 0.
CODE
tbl_OLD tbl_NEW ~~~~~~~ ~~~~~~~ ID Rank X Y ID Rank X Y =========================== =========================== 1 0 18 96 1 -1 0 0 1 1 65 100 1 0 18 96 1 2 39 50 1 1 65 100 1 3 58 23 1 2 39 50 1 4 25 103 1 3 58 23 2 0 39 50 1 4 25 103 2 1 65 100 2 -1 0 0 2 2 18 96 2 0 39 50 2 3 58 23 2 1 65 100 2 4 25 103 2 2 18 96 3 0 18 96 2 3 58 23 3 1 89 34 2 4 25 103 3 2 25 103 3 -1 0 0 3 3 58 23 3 0 18 96 3 4 39 50 3 1 89 34 3 2 25 103 3 3 58 23 3 4 39 50
I'm guessing this involves a UNION query, but I'm rusty enough that I cant get it to work.
Any help is appreciated
Vicky
RE: Help with UNION query?
You can probably create a query to run once:
CODE --> SQL
Then order the results by ID and Rank
Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
RE: Help with UNION query?
Thanks, Vicky
RE: Help with UNION query?
CODE
INSERT INTO tbl_OLD ( ID, Rank, X, Y ) SELECT DISTINCT ID, -1, 0, 0 FROM tbl_OLD
---- Andy
There is a great need for a sarcasm font.
RE: Help with UNION query?
Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016