Table structure change in VBA (1 Viewer)

Ben_Entrew

Registered User.
Local time
Today, 15:06
Joined
Dec 3, 2013
Messages
177
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

Have been here a while
Local time
Today, 23:06
Joined
Jun 17, 2012
Messages
7,732
Use a union query.
 

Ben_Entrew

Registered User.
Local time
Today, 15:06
Joined
Dec 3, 2013
Messages
177
Hi JHB,
thanks for the hint.

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

JHB

Have been here a while
Local time
Today, 23:06
Joined
Jun 17, 2012
Messages
7,732
...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

Registered User.
Local time
Today, 15:06
Joined
Dec 3, 2013
Messages
177
Thanks JHB,

this was the solution I was searching for.

Thanks again.

Regards,

Ben
 

JHB

Have been here a while
Local time
Today, 23:06
Joined
Jun 17, 2012
Messages
7,732
You're welcome, luck with your project.
 

Users who are viewing this thread

Top Bottom