Turning a 3 column flat file into a "3D" Table

[Ciaran Hudson]

Hi,

I want to create a table in Access using a 3 column flat file.
And turn into into a Table like a Pivot Table in Excel.
Although it will look like an Excel pivot table, it will not do any calculation.
What would normally be 'Values' in a pivot table simply needs to return content - more like an Excel lookup.

Each record in my data set is unique and I am not trying to do calculation.
I am just trying to represent the same data differently.

Here is an example of the data.
Data File
User Computer Status
Paul PC1 Active
Paul PC2 Inactive
Peter PC1 Locked
Peter PC2 Active

Note, each record when all 3 fields combined is unique.

And here is the desired table
Desired Table
User PC1 PC2
Paul Active Inactive
Peter Locked Active

In the desired table.
Each User will appear only once as a row
Each Computer will only appear once as a column.
And the status will appear where the User and Computer meet.

If it cannot be done in Access, I am open to Excel solutions.

Regards,
Ciarán

[Bob Quintal]

Post by Ciaran Hudson
Hi,
I want to create a table in Access using a 3 column flat file.
And turn into into a Table like a Pivot Table in Excel.
Although it will look like an Excel pivot table, it will not do
any calculation. What would normally be 'Values' in a pivot table
simply needs to return content - more like an Excel lookup.
Each record in my data set is unique and I am not trying to do
calculation. I am just trying to represent the same data
differently.
Here is an example of the data.
Data File
User Computer Status
Paul PC1 Active
Paul PC2 Inactive
Peter PC1 Locked
Peter PC2 Active
Note, each record when all 3 fields combined is unique.
And here is the desired table
Desired Table
User PC1 PC2
Paul Active Inactive
Peter Locked Active
In the desired table.
Each User will appear only once as a row
Each Computer will only appear once as a column.
And the status will appear where the User and Computer meet.
If it cannot be done in Access, I am open to Excel solutions.
Regards,
Ciarán

First create a crosstab query. The vaLue field requires a summation
function, 'first' would be the correct one to usre.
Then create a make table query based on the crosstab query.