Convert data grid into multiple rows

[Guest]

I have a table that is linked to an Excel spreadsheet. The Excel
spreadsheet is made up of a column containing part numbers and then 12
columns for each monthy forecast for sales of that product. There is also a
header row that includes the dates for each forecast column.
I need to convert the data into another table with three columns - a
part number, a date/month, and a quantity. This table would have a row for
every combination of part number and date.
Can anyone guide me in how to do this?
Thanks!

 

[John Vinson]

On Tue, 5 Jul 2005 07:21:02 -0700, "Clint Marshall" <Clint

I have a table that is linked to an Excel spreadsheet. The Excel
spreadsheet is made up of a column containing part numbers and then 12
columns for each monthy forecast for sales of that product. There is also a
header row that includes the dates for each forecast column.
I need to convert the data into another table with three columns - a
part number, a date/month, and a quantity. This table would have a row for
every combination of part number and date.
Can anyone guide me in how to do this?
Thanks!

Good on ya! That's exactly what you need to be doing.

A "Normalizing Union Query" is the ticket here. Let's say your
spreadsheet has 13 columns, PartNo, Jan, Feb, Mar, Apr and so on; and
that you want three columns, PartNo, ForecastDate (Date/Time), Qty.

Create a query in the SQL window (you can't create a UNION in the
design grid, it would violate the negotiated labor agreement):

SELECT PartNo, DateSerial(Year(Date()), 1, 1) AS ForecastDate, Jan As
Qty FROM Spreadsheet WHERE Jan IS NOT NULL
UNION ALL
SELECT PartNo, DateSerial(Year(Date()), 2, 1) AS ForecastDate, Feb As
Qty FROM Spreadsheet WHERE Feb IS NOT NULL
UNION ALL
SELECT PartNo, DateSerial(Year(Date()), 3, 1) AS ForecastDate, Mar As
Qty FROM Spreadsheet WHERE Mar IS NOT NULL

<etc for 12 SELECT clauses>

Open this query and see if it's returning the right data; then base an
Append or a MakeTable query upon it.

John W. Vinson[MVP]

 

[Guest]

Wow! That's great!
In the interests of curiousity and future independence, can you tell me a
littl more about how this works?

1) What does the "DateSerial" piece do? How does it always know to pick the
date from the first row?
2) I presume the "is not null" part is just to avoid blank forecasts, or
does it do something else?
3) In general, what is the Union All doing? I've worked with a few Union
queries before, but never seen one work quite this way.
4) If we later expand to 52 weekly forecasts, do I just expand this to 52
chycles? Is there any way to incorporate a for..next type loop to keep it
manageable?

Thanks for your great help!!!

-Clint Marshall

 

[John Vinson]

Wow! That's great!
In the interests of curiousity and future independence, can you tell me a
littl more about how this works?

1) What does the "DateSerial" piece do? How does it always know to pick the
date from the first row?

It doesn't. DateSerial (as the Help file in VBA would tell you)
constructs a Date/Time from three arguments, integer year, month and
day respectively. I'm just making the assumption that you want this
year's January 1 inserted for all amounts in the column named Jan.

You would need some separate code (which isn't immediately obvious to
me) to extract the date itself from the first row.

2) I presume the "is not null" part is just to avoid blank forecasts, or
does it do something else?

That's it.

3) In general, what is the Union All doing? I've worked with a few Union
queries before, but never seen one work quite this way.

It's "unzipping" your spreadsheet by taking it apart, one column at a
time (the 12 SELECT statements) and stitching the parts back together
end to end (UNION). The UNION ALL (instead of UNION) doesn't check for
duplicate data and is therefore faster - and you don't want to
eliminate dups anyhow.

4) If we later expand to 52 weekly forecasts, do I just expand this to 52
chycles? Is there any way to incorporate a for..next type loop to keep it
manageable?

For... Next works in VBA; but VBA and SQL are two quite different
languages! SQL is non-procedural and does not support looping.

You might run into the QUERY TOO COMPLEX error with 52 Selects - if
you do, you might need to have 1-26 in one query, 27-54 (there can be
more than 52 weeks in a year) in a second.

Thanks for your great help!!!

You're most welcome.

John W. Vinson[MVP]