RE: Design Assistance please

Comments within text . . .

"Wired Hosting News" wrote:

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.


.

Relevant Pages

  • Re: Help PLEASE! Not sure what answer is: Match? Index? Other?
    ... you need what Excel calls a list. ... would contain the name Area, B1 the name Store, C1 would contain the name ... you establish a pivot table. ... type the name of the sales data range -- I suggested SalesData ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Astronomy vendors and their "sales"
    ... their sales continue to dry up. ... electronics store in my area that was closing down a few years back. ... knew Circuit City was going out of business, I decided to make a trek ... Since the economic distress took hold this autumn, liquidators have ...
    (sci.astro.amateur)
  • Re: Thank you with Design Assistance but...
    ... (StoreID in the sales table) ... caused entirely by treating a database as a spreadsheet. ... Also how would I write the queries for, lets say, One Store, week ... the RecordSource for a report so you can pretty up your analysis. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Thank you with Design Assistance but...
    ... Although each Access database is limited to 2G, ... (StoreID in the sales table) ... You saw the simple queries that I ... Also how would I write the queries for, lets say, One Store, week ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Todays Priceless Supermarket Moment
    ... look like they are working hard to keep your business. ... You've never worked in a grocery store, ... waiting to bring in the next customer. ... And most of our sales come from repeat customers. ...
    (rec.food.cooking)