how can i transform a table like this?

[kenton]

i've many tables with data stored like this by a noob user:

ID field-A field-B
1 1 2
1 2 3
1 4
2 2 3
2 4
3 1 1
3 2
3 3
3 4
4 1
4 2
.
.
it's very hard for me to analize datas in this tables

i want to transform the table above like the one designed under here
(assuming that field-A can have values from 1 to 2
and field-B from 1 to 4),
i want to multiply the fields obtaining a unique raw for each ID and a 0 or 1 value (absent/present):

ID field-A1 field-A2 field-B1 field-B2 field-B3 field-B4
1 1 1 0 1 1 1
2 0 1 0 0 1 1
3 1 0 1 1 1 1
4 1 1 0 0 0 0
.
.

can be made a script or something of automatic using access?
tkx u

kenton

[kenton]

BETTER ...


FROM

ID field-A field-B
1 . . . 1 . . . 2
1 . . . 2 . . . 3
1 . . . . . . . 4
2 . . . 2 . . . 3
2 . . . . . . . 4
3 . . . 1 . . . 1
3 . . . . . . . 2
3 . . . . . . . 3
3 . . . . . . . 4
4 . . . 1
4 . . . 2
.
.

TO

ID field-A1 field-A2 field-B1 field-B2 field-B3 field-B4
1 . . 1 . . . . . 1 . . . . 0 . . . . 1 . . . . 1 . . . . . 1
2 . . 0 . . . . . 1 . . . . 0 . . . . 0 . . . . 1 . . . . . 1
3 . . 1 . . . . . 0 . . . . 1 . . . . 1 . . . . 1 . . . . . 1
4 . . 1 . . . . . 1 . . . . 0 . . . . 0 . . . . 0 . . . . . 0
.
.

[SDas]

kenton, THis should work:

SELECT ID
     , MAX( IIF(FIELD_A = 1,1,0) ) AS FIELD_A1
     , MAX( IIF(FIELD_A = 2,1,0) ) AS FIELD_A2
     , MAX( IIF(FIELD_B = 1,1,0) ) AS FIELD_B1
     , MAX( IIF(FIELD_B = 2,1,0) ) AS FIELD_B2
     , MAX( IIF(FIELD_B = 3,1,0) ) AS FIELD_B3
     , MAX( IIF(FIELD_B = 4,1,0) ) AS FIELD_B4
GROUP BY ID

IIF evaluates the expression (Field = X). If True, return the first value (1), If False, return the second value (0).

The Max and Group by consolidates the rows down to unique rows by ID value. If a column has only 0 values the Max is still 0. If the column has only 1 values the Max is still 1. If the column has mixed 1 and 0 values it will return 1.

Note: I do not have Access and can't test this but I think it will work.

[kenton]

Thank you very much SDas,
it works perfectly ...
and now i've very good tables

for other users, don't forget the FROM statement before the GROUP BY or the query doesn't work.

Like this:

SELECT [RRT procedures].ID

     , MAX( IIF( [RRT procedures].[RRT available] = "1",1,0) ) AS [RRT available 1]
     , MAX( IIF( [RRT procedures].[RRT available] = "2",1,0) ) AS [RRT available 2]
     , MAX( IIF( [RRT procedures].[RRT available] = "3",1,0) ) AS [RRT available 3]
     , MAX( IIF( [RRT procedures].[RRT available] = "4",1,0) ) AS [RRT available 4]

     , MAX( IIF( [RRT procedures].[RRT technique] = "1",1,0) ) AS [RRT technique 1]
     , MAX( IIF( [RRT procedures].[RRT technique] = "2",1,0) ) AS [RRT technique 2]
     , MAX( IIF( [RRT procedures].[RRT technique] = "3",1,0) ) AS [RRT technique 3]
     , MAX( IIF( [RRT procedures].[RRT technique] = "4",1,0) ) AS [RRT technique 4]
     , MAX( IIF( [RRT procedures].[RRT technique] = "5",1,0) ) AS [RRT technique 5]
     , MAX( IIF( [RRT procedures].[RRT technique] = "6",1,0) ) AS [RRT technique 6]

     , MAX( IIF( [RRT procedures].[total number of treatments] <> "",[RRT procedures].[total number of treatments],"") ) AS [total number of treatments]
     , MAX( IIF( [RRT procedures].[PD number of treatments] <> "",[RRT procedures].[PD number of treatments],"") ) AS [PD number of treatments]
     , MAX( IIF( [RRT procedures].[IHD number of treatments] <> "",[RRT procedures].[IHD number of treatments],"") ) AS [IHD number of treatments]
     , MAX( IIF( [RRT procedures].[CRRT number of treatments] <> "",[RRT procedures].[CRRT number of treatments],"") ) AS [CRRT number of treatments]

FROM [RRT procedures] 
	 
GROUP BY [RRT procedures].ID;

In the last part how to show a not null field too ...

[SDas]

I can't believe I forgot the FROM...