convert columns to rows

[lschultz]

I have a similar problem to the discussion string below.

My MS Access table is from an XLS and is in the following format:

Field1 Field2 Field3 Field4
-------- --------- ---------- ----------
<blank> Jan-06 Feb-06 Mar-06
cust1 100 200 300
cust2 400 500 600
cust3 700 800 900

and i need to get it into the following format.

Field1 Field2 Field3
--------- -------- ---------
cust1 Jan-06 100
cust1 Feb-06 200
cust1 Mar-06 300
cust2 Jan-06 400
cust2 Feb-06 500
cust2 Mar-06 600
cust3 Jan-06 700
cust3 Feb-06 800
cust3 Mar-06 900

The "normalizing union query" in the discussion below was effective in
getting Field1 and Field3 merged. However, I need the Field2 "date"
also. Any suggestions would be appreciated.
------------------------------------------------------------------------------------------------------

Thank you John and Duane for your help. I tried on about a dozen
fields on the table and it seem to work. Next I have to convert the
table consists of 150+ columns with missed type (text, number). The
SQL statement will be quite large and hope that it'll run ok and there
is no limit to it.
----- John Vinson wrote: -----

I have a table with the columns as: UserId, ItemA, ItemB,

ItemC, ItemD, etc and each item stores a "1" or a "0" depending on the
item selected. Is there a query that I can convert the columns into
rows? for example:

001 1 1 0 0
001 ItemA
001 ItemB

A "Normalizing Union Query" would do the trick here. You'll need
to go
to the SQL window but it's not hard:


SELECT UserID, "ItemA" As ItemSelected FROM table WHERE [ItemA] =
1
UNION
SELECT UserID, "ItemB" As ItemSelected FROM table WHERE [ItemB] =
1
UNION
SELECT UserID, "ItemC" As ItemSelected FROM table WHERE [ItemC] =
1
UNION
SELECT UserID, "ItemD" As ItemSelected FROM table WHERE [ItemD] =
1
....


Create this Query and check that it returns the desired data;
then
save it (say as uniAllSelected) and base an Append or MakeTable
query
on the saved query.


John W. Vinson[MVP]

 

[Guest]

First move the data in the first record to the field names. Then delete the
first record. For example it would then look like this in the table. Notice
that I used the field name of Custs:

Custs Jan-06 Feb-06 Mar-06
cust1 100 200 300
cust2 400 500 600
cust3 700 800 900

Then the following query will work after you insert the proper table name.
It also changes the 'date' fields to real dates plus the numbers to real
numbers. Both will make things much nicer in the future. For example it makes
it possible to sort by the TheDate field properly.

SELECT Lschultz.Custs, #1/1/2006# AS TheDate,CDbl(Lschultz.[Jan-06]) AS
TheNumber
FROM Lschultz
UNION ALL
SELECT Lschultz.Custs, #2/1/2006# AS TheDate, CDbl(Lschultz.[Feb-06]) AS
TheNumber
FROM Lschultz
UNION ALL
SELECT Lschultz.Custs, #3/1/2006# AS TheDate, CDbl(Lschultz.[Mar-06]) AS
TheNumber
FROM Lschultz
ORDER BY 1, 2 ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a similar problem to the discussion string below.

My MS Access table is from an XLS and is in the following format:

Field1 Field2 Field3 Field4
-------- --------- ---------- ----------
<blank> Jan-06 Feb-06 Mar-06
cust1 100 200 300
cust2 400 500 600
cust3 700 800 900

and i need to get it into the following format.

Field1 Field2 Field3
--------- -------- ---------
cust1 Jan-06 100
cust1 Feb-06 200
cust1 Mar-06 300
cust2 Jan-06 400
cust2 Feb-06 500
cust2 Mar-06 600
cust3 Jan-06 700
cust3 Feb-06 800
cust3 Mar-06 900

The "normalizing union query" in the discussion below was effective in
getting Field1 and Field3 merged. However, I need the Field2 "date"
also. Any suggestions would be appreciated.
------------------------------------------------------------------------------------------------------

Thank you John and Duane for your help. I tried on about a dozen
fields on the table and it seem to work. Next I have to convert the
table consists of 150+ columns with missed type (text, number). The
SQL statement will be quite large and hope that it'll run ok and there
is no limit to it.
----- John Vinson wrote: -----

I have a table with the columns as: UserId, ItemA, ItemB,

ItemC, ItemD, etc and each item stores a "1" or a "0" depending on the
item selected. Is there a query that I can convert the columns into
rows? for example:

001 1 1 0 0
001 ItemA
001 ItemB

A "Normalizing Union Query" would do the trick here. You'll need
to go
to the SQL window but it's not hard:


SELECT UserID, "ItemA" As ItemSelected FROM table WHERE [ItemA] =
1
UNION
SELECT UserID, "ItemB" As ItemSelected FROM table WHERE [ItemB] =
1
UNION
SELECT UserID, "ItemC" As ItemSelected FROM table WHERE [ItemC] =
1
UNION
SELECT UserID, "ItemD" As ItemSelected FROM table WHERE [ItemD] =
1
....


Create this Query and check that it returns the desired data;
then
save it (say as uniAllSelected) and base an Append or MakeTable
query
on the saved query.


John W. Vinson[MVP]

 

[John W. Vinson]

I have a similar problem to the discussion string below.

My MS Access table is from an XLS and is in the following format:

Field1 Field2 Field3 Field4
-------- --------- ---------- ----------
<blank> Jan-06 Feb-06 Mar-06
cust1 100 200 300
cust2 400 500 600
cust3 700 800 900

and i need to get it into the following format.

Field1 Field2 Field3
--------- -------- ---------
cust1 Jan-06 100
cust1 Feb-06 200
cust1 Mar-06 300
cust2 Jan-06 400
cust2 Feb-06 500
cust2 Mar-06 600
cust3 Jan-06 700
cust3 Feb-06 800
cust3 Mar-06 900

The "normalizing union query" in the discussion below was effective in
getting Field1 and Field3 merged. However, I need the Field2 "date"
also. Any suggestions would be appreciated.
------------------------------------------------------------------------------------------------------

Thank you John and Duane for your help. I tried on about a dozen
fields on the table and it seem to work. Next I have to convert the
table consists of 150+ columns with missed type (text, number). The
SQL statement will be quite large and hope that it'll run ok and there
is no limit to it.

Well... there IS a limit to it. Access cannot deal with more than 255 fields,
and a UNION query with 150 SELECTS will undoubtedly fail with a Query Too
COmplex error.

I suspect you'll need to either run this in three union queries of 50 columns
each, or do it in VBA code. In either case you'll need to omit the first row,
or use it as the header row so that you have fields named [Jan-06], [Feb-06]
and so on.

To get the date field into Field2, you need to use a date literal in each
SELECT. Are you intending Field2 to be Text or Date/Time? I'd really recommend
the latter so that it can be searched and sorted chronologically. If so, your
UNION query will look like

SELECT Field1, #1/1/06# AS Field2, [Jan-06] AS Sales FROM thespreadsheet
WHERE [Jan-06] IS NOT NULL
UNION ALL
SELECT Field1, #2/1/06# AS Field2, [Feb-06] AS Sales FROM thespreadsheet
WHERE [Feb-06] IS NOT NULL
UNION ALL
<etc etc>


John W. Vinson [MVP]