Any way to shift data axes?

[S Davis]

Helo all,

I am having problems getting some data into the format I need. I'm
wondering if this is even possible actually. I'm sure it is, but I'd
prefer to keep things simple.

Data is coming in from a linked Excel table in a format like the
following

MRC VEH 1 2 3 4 5
101 AAA 12 34 56 78 90
101 BBB 12 34 56 78 90
101 CCC 12 34 56 78 90
102 AAA 12 34 56 78 90
102 BBB 12 34 56 78 90
102 CCC 12 34 56 78 90
103 etc...

MRC and VEH denote the cost centre and vehicle types. For those
combinations is an outlay (1,2,3,4,5) to display the coming months,
and their allocated budgets.

I'd like to use this table in Access, but I need the information
diplayed vertically, such as the following:

MRC VEH Mth VALUE
101 AAA 1 12
101 AAA 2 34
101 AAA 3 56
101 AAA 4 78
101 AAA 5 90
101 BBB 1 12
101 BBB 2 34
101 BBB 3 56
101 BBB 4 78
101 BBB 5 90
101 CCC 1 12
101 CCC 2 34
101 CCC 3 56
101 CCC 4 78
101 CCC 5 90
102 AAA 1 12
102 AAA 2 34
102 AAA 3 56
102 AAA 4 78
102 AAA 5 90
etc...

I'm sure you can all see what I'm getting at here. Unfortunately for
me, Idon't see any way to translate column names from an imported
table into a value that can be used (Mth).

If you can assist me in any way, I'd appreciate it!

Thanks,
-SD

 

[S Davis]

Helo all,

I am having problems getting some data into the format I need. I'm
wondering if this is even possible actually. I'm sure it is, but I'd
prefer to keep things simple.

Data is coming in from a linked Excel table in a format like the
following

MRC VEH 1 2 3 4 5
101 AAA 12 34 56 78 90
101 BBB 12 34 56 78 90
101 CCC 12 34 56 78 90
102 AAA 12 34 56 78 90
102 BBB 12 34 56 78 90
102 CCC 12 34 56 78 90
103 etc...

MRC and VEH denote the cost centre and vehicle types. For those
combinations is an outlay (1,2,3,4,5) to display the coming months,
and their allocated budgets.

I'd like to use this table in Access, but I need the information
diplayed vertically, such as the following:

MRC VEH Mth VALUE
101 AAA 1 12
101 AAA 2 34
101 AAA 3 56
101 AAA 4 78
101 AAA 5 90
101 BBB 1 12
101 BBB 2 34
101 BBB 3 56
101 BBB 4 78
101 BBB 5 90
101 CCC 1 12
101 CCC 2 34
101 CCC 3 56
101 CCC 4 78
101 CCC 5 90
102 AAA 1 12
102 AAA 2 34
102 AAA 3 56
102 AAA 4 78
102 AAA 5 90
etc...

I'm sure you can all see what I'm getting at here. Unfortunately for
me, Idon't see any way to translate column names from an imported
table into a value that can be used (Mth).

If you can assist me in any way, I'd appreciate it!

Thanks,
-SD

Alright, I built something that works. Basically, I went back into my
linked Excel table and chaged the Month column headings from numbers
into Month Names. Then went into a Union query and defined them all in
there. SQ follows.

This is a Union query...

SELECT [MRC] as MRC_Class, 1 as [Month],[VEH], Sept as [Budget] from
BudgetTableXL
UNION ALL
SELECT [MRC] as MRC_Class, 2 as [Month],[VEH], Oct as [Budget] from
BudgetTableXL
UNION ALL
[etc....]

Thanks for looking. Hope this helps someone in future.
-SD

 

[Guest]

This union query will do it --
SELECT S_Davis_1.MRC, S_Davis_1.VEH, "1" AS Mth, S_Davis_1.[1] AS [Value]
FROM S_Davis_1
UNION ALL SELECT S_Davis_1.MRC, S_Davis_1.VEH, "2" AS Mth, S_Davis_1.[2]
FROM S_Davis_1
UNION ALL SELECT S_Davis_1.MRC, S_Davis_1.VEH, "3" AS Mth, S_Davis_1.[3]
FROM S_Davis_1
UNION ALL SELECT S_Davis_1.MRC, S_Davis_1.VEH, "4" AS Mth, S_Davis_1.[4]
FROM S_Davis_1
UNION ALL SELECT S_Davis_1.MRC, S_Davis_1.VEH, "5" AS Mth, S_Davis_1.[5]
FROM S_Davis_1;

Use your table name instead of S_Davis_1.