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

 

[mbizup]

If this is the ONLY purpose of your database, a fairly quick/simple (but not optimal) solution
Set up your table to include the colums as in your original data file (User, Computer, Status).

Include an autonumber field (ID). (Access practically begs you to do this when you try to create a table without one, so you can 'allow' access to automatically add this for you).

Once your table is created and populated as needed, you can use a query to pull the data in the desired format. Using SQL something like this view in the query builder:

SELECT t.User, q1.PC1, q2.PC2
FROM (YourTable t LEFT JOIN
(SELECT Status AS PC1 FROM YourTable t1 WHERE t1.User = t.User AND t1.PC = 'PC1') q1) LEFT JOIN
(SELECT Status AS PC2 FROM YourTable t2 WHERE t2.User = t.User AND t1.PC = 'PC1') q2


If your database has any potential of growing beyond this purpose, however, you should normalize your database with separate tables for users, with an auto number userID which would link to a computer status table with fields as follows:

ComputerStatID: Autonumber/PK
UserID: Number/integer (Foreign key for link to User table)
Computer: Text
isActive: Yes/No

 

[Bob Quintal]

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.