report layout from Excel

[Guest]

I have a spreadsheet that I would like to transfer data over to Access each
day for after-the-fact reporting. The spreadsheet is for electrical energy
hourly sales. It is set up with hours 1-24 across the top of the page. If I
were to set up an Access database with the following fields: Date, Hour,
Company, megawatts, Price/MW is it possible to have the reports print with
the hours across the top of the page rather than vertical or would I need to
set up the database with each hour as a field. I know that can be done, but
it seem cumbersome to me. All the after-the-fact reports that we compare
from others in the industry have the hours across the top of the page so
would be easier to check if we were consistent.

Thanks for any help!!

Patti

 

[John Spencer]

You should STORE the data vertically as you have suggested. I would have
only probably combine the Date and Hour into one field. You can extract
just the hour when you need it.

Your report can be based on a crosstab query which can generate one column
for each hour of the day - in addition to the other columns you want, such
as Date and Company.

 

[Guest]

Thanks, John. I'll give that a try. Kinda rusty on crosstab queries so will
study up on it.
I suppose this next question should be directed to the Excel help group? ....

How do I get the data in the Excel spreadsheet into a format that would
transfer easily into Access the way I want it? I'm thinking that I'll want
to copy the data into a 2nd worksheet with the numbers going vertical instead
of horizontal and then copy it into Access?

 

[Guest]

Thanks, John. I'll give that a try. Kinda rusty on crosstab queries so will
study up on it.
I suppose this next question should be directed to the Excel help group? ....

How do I get the data in the Excel spreadsheet into a format that would
transfer easily into Access the way I want it? I'm thinking that I'll want
to copy the data into a 2nd worksheet with the numbers going vertical instead
of horizontal and then copy it into Access?