Re-organizing Access table (convert rows to column

Question

I have a database where there are 2 fields similar (that match another table) and then the third is unique. I want to convert the table to have the 3rd unique value listed in columns instead of a separate record.

I.E.

5/1/2013  A321  1  $1,120
5/2/2013  A325  1  $2,261
5/1/2013  A321  2  $2,120

will convert to

5/1/2013  A321  $1,120  $2,120
5/2/2013  A325  $2,261

I could create separate queries for each value in column 3 and then have another query that joins them in the fashion I want them to but I was hoping there was a more elegant solution.

Answer 1

For test data in [OldTable]

FieldA      FieldB  FieldC  FieldD
----------  ------  ------  ------
2013-05-01  A321         1    1120
2013-05-02  A325         1    2261
2013-05-01  A321         2    2120

the Crosstab Query

TRANSFORM First(FieldD) AS FirstOfFieldD
SELECT FieldA, FieldB
FROM 
    (
        SELECT
            FieldA,
            FieldB,
            'Value' & FieldC AS ColumnName,
            FieldD
        FROM OldTable
    )
GROUP BY FieldA, FieldB
PIVOT ColumnName

returns

FieldA      FieldB  Value1  Value2
----------  ------  ------  ------
2013-05-01  A321      1120    2120
2013-05-02  A325      2261        

If you want to actually create a new table containing those results then save the above query in Access as [xtabQuery] and then run

SELECT xtabQuery.* INTO NewTable
FROM xtabQuery;

Answer 2

I would open up the Query Wizard and create a Crosstab Query. It should prompt you to put in what you want for the columns and records.

When the query has been written by the Wizard, just make that into a MakeTable query by opening it in SQL View and adding "INTO MyNewTable" before the FROM statement.