RE: Design Assistance please
- From: mnature <mnature@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 12 Jun 2006 07:43:03 -0700
Comments within text . . .
"Wired Hosting News" wrote:
Can anyone assist me in my design?
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
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
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
I have written many functions and macros in Excel to analize the data, but
it is very
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
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
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
Once your tables are set up and properly normalized, then we can talk about
how to write queries/forms/reports.
- Design Assistance please
- From: Wired Hosting News
- Design Assistance please
- Prev by Date: Re: Access '07 Beta 2, "Record(s) cannot be deleted, no delete per
- Next by Date: RE: designing database
- Previous by thread: Design Assistance please
- Next by thread: Re: Design Assistance please