query with group by

[kiblinger]

I have some data in a table in an MDB and I am building a query in MS Access. Seems simple but so am I

I have a structure that is essentially always like this for all my records (A-F in this simplified sample):

SPU1 SPU2
A 1 NULL
B 1 NULL
C 1 2
D 1 2
E 2 NULL
F 2 NULL



I want to move the values in the E and F records like this:

SPU1 SPU2
A 1 NULL
B 1 NULL
C 1 2
D 1 2
E NULL 2
F NULL 2


The basic idea in my head is:

Step 1: Get all records where SPU2 IS NULL and GROUP BY SPU1

Step 2: Get all records where SPU2 IS NOT NULL and GROUP BY SPU2

Step 3: Take the list of unique SPU2s from Step 2. Get the first one, say, "SPU2=2," and look through the results from Step 1 and find where SPU1 = "SPU2=2"

Step 4: Move that SPU1 code from the SPU1 field to SPU2, and make SPU1 NULL

Step 5: Repeat Steps 3 and 4, working through the entire list from Step 2


Thanks in advance!

 

[Remou]

From the above, it seems that you wish to update SPU1 to SPU2 where SPU1 is null and SPU2 is not null. Is this correct?

 

[kiblinger]

I think my original example was too terse. Combine my original description with this more complete example:

I have things like this:

	SPU1		SPU2
A	1		NULL
B	1		NULL
C	1		2
D	1		2
E	2		NULL
F	2		NULL
G	2		3
H	3		NULL
I	3		NULL
J	4		3
K	4		NULL
L	5		4
M	5		NULL
N	5		NULL

I want to change it to this:

	SPU1		SPU2
A	1		NULL
B	1		NULL
C	1		2
D	1		2
E	NULL		2
F	NULL		2
G	3		2
H	3		NULL
I	3		NULL
J	3		4
K	NULL		4
L	5		4
M	5		NULL
N	5		NULL

 

[Golom]

In words, you want to exchange the values of SPU1 and SPU2 when SPU1 is an even number?

 

[kiblinger]

Alas it isn't that simple. The values in the fields in my example are irrelevant except in respect to their being unique or not to a particular record. Even/odd, numerical value, the fact that they are integers etc., are all irrelevant. They are just placeholders.

I think this is as close to a description as I can come of what I want to do:

Step 1: Get all records where SPU2 IS NULL and GROUP BY SPU1

Step 2: Get all records where SPU2 IS NOT NULL and GROUP BY SPU2

Step 3: Take the list of unique SPU2 values from Step 2. Get the first one in the list, say, "SPU2=2," and look through the results from Step 1 and find where SPU1=2

Step 4: Move that SPU1 code from the SPU1 field to SPU2, and make SPU1 NULL

Step 5: Repeat Steps 3 and 4, working through the entire list from Step 2

 

[Golom]

That's not what your example shows.

On records "G" and "J" you have exchanged SUP1 and SUP2. You didn't set SUP1 to NULL as Step 4 specifies.

Following your description, you should get
[tt]
Code SPU1 SPU2
A 1 NULL
B 1 NULL
C 1 2
D 1 2
E NULL 2
F NULL 2
G NULL 2
H NULL 3
I NULL 3
J NULL 4
K NULL 4
L 5 4
M 5 NULL
N 5 NULL
[/tt]
and you get that with

UPDATE SPU SET SPU2 = SPU1, SPU1 = NULL

WHERE SPU1 IN
(
SELECT DISTINCT SPU1 As [CodesToChange]
FROM SPU AS s
WHERE SPU2 IS NULL
     AND  SPU1  IN (SELECT  DISTINCT  SPU2
                    FROM SPU AS s
                    WHERE SPU2 IS NOT NULL)
);

 

[kiblinger]

Aha - you're right. My description and example don't exactly match. Sorry about that! My example is what I want but I can't figure out how to describe it. It's spatial data, and some adjacent polygons share some common values and I need to move some of those values around.... Anyway, thanks again and if I think of how to describe it I'll post later.