T
T Kirtley
I have a linked table to an Excel sheet that includes columns for the past 12
months. I need a query (or vba code) that will normalize the data into one
column for month values, and multiple rows for each month.
Starting with a structure like:
State Dec2004 Jan2005 Feb2004 Mar2004
OH 2 3 4
5
MI 20 30 40
50
NY 200 300 400
500
I want to create a structure like:
State Month Widgets
OH Dec2004 2
OH Jan2005 3
OH Feb2005 4
OH Mar2005 5
MI Dec2004 20
MI Jan2005 30
MI Feb2005 40
MI Mar2005 50
NY Dec2004 200
NY Jan2005 300
....
It is easy enough to write a UNION query to do this for a known list of
months, but since the months included in the linked spreadsheet columns
change each month I need a way to identify the months that are in the linked
sheet and to run a union query that creates rows that identify all the date
values that are found in the sheet.
I am a bit rusty with ADO, but I suspect that the answer lies in that
direction. Can someone please guide me towards the best way to do this?
Thanks,
TK
months. I need a query (or vba code) that will normalize the data into one
column for month values, and multiple rows for each month.
Starting with a structure like:
State Dec2004 Jan2005 Feb2004 Mar2004
OH 2 3 4
5
MI 20 30 40
50
NY 200 300 400
500
I want to create a structure like:
State Month Widgets
OH Dec2004 2
OH Jan2005 3
OH Feb2005 4
OH Mar2005 5
MI Dec2004 20
MI Jan2005 30
MI Feb2005 40
MI Mar2005 50
NY Dec2004 200
NY Jan2005 300
....
It is easy enough to write a UNION query to do this for a known list of
months, but since the months included in the linked spreadsheet columns
change each month I need a way to identify the months that are in the linked
sheet and to run a union query that creates rows that identify all the date
values that are found in the sheet.
I am a bit rusty with ADO, but I suspect that the answer lies in that
direction. Can someone please guide me towards the best way to do this?
Thanks,
TK