Crosstab in reverse

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

I am trying to create a query that will do the
following...

Turn this:

RA Number ProductID Sm Med
245 00000456 12 12

Into this:

RA Number ProductID Size Qty
245 00000456 Sm 12
245 00000456 Med 12

Any Ideas?
 
Try a union query:

SELECT [RA Number], ProductID, "Sm" As Size, Sm
FROM MyTable
UNION ALL
SELECT [RA Number], ProductID, "Med" As Size, Med
FROM MyTable
 
One slight modification would omit the row for Sm or Med if the value
might be NULL:

SELECT [RA Number], ProductID, "Sm" As Size, Sm
FROM MyTable
WHERE Sm IS NOT NULL
UNION ALL
SELECT [RA Number], ProductID, "Med" As Size, Med
FROM MyTable
WHERE Med IS NOT NULL

In some cases this can be useful. It causes missing data (NULLs) to
be changed into missing rows.

This is commonly called a "normalizing UNION" query because it can be
used to create a normalized (good database practice) from
"spreadsheet" data (bad database practice). If you wanted to add
another set of data for "Lg" you would not need to modify the table.
This brings up the point that, if you want your data stored this way,
you should probably have another table of "Sizes" in which you put
"Sm" and "Med" along with any new sizes added later.

It is rather easy to convert back to the format with columns for "Sm"
and "Med" so it is best to store the data in normalized fashion.
Converting the other way, to normalized fashion from unnormalized, is
a bit of a problem.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Good point, Tom. I do that myself, but forgot it in when posting the
solution.

In fact, you may want to block 0 values as well, in which case you could use

WHERE Nz([Sm], 0) <> 0

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Tom Ellison said:
One slight modification would omit the row for Sm or Med if the value
might be NULL:

SELECT [RA Number], ProductID, "Sm" As Size, Sm
FROM MyTable
WHERE Sm IS NOT NULL
UNION ALL
SELECT [RA Number], ProductID, "Med" As Size, Med
FROM MyTable
WHERE Med IS NOT NULL

In some cases this can be useful. It causes missing data (NULLs) to
be changed into missing rows.

This is commonly called a "normalizing UNION" query because it can be
used to create a normalized (good database practice) from
"spreadsheet" data (bad database practice). If you wanted to add
another set of data for "Lg" you would not need to modify the table.
This brings up the point that, if you want your data stored this way,
you should probably have another table of "Sizes" in which you put
"Sm" and "Med" along with any new sizes added later.

It is rather easy to convert back to the format with columns for "Sm"
and "Med" so it is best to store the data in normalized fashion.
Converting the other way, to normalized fashion from unnormalized, is
a bit of a problem.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Try a union query:

SELECT [RA Number], ProductID, "Sm" As Size, Sm
FROM MyTable
UNION ALL
SELECT [RA Number], ProductID, "Med" As Size, Med
FROM MyTable
Click to expand...
Click to expand...
 
Back
Top