Creating columns from rows

[TropicalFred]

I have an Access database table that looks like this:

Account Attribute
100 Reg20
100 Comp
100 South
101 Reg31
101 NonComp
101 North
102 Closed

And what I need is a table that looks like this:

Account Group Region Comp/NC Open/Closed
100 South Reg20 Comp
101 North Reg31 NonComp
102 Closed

I suppose that I could write a query for each attribute and then combine the queries into a single table, but I was just wondering if there was a better (i.e. easier) way to do this.

Thanks for your help and have a Happy Thanksgiving.

 

[cmmrfrds]

Since you just have a few values/columns then the iif function can do it in a query.

Basic idea.

select Account, iif(attribute="South" OR attribute="North",attribute,"&quot as Group, iif(attribute="Reg20" OR attribute="Reg31",attribute,"&quot as Region, etc..... From yourtable

 

[JonFer]

It looks like your table is missing a field that I would call AttributeType. It would have values like Region, Group, etc. The table would then have a primary key of Account and Group (assuming you want only one attribute of each type on an account). Then you can do a simple crosstab query on the table.