Re: Thank you with Design Assistance but...

I have to say this to all of you. I have spent the last 5 years, in my
spare time, learning object Oriented programing by use of Visual Basic and
have learned to emulate "real world" objects into classes, so hopefully the
experience will greatly assist my access learning curve.

What will assist my learning the most.. is you guys.

No offense to the VB people, but you all are amazing, responsive, and
informational. I have learned more in one posting and its threads then I
learned in my first year of postings on the VB side.
Thank you... Thank you ... Thank you.


"Pat Hartman(MVP)" <patsky@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:%23QpSEGwjGHA.4512@xxxxxxxxxxxxxxxxxxxxxxx
You already have several good answers to your questions so I'll just try
to add information. Although each Access database is limited to 2G, you
can link to as many Access databases or other ODBC databases for that
matter, as you need. So, in Access (actually Jet), a single table is
limited to 2G minus the overhead required for indexes.

When you create relationships, StoreID from the sales table to StoreID in
the store table, Access (Jet) creates hidden indexes on the foreign key
(StoreID in the sales table) to facilitate joins so you don't have to.

Just an FYI, most large tables can be searched via their index with very
few physical reads due to the search methods used. A very common method
is a binary search. The db engine finds the "middle" row of an index and
compares the search value to it. Then depending on whether the value is
high or low, the top half or bottom half of the set is halved again -
hence the term binary. I don't know the formula but it takes about 12
"half's" to find a particular record in a set of 500,000 and only 1 more
to find that same record in a set of a million.

People moving from Excel to Access have certain expectations. They think
that because a table in data*** view looks like a spread*** that it
acts like a spread***. Nothing could be further from the truth. The
biggest problem they run into is directly related to creating tables that
look like spreadsheets where you add columns as your data grows. In your
case, you were talking about adding a new column each week. The first
thing you would find is that there is no function that will sum/count/avg
the columns of a row. That means that you would need to write your own
formula - wk1 + wk2 + wk3 + wk4 + ... etc.. Then you would need to change
the formula next week to add a new column. You would also need to change
any forms or reports that displayed the data. Then you would scream -
Access sucks! Excel rocks! but you would be wrong. The problems would be
caused entirely by treating a database as a spread***. You saw the
simple queries that I suggested in a previous post to sum and average the
data. Those simple queries are ONLY possible if you NORMALIZE your data.
In a database application NOTHING is more important than table schema. If
you get the schema right, you can do most of your analysis with queries.
If you get it wrong, you'll be burning the midnight oil writing VBA to get
around your schema design.

"Wired Hosting News" <jtingato@xxxxxxxxxxx> wrote in message
news:128s82rhvr65o44@xxxxxxxxxxxxxxxxxxxxx
Thanks everyone. mnature.... good stuff.

Pat, After you answer the first two questions "NO". I said "Pat, Your
killing me"... It has to be one or the other!!!!
Then you explained the third option and (pardon the french), damn I
understand now. I have a real shift in thought to acheive.

I have created many OOP programs in C++ or visual basic and have always
done the legwork with classes. I hope using Access will be a simple
approach once I start to understand more completely.

Question Pat:
Each weeks sales reports have 1800 stores with 11 items each. Thats
19,800 line or records added to my sales table per week, 85,140 records
per month, and 1,021,680 records a year. All in one table.
Is that correct?
Can one table hold that many records? If so, why is Excel so lame? :)
What are my limits?

So basically you are saying that Access design is building relationships,
not data. And it is proper to pool many, many fields and many, many
records into one table, as long as there is not any redundancy.

With 1 million records to sort through, is Access efficient. We are
always taught to organize our dataand break it down into managable units.
I guess with Access, you pool it all together, assign keys and Access
does the internal organization itself... Correct?

Thanks again

John




"Pat Hartman(MVP)" <patsky@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:%238tyTvkjGHA.836@xxxxxxxxxxxxxxxxxxxxxxx
mnature has given you a lot of good general advice. I'll try to answer
some of your questions specifically so you'll have an idea of how to
proceed.
1. Should I create the sales tracking *** with a column for every
weeks sales? NO
2. Or should I create a new sales table every time I receive a new
weekly sales report? NO
3. Also how would I write the queries for , lets say, One Store, week
2/20 sales all items in that store.
If your tables are properly normalized, this query will give you total
sales by week, by store:
Select StoreID, Year(SalesDate) & "/" & Format(SalesDate,"ww") As
YearWeek, Sum(SalesPrice) as SumSalesPrice
From YourTable
Group By StoreID, Year(SalesDate) & "/" & Format(SalesDate,"ww");
If you want a single store and a single week, you can use a parameter
query to prompt you:
Select StoreID, Year(SalesDate) & "/" & Format(SalesDate,"ww") As
YearWeek, Sum(SalesPrice) as SumSalesPrice
From YourTable
Where StoreID = [Enter store ID] AND Year(SalesDate) & "/" &
Format(SalesDate,"ww") = [Enter Year/Week]
Group By StoreID, Year(SalesDate) & "/" & Format(SalesDate,"ww");
4. Plus I would also like to get average national sales for all stores
on any given SKU.
If your tables are properly normalized, this query will give you an
average for each store:
Select StoreID, SKU, Avg(SalesPrice) As AvgSalesPrice
From YourTable
Group By StoreID, SKU;
If you really want just a single store, you can use a parameter query to
prompt you:
Select StoreID, Avg(SalesPrice) As AvgSalesPrice
From YourTable
Where StoreID = [Enter store ID]
Group By StoreID, SKU;

The first two questions were two possibilities for creating the sales
data table but there is a third alternative which is to "normalize" the
data. mnature suggested this. By normalizing your data, you can see by
the answer to 3 and 4, the queries to analyze your data are quite
simple. I gave two examples of each. Any of the queries can be used as
the RecordSource for a report so you can pretty up your analysis.

Moving from Excel to Access requires that you take off your spread***
cap and put on your relational database cap. Spreadsheets in general
are short and wide - you add columns to extend data such as your
question #1. As you already know, this can involve modifying formulas.
The problem is much worse if you attempt this design style in a
database. Some people call it committing Spread***. In a database,
tables are long and narrow. You don't add columns to a table which
would involve modifying, queries, forms, reports, and possibly code.
Instead, you add rows. No modifications to anything are required.

Your Sales table will look something like:

SalesRecID (autonumber primary key)
StoreID (foreign key to store table)
ProductID (foreign key to product table)
SalesDate (preferably this will be a real date. If the retailer is only
sending you week #, you should convert this to the date of the first day
of the week (Sunday))
SalesPrice

Before you invest a lot of time in this database, so some serious study
of relational database design and normalization. It will save you lots
of trouble in the long run.


"Wired Hosting News" <jtingato@xxxxxxxxxxx> wrote in message
news:128qs2f3vh7iie8@xxxxxxxxxxxxxxxxxxxxx
Can anyone assist me in my design?

Real world:
I am a manufacturer of goods sold to a national retail chain. I have 10
different items in 1800 stores.
Every week I get a report from the chain that shows inventory levels
and #
of purchases that week for each item, each store; 18,000 line on a
spread***.

In Excel :
I have a work*** "Store List" which has all the information about the
stores; Store number, address, regional info, etc. all pertinent info
the
chain uses to describe its stores.

I have a work*** "Product List" that has all info on each of the 11
items,
SKU number, our part number, size, weight, cost, retail, description,
etc.

I have a work*** "Sales Data" that contain a line for every store,
every
item... a reflection of the reports received from the chain with an
inventory column, sales column, and on order column for each weeks
report I
receive. I transfer over the Inventory and sales data to a new column
by
hand.

I have written many functions and macros in Excel to analize the data,
but it is very
inefficient.
I would like to utilize access to improve performance and ease of
programming future improvements.

So far I have:

A store table: Describes any one store with all the store data from the
"store list" work***
A Product Line Table: Which creates an array of 10 item classes
A Sales tracking Table: With store number and each of the items SKU
number.

Should I create the sales tracking *** with a column for every
weeks sales?

Or should I create a new sales table every time I receive a new weekly
sales report?

Also how would I write the queries for , lets say, One Store, week 2/20
sales all items in that store.

Plus I would also like to get average national sales for all stores on
any given SKU.

Can anyone recommend a good book on this subject?


Any direction will be appreciated.
Thanks











.

Quantcast