Transpose Table

[TGW]

Hi,

I have inherited a table that maintains units of stock by location with the
products listed in column headers.

Location; Prod_1; Prod_2....Prod_222
A, 2,3....4
B, 3,3....3
C, 4,4....5

Want to transpose / create new table so that data is stored as follows

Location, Prod_Ref, No of Units.
A, Prod_1, 2
A, Prod_2, 3

Any ideas how this can be achieved?

Thanks






I would like to create a new table which transpose this

 

[Stefan Hoffmann]

hi,

I have inherited a table that maintains units of stock by location with the
products listed in column headers.

Location; Prod_1; Prod_2....Prod_222
A, 2,3....4
B, 3,3....3
C, 4,4....5

Want to transpose / create new table so that data is stored as follows

Location, Prod_Ref, No of Units.
A, Prod_1, 2
A, Prod_2, 3

Any ideas how this can be achieved?

You need a query like that (air code):

Dim fd As DAO.Field
Dim qd As DAO.QueryDef
Dim td As DAO.TableDef
Dim SQL As String

Set td = CurrentDb.TableDefs.Item("yourInheritedTable")
For Each fd In td.Fields
If fd.Name <> "Location" Then
SQL = SQL & _
"UNION ALL " & _
"SELECT Location, '" & fd.Name & "', [" & fd.Name & "] " & _
"FROM yourInheritedTable "
End If
Next fd
SQL = Mid(SQL, 11)

Set qd = CurrentDb.CreateQueryDef("youNameIt", SQL)



mfG
--> stefan <--

 

[Gennaro]

Hi,

I have inherited a table that maintains units of stock by location with
the
products listed in column headers.

Location; Prod_1; Prod_2....Prod_222
A, 2,3....4
B, 3,3....3
C, 4,4....5

Want to transpose / create new table so that data is stored as follows

Location, Prod_Ref, No of Units.
A, Prod_1, 2
A, Prod_2, 3

Any ideas how this can be achieved?

Thanks






I would like to create a new table which transpose this

 

[John W. Vinson]

Hi,

I have inherited a table that maintains units of stock by location with the
products listed in column headers.

Location; Prod_1; Prod_2....Prod_222
A, 2,3....4
B, 3,3....3
C, 4,4....5

Want to transpose / create new table so that data is stored as follows

Location, Prod_Ref, No of Units.
A, Prod_1, 2
A, Prod_2, 3

Any ideas how this can be achieved?

One - or probably more - Normalizing Union Queries will do this. Stefan has
some eloquent VBA code to construct such a query - it needs to end up in the
form

SELECT Location, "Prod_1", [Prod_1] FROM table WHERE [Prod_1] IS NOT NULL
UNION ALL
SELECT Location, "Prod_2", [Prod_2] FROM table WHERE [Prod_2] IS NOT NULL
UNION ALL
<etc etc>

At some point - very likely before 222 UNION clauses - you'll get a QUERY TOO
COMPLEX error. I'd suggest perhaps 40 or 50 products at a time.

You can then base an APPEND query (into a correctly normalized empty table as
you suggest above) on each of these union queries.

 

[hor vannara]

Hi,

I have inherited a table that maintains units of stock by location with
the
products listed in column headers.

Location; Prod_1; Prod_2....Prod_222
A, 2,3....4
B, 3,3....3
C, 4,4....5

Want to transpose / create new table so that data is stored as follows

Location, Prod_Ref, No of Units.
A, Prod_1, 2
A, Prod_2, 3

Any ideas how this can be achieved?

One - or probably more - Normalizing Union Queries will do this. Stefan
has
some eloquent VBA code to construct such a query - it needs to end up in
the
form

SELECT Location, "Prod_1", [Prod_1] FROM table WHERE [Prod_1] IS NOT NULL
UNION ALL
SELECT Location, "Prod_2", [Prod_2] FROM table WHERE [Prod_2] IS NOT NULL
UNION ALL
<etc etc>

At some point - very likely before 222 UNION clauses - you'll get a QUERY
TOO
COMPLEX error. I'd suggest perhaps 40 or 50 products at a time.

You can then base an APPEND query (into a correctly normalized empty table
as
you suggest above) on each of these union queries.