Separating implicit data from Access table formatted like an Excel table

[mork283]

Good afternoon,

I've inherited an Access database containing tables formatted more like Excel tables. The table I'm working on now looks like...

PK	Year	Profit Center	SalesmanID	January Units	February Units	March Units	etc...	January Margin	February Margin	March Margin	etc...
1	2016	5	15	5,000	7,500	6,500	...	55.25	51.50	53.00	...

I tried a union query to reorganize the data by month and type (units or margin), but I wasn't able to keep the PK, Year, Profit Center and SalesmanID as they were. So, I'm looking for tips on how to get my data into the following format.

PK	Year	Month	Profit Center	SalesmanID	Units	Margin
1	2016	1	5	15	5,000	55.25
1	2016	2	5	15	7,500	51.50
1	2016	3	5	15	6,500	53.00

Any suggestions?

Thanks,

[June7]

What do you mean by "wasn't able to keep the PK, Year, Profit Center and SalesmanID as they were" - why not? A UNION query should provide the desired output. Post the attempted query. Something like:

SELECT PK, [Year], 1 AS [Month], [Profit Center], SalesmanID, [January Units] AS Units, [January Margin] AS Margin FROM tablename
UNION SELECT PK, [Year], 2, [Profit Center], SalesmanID, [February Units], [February Margin] FROM tablename
...
UNION SELECT PK, [Year], 12, [Profit Center], SalesmanID, [December Units], [December Margin] FROM tablename;

Year and Month are reserved words and should not use reserved words as names for anything. Also advise no spaces or punctuation/special characters (underscore only exception) in names.

[mork283]

To answer your first question, turns out I just had the format incorrect in my initial Select and saw it when I compared to your query. The rest works like a charm! Thank you!