Table structure change in VBA

[Ben_Entrew]

Hi all,

I get a table with the following structure:

Customer 1st Month 2nd Month 3rd Month
ABC 55 63 21


Each month is showing the quantities. The count of columns can be variable for each time.

How can I turn this structure into :

Months Customer Quantity
1st Month ABC 55
2nd Month ABC 63
3rd Month ABC 21

Thanks in advance.

Regards,
Ben

 

[JHB]

Use a union query.

 

[Ben_Entrew]

Hi JHB,
thanks for the hint.

How can I use a Union query if both tables don't have identical column names?

 

[JHB]

...How can I use a Union query if both tables don't have identical column names?...

Which both table?

...
How can I turn this structure into :

Months Customer Quantity
1st Month ABC 55
2nd Month ABC 63
3rd Month ABC 21
...

SELECT BaseTable.Customer, "1st Month" AS Month, BaseTable.[1st Month]
FROM BaseTable
UNION
SELECT BaseTable.Customer, "2nd Month" AS Month, BaseTable.[2nd Month]
FROM BaseTable
UNION
SELECT BaseTable.Customer, "3rd Month" AS Month, BaseTable.[3rd Month]
FROM BaseTable

 

[Ben_Entrew]

Thanks JHB,

this was the solution I was searching for.

Thanks again.

Regards,

Ben

 

[JHB]

You're welcome, luck with your project.