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]

It seems you already know that Access (database) is very different than Excel(spreadsheet).
For the database, I'd suggest you create a model of exactly what you're dealing with.

Stores Products ProductTypes Sales Locations

To get the proper database structure, you need to design it.
The key to design is Normalization.

See the first few examples here

http://www.rogersaccesslibrary.com/forum/topic238.html

 

[Norie]

Access might not be able to do the same 'analysis' that Excel can but there's nothing stopping you storing the data in Access and exporting it to Excel.

You could probably do some of it in Access though.

PS What do you mean by 'dimensions'?

 

[xenou]

In a database you would typically store data in one or more tables as records. You usually don't try to display it as it would appear in a report, or honestly as many would store it in an excel spreadsheet.

Much simplified but something like this:
---------------------------------------------------
| ProdType  | Store | DateOfSale | State | Amount |
---------------------------------------------------
| Sunscreen | WM    |      1-Jan | CA    |  $5.00 |
| Sunscreen | WG    |      2-Jan | NM    |  $4.00 |
| Sunscreen | WM    |      3-Jan | CA    |  $3.00 |
| Sunscreen | WM    |      1-Feb | CA    |  $6.00 |
| Sunscreen | WG    |      1-Feb | CA    |  $7.00 |
| Sunscreen | WG    |      3-Feb | UT    |  $2.00 |
| Sunscreen | WG    |      4-Feb | UT    |  $6.00 |
| TanOil    | WG    |      5-Jan | CA    |  $4.00 |
| TanOil    | WG    |      5-Feb | CA    |  $5.00 |
| TanOil    | WG    |      6-Feb | CA    |  $1.00 |
| Sunscreen | WG    |      6-Feb | UT    |  $3.00 |
| Sunscreen | WG    |      7-Feb | AZ    |  $2.00 |
| Sunscreen | WM    |      1-Jan | UT    |  $8.00 |
| Sunscreen | WG    |      2-Jan | CA    |  $6.00 |
| Sunscreen | WG    |      3-Jan | CA    |  $4.00 |
| Sunscreen | WG    |      1-Feb | UT    |  $7.00 |
| Sunscreen | WG    |      1-Feb | UT    |  $4.00 |
| Sunscreen | WM    |      3-Feb | CA    |  $5.00 |
| Sunscreen | WG    |      4-Feb | AZ    |  $2.00 |
| TanOil    | WM    |      5-Jan | AZ    |  $6.00 |
| TanOil    | WM    |      5-Feb | AZ    |  $4.00 |
| TanOil    | WG    |      6-Feb | AZ    |  $2.00 |
| Sunscreen | WG    |      6-Feb | AZ    |  $3.00 |
| Sunscreen | WG    |      7-Feb | AZ    |  $4.00 |
---------------------------------------------------

It don't look like much but we can slice or dice this any way you like - databases have different rules about storing data but once you put it in correctly they are great for getting it back out.

A compromise position (as suggested by Norie) would be to store the raw data in Access and work with it in Excel - you could output the data to a "data" in the workbook and use your formula skills, pivot tables, or what have you to view it and summarize it on a "report" sheet.

 

[Gingertrees]

Norie: by "Dimensions" I mostly just mean I'm measuring much more complex data than my previous database-creations. It doesn't help that I'm still going to MS office help far too often as I become exasperated asking "OK, I knew where it was in the menus, where did they hide ______ in the &*$#ing ribbon?"

jackd: Thanks for the link on Normalization. Before, I've always worked with much simpler databases that allowed me to get by with less focus on normalization. I'll read through that site and try again. I'll post more questions as I come up with them.

Excellent timing, too, since I just figured out how to produce reports to serve my need in this case - but I then realized that while this works in the short term, by year's end, I would need to create over 100 separate queries to continue in that vein! Eep!

 

[SydneyGeek]

You shouldn't need to change the queries once the correct structure is in place.
If you look at xenou's suggested table layout a couple of comments may be helpful:
1. No change in the table structure. Just add more rows as the data grows.
2. To get to the report layout you can build a crosstab query, which pivots the months across the top and summarises the rest of the data for you. That data can be exported to Excel for further analysis, or used as the basis of an Access report. You can either leave the number of columns to expand (the limit is 255 cols) or you can apply a filter to only show the last year / financial year.

Denis

 

[Gingertrees]

OK y'all, I get normalization in theory, but I cannot see how to apply it to my situation. I'm still not real clear on how I'm supposed to NOT have redundant data when, well, it seems like it NEEDS to be redundant. Also I already have so MUCH data I thought it would be unwieldy to put multiple stores' data in one main table, as this normalization seems to suggest.

So I took a stab at it. In hoping I could somehow mash two queries together for a report at some point, I decided to concentrate on just tanning data. So my tables are Walmart tanning data, Target tanning data, CVS tanning, etc. (I did the same thing for sunscreen, but I figured concentrating on one was good enough to wrap my head around it.)

I built a bunch of smaller tables (as in, 50,000 records each vs. a million +) in the hopes of being able to draw their data together with queries/reports. So far... no success.
I created:

• a table for the two products (Product ID/Product name), so lines reference 1 or 2 instead of "tanning" or "sunscreen"
• a States table, containing all 50 states and who the salesperson is for each state (here's an example of not understanding normalization. I have this set up at 1 state to many state-records in each main store table... but that seems kind of many-to-many, as each state can have many records/sales, but each store(part of the sales) does business in many states)
• a Salespeople table, which includes just a list of all the salespeople (linked to the States table in 1-to-many relationship)
• 8 main store tables: WM tanning, WM sunscreen, WG tanning, WG sunscreen, etc.
• each main store table: ID/product ID/ StoreID/State /SaleMonth/SaleYear /SaleAmt

This means that StateID is linked to the state field in WM tanning, WG tanning, etc. This produces a fat nothing in the way of every more-than-1-table query I've tried to run off of it. From looking at the SQL, it seems to think that since target, walmart, cvs, and walgreens are linked to the state table, every record of target should thereby be linked to every record from Walmart! And...that wasn't the goal.

1 Goal: From the above tables, build a query to summarize amounts by state for Walmart, for Target, etc. I achieved this goal in the past by writing 4 summary queries (1 q. for WM, 1 q. for Target, 1 for..), and then doing a query on the 4 queries. Is there a way with ONE query on my data? Or did I hit the nail on the head with my messy method?

Anyway, thoughts on my situation? I'm dreaming about bloody Access relationships at this point...

 

[xenou]

8 main store tables: WM tanning, WM sunscreen, WG tanning, WG sunscreen, etc.

Of the top of my head, I'd expect just one table: Stores, or StoreSales, something like that.

Then this still works I think, but all in one table:

• ID/product ID/ StoreID/State /SaleMonth/SaleYear /SaleAmt

The StoreID field is enough to allow you to query for individual stores, or more than one store, all from the same table. If a store is in a state and that's what state means, you don't really need it in the table since the state can be determined from the store. But if the state is the state of the sale (a store on a border could sell in two states, for instance), then you need the state in there - which appears to be the case now that I read your comments again.

For sales reps and states, etc., your setup is limiting but may work for now. It sounds like you're assuming only one sales rep per state. If that's the case you can put the sales rep in the states table: State/Sales Rep. And you can include the sales reps in queries based on the state. But if a state can have more than one sales rep you need more tables to handle that kind of many to one thing going on - something to consider.

None of this is easy, but it sounds like you have an excellent candidate for doing this work.

 

[Gingertrees]

Of the top of my head, I'd expect just one table: Stores, or StoreSales, something like that.

I don't know the limits of Access tables, but don't you think a table of over a million records would be somewhat unwieldy? I mean, I get that the bestest way would be one table - I got that from Roger's Access blog - I was just a bit scared by the sheer volume here.

It is the state of the sale, but store is a misleading pseudoname. When I say I'm getting data from Walmart, Target, etc, I don't mean "the Walmart at 300 Main St, Smithville MI." I mean Walmart, Inc. All the sales that all the Walmarts in all of Michigan that have been reported for a period, combined with all the sales from all the Walmarts in Ohio, Kansas, California, etc.
1 month of data for 1 of these 4 giants can be 50K+ records.

Another thing that concerned me about creating a supermassive blackhole of a table: I'm still running XP at work. Running 2 Office Suite prgs at once and doing much of anything else has been known to crash my work PC.

Any insight on the above misgivings? As I said, while I'm no stranger to Access, dealing with this volume of data is a first.

 

[SydneyGeek]

Access will handle that much data in one table.
If you're worried about the capacity you can put the data into SQL Server Express (free, with a capacity of 10GB for 2008 R2 vs the 2GB for Access).
It's still possible to build the interface (forms, queries, reports) in Access, pointed at the SSE backend database.

Denis