Design Assistance please

[Wired Hosting News]

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
spreadsheet.

In Excel :
I have a worksheet "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 worksheet "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 worksheet "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" worksheet
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 sheet 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

 

[Guest]

There are a number of Idiot's Guide type books about Access, which are good
for just getting your feet wet. For guides on database design, I would
recommend Hernandez' book, Database Design for Mere Mortals (now in 2nd
edition), and O'Reilly's Access Database Design & Programming. The one main
reference book (and I emphasize that it is for reference) would be Microsoft
Office Access 2003 Inside Out.

If you have worked with spreadsheets, you will have to relearn everything
you think you know about data management.

Just to condense some information for you:

First Normal Form:
1) Eliminate repeating groups in individual tables.
2) Create a separate table for each set of related data
3) Identify each set of related data with a primary key

Do not use multiple fields in a single table to store similar data. For
example, to track an inventory item that may come from two possible sources,
an inventory record may contain fields for Vendor Code 1 and Vendor Code 2.

What happens when you add a third vendor? Adding a field is not the answer;
it requires program and table modifications and does not smoothly accommodate
a dynamic number of vendors. Instead, place all vendor information in a
separate table called Vendors, then link inventory to vendors with an item
number key, or vendors to inventory with a vendor code key.

Second Normal Form:
1) Create separate tables for sets of values that apply to multiple records
2) Relate these tables with a foreign key

Records should not depend on anything other than a table's primary key (a
compound key, if necessary). For example, consider a customer's address in an
accounting system. The address is needed by the Customers table, but also by
the Orders, Shipping, Invoices, Accounts Receivable, and Collections tables.
Instead of storing the customer's address as a separate entry in each of
these tables, store it in one place, either in the Customers table or in a
separate Addresses table.

 

[Guest]

Comments within text . . .

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
spreadsheet.

In Excel :
I have a worksheet "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.

It is both good and bad to have created Excel worksheets before converting
to a database format. The good news is that you have a lot of information,
which makes it easier to figure out what you want to put into the database.
The bad news is that your information is in a worksheet format, and you will
now have to go through an unpleasant process called normalization, which will
massage your data into a database format, which is what will allow you to
mine your database for information.

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

Sounds like you can create a Products Table with your current data. You
will need a unique primary key for each individual product.

I have a worksheet "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.

You will want to create a Stores Table, which will have all the data on your
stores (but just that data which is specific to a store). You then need a
Sales Data Table, which will have a primary key to uniquely identify each
sales. The table will include at least two foreign keys, which will refer to
a product and a store. This table will probably contain more records than
any other, because of the number of items being sold. Don't worry, Access
can handle a lot of records in a table. You will eventually be creating
queries/forms/reports that help to sort out this data into legible
information.

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.

If you set up your tables correctly from the beginning, you will be amazed
at how quickly and easily you can generate analyses of your data.

So far I have:

A store table: Describes any one store with all the store data from the
"store list" worksheet

All stores will be included in the Stores Table. Only store data will be
included in the Stores Table.

A Product Line Table: Which creates an array of 10 item classes

Array? No, no. If you want to assign an item class to a product, then use
a separate Item Class Table, where every item class has a unique primary key,
then reference that primary key in your Products Table. What happens if you
decide you need 11 item classes? Or 20 item classes? A product table should
only contain data about the product. Every field in that table should have
unique information about that product.

A Sales tracking Table: With store number and each of the items SKU number.

Sales Data Table, referencing stores by using a foreign key, and referencing
products by using a foreign key.

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

NO! This is not a worksheet, it is a database. Your tables should bear
only a passing resemblance to a worksheet, in that they do have what look
like columns and rows. You will not be entering data, or looking at data,
using your tables. You will use queries, tables, and reports to filter and
organize your data into information.

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

NO! You should have ONE Sales Table, and all sales will go into that table.
You simply have enough data in that table to be able to create the forms and
reports that you require. The data in a sales table should probably include
foreign keys to identify the product and the store. It should also include
the date (which is how you can then generate daily, weekly, or monthly
reports on sales). All data about a sales should be included in a sales
table, but only data about a sale should be included in a sales table.

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.

Once your tables are set up and properly normalized, then we can talk about
how to write queries/forms/reports.

 

[Pat Hartman\(MVP\)]

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 sheet 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 spreadsheet 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 Spreadsheet. 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.

 

[jahoobob via AccessMonster.com]

Pat, did you mean for the Sales table to have the info from the stores? If
so, it would need Inventory, Sales, and On Order as well.

Wired Hosting News,
If you can get the stores to send you their weekly reports in worksheets
(Excel) with the info needed in the Sales table and their Store Numbers and
Parts number match the ones you have in your two other tables, you can import
their data into the Access tables, eliminating the 1800 manual line entries
you have to make each month.

Good move on your part to move this to Access.

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 sheet 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 spreadsheet 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 Spreadsheet. 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.

Can anyone assist me in my design?

[quoted text clipped - 51 lines]

Any direction will be appreciated.
Thanks

 

[Wired Hosting News]

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

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 sheet 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 spreadsheet
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 Spreadsheet. 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.

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
spreadsheet.

In Excel :
I have a worksheet "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 worksheet "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 worksheet "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" worksheet
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 sheet 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

 

[Wired Hosting News]

Yes, I receive an Excel worksheet with all the sales data weekly
Report date
Store Number
SKU Number - The Store's Part ID
Mfg Part Number- Our Part ID
Sold quantity - For that week
Inventory - levels at the end of the week

The on order information comes from our accounting records, and I assume it
should be in its own table. Yes? No?

Since the main number defining an given item is the SKU Number, the Part ID
can be stripped, because it is already in the Product Line Table.

So my options are:

I can write an Excel macro to open the database and line by line add new
records by use of SQL statements
Format the Excel sheet to match the table columns and copy and paste
into the table
Or Use an import.

Yes?

Pat, did you mean for the Sales table to have the info from the stores?
If
so, it would need Inventory, Sales, and On Order as well.

Wired Hosting News,
If you can get the stores to send you their weekly reports in worksheets
(Excel) with the info needed in the Sales table and their Store Numbers
and
Parts number match the ones you have in your two other tables, you can
import
their data into the Access tables, eliminating the 1800 manual line
entries
you have to make each month.

Good move on your part to move this to Access.

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 sheet 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 spreadsheet
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 Spreadsheet. 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.

Can anyone assist me in my design?

[quoted text clipped - 51 lines]

Any direction will be appreciated.
Thanks

 

[jahoobob via AccessMonster.com]

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?

Excel isn't lame, it just isn't a database. Access is meant to "hold" tons
of data in a bucket (a table) with the means to dip into that bucket and
bring out whatever information you want it to, and organized too. If memory
serve me correctly, you are limited by the size of the database not the
number of records

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.

Access is all about the data with relationships. Excel database is a flat db
in that you have to put all the information you want asscociated with certain
data in the same row so you have to store say the stores location with each
part for the data to be meaningful. With Access, you can store the store's
location once and associate it with every "row" of parts.

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?

Access gives you the tools (queries and filters) to break the data into those
manageable units. You need not worry what the data "looks like" in the table
because it is just a bucket to hold it. You can reach into that bucket and
bring out the total of each of the 11 parts ordered by the 1800 stores for
any given time period (month, year, decade, etc)

Thanks again

John

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

[quoted text clipped - 122 lines]

 

[Guest]

Just jumping in, with notes within text . . .

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?

That sounds about right.

Can one table hold that many records? If so, why is Excel so lame?

hehe. Excel is an excellent tool for what it does. If you try to use Excel
as a database, then it is lame. If you try to use Access as a spreadsheet,
then it is lame. You can use a hammer and chisel to remove a bolt, but it
just isn't as effective as using a wrench.

What are my limits?

Limits? We don't need no stinkin' limits! You are only limited by how much
hard drive space and RAM you have on your computer, and how many other
programs you are running at the same time. A million records per year is
completely reasonable. Plus, you can keep many years worth of records, and
do all sorts of fun statistical graphs.

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.

Most tables only need between five and twenty fields. Any more than that,
and you should question whether you have truly normalized your tables. You
want to eliminate redundancy in order to make the database more effective.
Tables should always be a natural grouping of data. You should not put store
data in your products table. You should not put product data into your sales
table.

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?

The tables are the manageable, organized units. All product data belongs in
one table. Only product data belongs in that one table. How is a product
associated with a sale? Through a table that uses the unique identifier from
the products table, and the unique identifier from the stores table, in order
to create a unique record of a specific sale. And even if that product gets
sold in that store again, there will be a unique identifier in the sales
table that distinguishes between the two sales.

If you look at these various tables, you could have a hard time picking out
the specific data that you are trying to mine out, because there can be
millions of records in your sales table. That is where you let Access do the
work for you, by creating specific queries, forms, and reports that let you
filter and sort the data into whatever information you need.

You will want to create some queries/forms/reports that do the same job over
and over. You may want to see what the previous week/month/year sales are.
You may want to see which product sold the best in the previous
week/month/year. But if someone asks which product sells best, depending on
which store, in a particular month, you can quickly create a query and report
to do that. If you want to change that to which product sold worst,
depending on which store, in a particular month, you can quickly modify the
query to produce that information.

 

[Immanuel Sibero]

Hi Wired,

Just jumpin in here, I'm sure Pat will also answer the questions.

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?

Yes, that is correct. The limit is the size of the mdb file; 2GB. So you
would want to watch the size of the mdb file to determine if you need to
upsize to something like SQL Server.
Also note that Access has compact/repair option. Be sure to use it once in a
while.
You should also split your application into Front End (i.e. all user
interface objects - forms, queries, reports, etc) and Back End (i.e. tables
only)

Can one table hold that many records? If so, why is Excel so lame?

Yes a table can hold that many records.
Excel is lame (within the context of this discussion) because Excel is
designed to be a product to analyze data not process data (as is the case
with Access). Analyzing data demands realtime and fast response/interactions
to the user, so Excel is implemented to run in memory (RAM). All data that
Excel works with has to be in RAM, so you're limited to the amount of your
RAM. This is why Excel cant handle hundreds of thousands of records and
therefore the perception of *lame*. But it's not lame, it's just designed
for a different purpose.

What are my limits?

2GB per mdb file.

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.

Not quite.
Yes redundancy is an objective but not the only one. Designing in Access
(i.e. in any other relational database system, for that matter) is modelling
a real world into a database application. The relational database design
makes use of Entity Relationships (ER) concepts to extract the real world
model into the database application.
So to restate what you were saying: Access design is emulating some real
world situation into an application by using ER concepts (i.e. implementing
real world *entities* into tables and defining how those entities are
*related* to one another - one to many, many to many, etc).

Is it proper to pool many, many fields and many, many records into one
table, as long as there is not any redundancy. NO.
What's proper is to construct tables based on the ER concepts above.

With 1 million records to sort through, is Access efficient.

Absolutely. That's what Access is designed for.

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?

Not quite.
Again, you pool all your data together based on the ER concepts. Some ER
concepts requires pooling certain data items, while others require
separating certain data items. In a way, what you stated about manageable
units is actually true. ER concepts break down some real world situation
into manageable units in the forms of tables and realtionships.


HTH,
Immanuel Sibero

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

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 sheet 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 spreadsheet
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 Spreadsheet. 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.

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
spreadsheet.

In Excel :
I have a worksheet "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 worksheet "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 worksheet "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" worksheet
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 sheet 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

 

[Pat Hartman\(MVP\)]

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 datasheet view looks like a spreadsheet that it acts
like a spreadsheet. 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 spreadsheet. 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.

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

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 sheet 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 spreadsheet
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 Spreadsheet. 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.

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
spreadsheet.

In Excel :
I have a worksheet "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 worksheet "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 worksheet "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" worksheet
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 sheet 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

 

[Wired Hosting News]

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.

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 datasheet view looks like a spreadsheet that it
acts like a spreadsheet. 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 spreadsheet. 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.

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

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 sheet 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 spreadsheet
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 Spreadsheet. 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.


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
spreadsheet.

In Excel :
I have a worksheet "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 worksheet "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 worksheet "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" worksheet
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 sheet 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