reverse crosstab query !

[jeanulrich00]

Hi

I have imported a range from Excel that contains over 65 columns and
200 rows; so my access table as 65 fields. First field is ID (unique
no duplicate)

60 of those columns represent periods (months). First column out of
the 60 columns is Period1, second column is Period2, then Period 3,
Period4 ....till period 60

Names for those 60 fields in my table are M01, M02, M03 ..and so on
until M60

What I want to obtain is a table with 12,000 records (60 columns x 200
rows) that would look like this

ID Period Value
1 1 M01
1 2 M02
1 3 M03
1 4 M04
1 5 M05
1 6 M06 till Period 60 and value till M60 then

2 1 M01
2 2 M02
2 3 M03
2 4 M04
2 5 M05
2 6 M06 till Period 60 and value till M60 then

I think it could be possible with coding (maybe SQL code) but I am not
familiar with SQL

Thanks for helping

 

[Duane Hookom]

Generally this could be done with a normalizing union query like:
SELECT ID, 1 as Mth, M01 as MthValue
FROM tblImportFromExcel
UNION ALL
SELECT ID, 2, M02
FROM tblImportFromExcel
UNION ALL
SELECT ID, 3, M03
FROM tblImportFromExcel
UNION ALL
--- etc ---
SELECT ID, 60, M60
FROM tblImportFromExcel;

Considering the number of fields, this might become overly complex to the
point where Access errors. If this happens you could subdivide the union and
append the results to your new table.

 

[John W. Vinson]

Hi

I have imported a range from Excel that contains over 65 columns and
200 rows; so my access table as 65 fields. First field is ID (unique
no duplicate)

60 of those columns represent periods (months). First column out of
the 60 columns is Period1, second column is Period2, then Period 3,
Period4 ....till period 60

Names for those 60 fields in my table are M01, M02, M03 ..and so on
until M60

What I want to obtain is a table with 12,000 records (60 columns x 200
rows) that would look like this

ID Period Value
1 1 M01
1 2 M02
1 3 M03
1 4 M04
1 5 M05
1 6 M06 till Period 60 and value till M60 then

2 1 M01
2 2 M02
2 3 M03
2 4 M04
2 5 M05
2 6 M06 till Period 60 and value till M60 then

I think it could be possible with coding (maybe SQL code) but I am not
familiar with SQL

Thanks for helping

You're absolutely on the right track here. The technique you need is called a
"Normalizing Union Query". You need to create a new query based on your table
and select View... SQL; the query grid isn't capable of handling this
specialized type of query.

Edit it to something like

SELECT [ID], (1) AS Period, [MO1] AS Value
FROM yourtable
WHERE [MO1] IS NOT NULL
UNION ALL
SELECT [ID], (2) AS Period, [MO2] AS Value
FROM yourtable
WHERE [MO2] IS NOT NULL
UNION ALL
SELECT [ID], (3) AS Period, [MO3] AS Value
FROM yourtable
WHERE [MO3] IS NOT NULL
UNION ALL

<etc etc through all 60 fields>

Then base an Append query on this saved UNION query. Omit the WHERE MOx IS NOT
NULL if you want to preserve whatever's in the column even if it is null or if
you're sure that there will never be NULL values. There are apparently four
other fields not mentioned, you can include them in each SELECT as well if you
need them.


It's possible that you'll get a "QUERY TOO COMPLEX" error; if so do it as two
queries, say for MO1-MO30 and MO31-MO60.

 

[Piet Linden]

This will work... requires a reference to DAO, though...
FixedXL is the name of my destination table...
You may need to modify it a little so it matches up with your fields
exactly, but it should get you going.
First step: create a table (Mine is FixedXL)
Fields:
ID - long integer
Value - same type as your number
PeriodNo - long/integer

Then run something like this:

Public Sub NormalizeAppend()
'[ID],[Period 1], [Period 2], ... where [Period n] contains the
value.

Dim strSQL As String
Dim intCounter As Integer

Const cstrSQL As String = "INSERT INTO FixedXL ( ID, [Value],
PeriodNo ) SELECT xlsPeriods.ID, xlsPeriods.[Period 1], 1 As MonthNo
FROM xlsPeriods;"

For intCounter = 1 To 60
strSQL = Replace(cstrSQL, 1, intCounter)
DBEngine(0)(0).Execute strSQL, dbFailOnError
Next intCounter


End Sub

 

[jeanulrich00]

Generally this could be done with a normalizing union query like:
SELECT ID, 1 as Mth, M01 as MthValue
FROM tblImportFromExcel
UNION ALL
SELECT ID, 2, M02
FROM tblImportFromExcel
UNION ALL
SELECT ID, 3, M03
FROM tblImportFromExcel
UNION ALL
--- etc ---
SELECT ID, 60, M60
FROM tblImportFromExcel;

Considering the number of fields, this might become overly complex to the
point where Access errors. If this happens you could subdivide the union and
append the results to your new table.

--
Duane Hookom
Microsoft Access MVP













- Show quoted text -

Thanks a lot Duane. As the union query was too complex, I had to
split it in 2 queries and I append results in my table. Now it works
perfectly

 

[jeanulrich00]

Hi

I have imported a range from Excel that contains over 65 columns and
200 rows; so my access table as 65 fields.  First field is ID (unique
no duplicate)

60 of those columns represent periods (months).  First column out of
the 60 columns is Period1, second column is Period2, then Period 3,
Period4 ....till period 60

Names for those 60 fields in my table are M01, M02, M03 ..and so on
until M60

What I want to obtain is a table with 12,000 records (60 columns x 200
rows) that would look like this

ID  Period  Value
1      1         M01
1      2         M02
1      3         M03
1      4         M04
1      5         M05
1      6         M06  till Period 60  and value till M60 then

2      1         M01
2      2         M02
2      3         M03
2      4         M04
2      5         M05
2      6         M06  till Period 60  and value till M60 then

I think it could be possible with coding (maybe SQL code) but I am not
familiar with SQL

Thanks for helping

You're absolutely on the right track here. The technique you need is called a
"Normalizing Union Query". You need to create a new query based on your table
and select View... SQL; the query grid isn't capable of handling this
specialized type of query.

Edit it to something like

SELECT [ID], (1) AS Period, [MO1] AS Value
FROM yourtable
WHERE [MO1] IS NOT NULL
UNION ALL
SELECT [ID], (2) AS Period, [MO2] AS Value
FROM yourtable
WHERE [MO2] IS NOT NULL
UNION ALL
SELECT [ID], (3) AS Period, [MO3] AS Value
FROM yourtable
WHERE [MO3] IS NOT NULL
UNION ALL

<etc etc through all 60 fields>

Then base an Append query on this saved UNION query. Omit the WHERE MOx IS NOT
NULL if you want to preserve whatever's in the column even if it is null or if
you're sure that there will never be NULL values. There are apparently four
other fields not mentioned, you can include them in each SELECT as well if you
need them.

It's possible that you'll get a "QUERY TOO COMPLEX" error; if so do it astwo
queries, say for MO1-MO30 and MO31-MO60.



--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

Thanks John. Duane and you gave me the right way to do it. Like I
told Duane I had to make 2 Union queries because "Query too complex".
I am realy happy. Thanks again