Sending Data to Access Database

[PattiP]

I have a spreadsheet for hourly energy sales and purchases. The spreadsheet
is set up something like this:

"Date, Company, ScheduleType, HourEnding1, HourEnding2,....HE24" across the
top of the sheet. I have a section for sales and a section for purchases
where the Megawatts (MW) sold or purchased are entered under each hour for
each transaction.
In another area of the spreadsheet I have a similar setup where the prices
are entered. I will eventually calculate the HE1 MW * HE1 price for each
transaction, but would like do all that in Access. Howver, instead of having
over 50 fields (ie, HE1MW, HE1price, HE2MW, HE2price....) I would like to
set up the Access database something like this:

"Date, HourEnding, Company, ScheduleType, Megawatt, Price" so that each
company/schedule type could have up to 24 records, 1 for each hour. Is
there a way to get the Excel data into a useable format to transfer the data
into ACCESS?

I guess one question I have that co-workers have asked me is: is it wrong to
have 50+ fields in my database?? I know there's always more than one way to
do things and I think it could be do-able but seems very cumbersome and not
the "right way" to do it based on previous database training that I've had.

 

[bpeltzer]

To me, the latter is easier to query. But I wouldn't get to concerned about
either approach.
If you do want to convert from 50 fields to 5, you can use the offset
function to take appropriate steps through the data. If your existing data
(sheet1) has 3 'header' columns, followed by 24 hourly columns, and your new
layout (sheet2) should have the 3 headers, plus a column indicating the hour
and a column for the value...
Sheet2 would have the headers in row 1. In A2:
=OFFSET(Sheet1!A$1,CEILING((ROW()-1)/24,1),0). Autofill that into B2 and C2.
In D2: =MOD(ROW()-2,24)+1. And in E2:
=OFFSET(Sheet1!C$1,CEILING((ROW()-1)/24,1),D2). You can autofill those
formulas through as many rows as you need to restructure the data.