Ken,
I do not have time to work out the details but this is my first impression
of tackling this task:
Two Loops
Outer loop
read into a recordset the 6300 rows of data.
Inner loop for each row do the following
Create SQL statement to INSERT the common columns and the current column
value using the column name (Red in your example below into Color)
Go to next column value and repeat till row complete
Go to next row until all rows complete
Hal McGee
Engineering Data Manager
Group Engineering - Process and Compliance
Seating Products Group
B/E Aerospace
Winston-Salem, NC
"k_winterborne"
<dnkhavefuntogeth
[EMAIL PROTECTED]> To
Sent by: AccessDevelopers@yahoogroups.com
AccessDevelopers@ cc
yahoogroups.com
Subject
[AccessDevelopers] How do I do
10/12/2005 01:52 this?
PM
Please respond to
AccessDevelopers@
yahoogroups.com
I have a spreadsheet with 6,300 rows/records of data. This spreadsheet has
91 columns of data. 86 columns are data elements that correspond to the
rows of data with a value of zero or some other number. The zeros are
essentially irrelevant and looking at the entire spreadsheet is difficult
due to its size. Consequently, I would like to convert the columns of data
to rows and only indicate the rows that contain valid data.
Looks like this:
Item |Size |Red |Green |Blue |Yellow
------------+----------+---------+------------+----------+--------------
Item1 |M |2 |0 |4 |1
------------+----------+---------+------------+----------+--------------
Item2 |L |0 |0 |0 |3
------------+----------+---------+------------+----------+--------------
Item3 |L |0 |2 |0 |0
------------+----------+---------+------------+----------+--------------
Item4 |S |1 |0 |2 |0
>>
Want this:
Item |Size |Color |Qty
------------------+---------------+---------------------+--------------
Item1 |M |Red |2
------------------+---------------+---------------------+--------------
Item1 |M |Blue |4
------------------+---------------+---------------------+--------------
Item1 |M |Yellow |1
------------------+---------------+---------------------+--------------
Item2 |L |Yellow |3
------------------+---------------+---------------------+--------------
Item3 |L |Green |2
------------------+---------------+---------------------+--------------
Item4 |S |Red |1
------------------+---------------+---------------------+--------------
Item4 |S |Blue |2
Is there a way in Access or Excel to easily to do this which such a large
amount of data?
Ken
Please zip all files prior to uploading to Files section.
YAHOO! GROUPS LINKS
Visit your group "AccessDevelopers" on the web.
To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
------------------------ Yahoo! Groups Sponsor --------------------~-->
Get Bzzzy! (real tools to help you find a job). Welcome to the Sweet Life.
http://us.click.yahoo.com/A77XvD/vlQLAA/TtwFAA/q7folB/TM
--------------------------------------------------------------------~->
Please zip all files prior to uploading to Files section.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AccessDevelopers/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
