Query to re-organize data (single field into multiple fields)

[srk2011]

Hi,

I have a table that describes an action undertaken by a person, where there is one record PER action -- together, IndustryNo, DeptNo, PersonNo and Action describe a unique record. There is at least one record for every person of interest, and some people may have multiple actions.

e.g.,

IndustryNo  DeptNo            PersonNo       Action
       1        1              1001               2
       1        1              1002               4
       1        1              1002               6
       1        1              1002               7 
       1        2              1003               3
       2        1              1001               2
       2        1              1001              11

I would like to have a select query that returns the data in the following format: Organized as ONE RECORD per person, and the multiple actions are split across fields, so that the result of the query looks like:

IndustryNo  DeptNo              PersonNo         Action1   Action2    Action3 
       1         1              1001                   2        
       1         1              1002                   4         6          7
       1         2              1003                   3
       2         1              1001                   2        11

What would be the best way to go about doing this?

Thanks!

 

[plog]

Whenever you want to turn a column into a row you want a Cross Tab query. Create a new query, bring in the fields you want, select crosstab query, configure it best as you can and keep playing with it until Access stops yelling errors at you and the data is how you want it.

If you run into specific problems that you cant overcome, post your SQL here and I can give more specific answers.

 

[Millah]

In order to accomplish this I think you'll need to add another field that keeps track of what action number it is. Access does not have a ranking function, otherwise you could accomplish what you are trying to accomplish without an extra field. Once you have the extra field it is simple.


TRANSFORM First(Person.Action) AS FirstOfAction
SELECT Person.IndustryNo, Person.DeptNo, Person.PersonNo
FROM Person
GROUP BY Person.IndustryNo, Person.DeptNo, Person.PersonNo, Person.PersonNo
PIVOT Person.ActionNo;