Tranposing Fields in Access?

[kestefon]

I have three fields representing Units, Price, and Cost. Instead of having three distinct fields, I actually need to have one field with variable values. Units, price, and cost would be specified by a "Variable Type" column of their own. (I know that isn't ideal for a database, but the data has to laid out this way per someone's request.)



I know how to do this in Excel with PivotTables, but I'm curious whether there's an easier way of doing it in Access without Pivot Tables. Any help would be greatly appreciated.

Also, the attached Access file should clarify what I'm trying to accomplish.

SampleData_Transposed.accdb

[June7]

That can be done with a UNION query:

SELECT Product, BrandDetail, "Units" AS Variable, Units AS Amount FROM RawData
UNION SELECT Product, BrandDetail, "Price", Price FROM RawData
UNION SELECT Product, BrandDetail, "Cost", Cost FROM RawData;

Value is a reserved word http://support.microsoft.com/kb/286335
Should not use reserved words as names.

Use that query to accomplish whatever your user needs but don't change table structure.

[Dal Jeanis]

First, "has to be laid out this way per someone's request" isn't a good enough reason for a table design. Report design or query design, sure, but not table design.

Hmmm. Okay, I see what they are asking you to do. This is taking normalization to a silly extreme, IMHO.

The solution is trivial, though.

You don't have to actually transpose the data at all to make that query.

Create a table Called tblFields with one field called Variable. Add three records, Units, Cost and Price.

tblFields
   Variable    Text     "Units", "Cost", "Price"

Query1:
SELECT 
   TR.Product,
   TR.BrandDetail,
   TF.Variable,
   IIF(TF.Variable = "Units",TR.Units,
       IIF(TF.Variable = "Price",TR.Price,
           IIF(TF.Variable = "Cost",TR.Cost,0))) AS VarValue
FROM 
   RawData AS TR, 
   tblFields AS TF;

If you really must do so, then you can make that query into a maketable query and create a table of the hypernormalized data. I can't think of a useful application for that table layout, but it can be done.

Better practice to just run that query when the boss wants to see it that way.

[Dal Jeanis]

The reason you DON'T want to do that transform to the real table and lose the original configuration is this - suppose you want to calculate units times cost. Here's the SQL using table RawData:

SELECT 
   TR.Product,
   TR.BrandDetail,
   TR.Units,
   TR.Cost,
   (TR.Units * TR.Cost) AS TotCost
FROM
   RawData AS TR;

Okay, here's the same SQL using Query1's table structure:

SELECT 
   Q1.Product,
   Q1.BrandDetail,
   Q1.Units,
   Q2.Cost,
   (Q1.Units * Q2.Cost) AS TotCost
FROM
   Query1 AS Q1 
   INNER JOIN 
   Query1 AS Q2
   ON Q1.Product = Q2.Product
   AND Q1.BrandDetail = Q2.BrandDetail
WHERE Q1.Variable = "Units"
AND Q2.Variable = "Cost";

And if you want to use all three fields in a single query, then you end up adding inner joins on three different aliases of the same hypernormal table.

[kestefon]

Thanks to both of you for the great explanations. That solved my problem perfectly. And I'm surprised how much easier it is in Access vs. in Excel.

June7, could you explain what you're doing, particularly in these parts of the code:

"Units" AS Variable, Units AS Amount FROM RawData
"Price", Price FROM RawData
"Cost", Cost FROM RawData;

I just want to understand conceptually what's going on. For instance, why do you have Units/Price/Cost in quotations first, then without quotations?

[June7]

If you build and run the query, will probably explain a lot.

With Units in quotes this is a literal string that calculates a value for the constructed field named Variable.

The second reference is to pull the data from the referenced field into the constructed field named Amount.