How to make this crosstab query

  • Thread starter Thread starter Marco
  • Start date Start date
M

Marco

Hello.

I need to create this croostab query. I have this query result:

ID Item Mes1 Mes2 Mes3 Mes4 Mes5
1 92 100 120 150 95 88


and I need that my result be be like this so I can inset it in another table:

ID Item Mes Mes1
1 92 Mes1 100
1 92 Mes2 120
1 92 Mes3 150
1 92 Mes4 95
1 92 Mes5 88


How can I do this?

Regards,
Marco
 
Hello.

I need to create this croostab query. I have this query result:

ID Item Mes1 Mes2 Mes3 Mes4 Mes5
1 92 100 120 150 95 88


and I need that my result be be like this so I can inset it in another table:

ID Item Mes Mes1
1 92 Mes1 100
1 92 Mes2 120
1 92 Mes3 150
1 92 Mes4 95
1 92 Mes5 88


How can I do this?
Click to expand...

A "Normalizing Union Query" will do it - but surely the information exists in
a more normalized form in the table(s) upon which the crosstab is built? Can't
you do it from there?

If not, you'll need to use SQL view to create a UNION query:

SELECT [ID], [Item], "Mes1" AS Mes, [Mes1] FROM yourcrosstab
UNION ALL
SELECT [ID], [Item], "Mes2" AS Mes, [Mes2] FROM yourcrosstab
UNION ALL
SELECT [ID], [Item], "Mes3" AS Mes, [Mes3] FROM yourcrosstab
UNION ALL
SELECT [ID], [Item], "Mes4" AS Mes, [Mes4] FROM yourcrosstab
UNION ALL
SELECT [ID], [Item], "Mes5" AS Mes, [Mes5] FROM yourcrosstab

Base an Append query on this UNION query to populate the other table.
 
Back
Top