How to organize data to produce Excel-like results???

[Gingertrees]

I've got a bunch of data in Excel, but it's getting to be too much work to maintain and manipulate - need to move it into Access. Pretend I'm analyzing data on one brand's sun lotion sales at large-volume stores nationwide.
I need to analyze data on sales in the 5 following dimensions:
1 - Store: Walmart, Walgreens, Target, and CVS.
2 - Type: Sunscreens vs. tanning oils
3 - Date of sale: for comparing 2 or more months for seasonal trends, YTD, etc.
4 - State of sale (all 50)
5 - Amount of sale ($)

That seems to be too many dimensions for Access to handle. In Excel, I've got 2 spreadsheets for each store, set up like this:
(expand your screen for the "sheets" below!)

SHEET: Walmart-Sunscreen
======================================
State....Mar2011...Apr2011..May2011...
AK.................$35......$130
AL.......$300.....$480......$554
AR.......$420.....$532......$550
CT................$39.......$155

SHEET: Walmart-Tanning
======================================
State....Mar2011...Apr2011..
AK.......$40.......
AL.......$130....
AR.......$200.....
CT.............

SHEET: CVS-Sunscreen
======================================
State...Mar2011....Apr2011...
AK......$

SHEET: CVS-Tanning
======================================
(etc)

I use VLOOKUPs to place them into reporting sheets, such that it looks like this:
SUNSCREEN_________________________|+|TANNING_________________________________
........MAY 2011.......//....JUNE 2011...... |+| .....MAY 2011......//...JUNE 2011......
......WM.WG.TGT..CV..//.WM.WG.TGT.CV|+| WM.WG.TGT.CV.//.WM.WG.TGT.CV.
AK.|..$$....$....$....$..//..$$..$....$....$...|+| ..$$..$....$....$..//..$$..$....$....$
AL.|..$$....$....$....$$.//..$$..$....$....$...|+| ..$$..$....$....$..//..$$..$....$....$
AR.|..$$....$....$....$..//..$$..$....$....$...|+| ..$$..$....$....$..//..$$..$....$....$
AZ.|..$$....$....$$...$..//..$$..$....$....$...|+| ..$$..$....$....$..//..$$..$....$....$

(Sorry I don't have HTML maker or whatever y'all use now)

It seems like I should be able to do this in Access, but I've never had to keep track of so many dimensions before! I tried making 1 gigantic table, where every record included every bit of data, but that was unmanageably big, and growing with each passing month. I tried importing my tables as they are in Excel, but I couldn't get the results like I wanted above.

What am I missing???? I'm so ashamed that I can't get this right... please help. Thanks.

 

[jackd]

Table structure is key. The volumes, as has been stated, can be accommodated. If xenou's [post #4] layout ( if you add SaleYear) will handle your base data, that could be sufficient.

 

[xenou]

My largest table that I work with regularly has about 700,000 records in it - and it doesn't even blink when I query it. Should work - I'd start with a test of concept implementation (which I think is what you've been doing - using a sample like Walmart stores only).

Where's this data coming from? Is it being generated from another system/database that you're pulling from?

 

[Gingertrees]

I get sales data from the large stores in Excel or CSV format, and I chop it and refine it in Excel before import by Access.

 

[xenou]

Okay - makes sense. You're on the right trail but it takes time when you are learning. Heck, it takes time anyway. Database work generally means more time spent developing "up front" (building the database), but usually pays back "later on" (when you can do your work 10 times faster).