Recap On Design Assistance

[Wired Hosting News]

OK... I have taken your advice and have change my way of thinking and
switched from Excel thoughts to Access thoughts. With respect for my
scenario of a manufacturer that sells 11 items to 1800 stores, I have
created the following tables with the following fields. Could you look at it
and tell me if I am on the right track?
As for setting up the relationships, I am a little confused.

Is this Normalized???

Also Pat... you had given me some query samples. Do I type those in on the
fly. Do they get coded into a VBA mod or a form mod. See bottom of this
page for what you sent me.

*** Store List Table ***
Store Number - Primary Key
Store Name
Market Number
Address
City
State
ZipCode
Phone Number
Fax Number

*** Products Available***
Part Number - Primary Key
UPC Number
Description
Size
Cost
Buy Pack

*** Products In the Chain Store *** One market has 2 extra items so I guess
I have to list every market and every skuu available in that market.
Market Number - Primary Key
Part Number
Sku Number

*** Sales Data Table ***
AutoGenerated - Primary Key
StoreNumber - Foriegn Key
Sku Number - Foreign Key
Inventory On Hand

*** Purchase Order Data Table ***
Store Number-
Sku Number
Purchase Order Number
Quantity Ordered
Due Date

Queries given by Pat:
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");

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;

 

[John Vinson]

OK... I have taken your advice and have change my way of thinking and
switched from Excel thoughts to Access thoughts. With respect for my
scenario of a manufacturer that sells 11 items to 1800 stores, I have
created the following tables with the following fields. Could you look at it
and tell me if I am on the right track?

Comments inline (and I hope some of the other good folks jump in).

As for setting up the relationships, I am a little confused.

You're well up into the foothills of the steep and rocky learning

Is this Normalized???

Not yet.

Also Pat... you had given me some query samples. Do I type those in on the
fly. Do they get coded into a VBA mod or a form mod. See bottom of this
page for what you sent me.

Neither. They go into the SQL window of a new Query.

*** Store List Table ***
Store Number - Primary Key
Store Name
Market Number

I'd avoid putting blanks in fieldnames; it's legal but requires that
you always use square brackets, and can cause trouble down the line.
"Camel Case" e.g. StoreName is just about as readable and less of a
problem.

Address
City
State
ZipCode
Phone Number
Fax Number

probably ok, provided each store has one and only one phone. If you
have multiple extensions you might want to split out a Phones table
with StoreNumber

*** Products Available***
Part Number - Primary Key
UPC Number
Description
Size
Cost
Buy Pack

If a given product PartNumber can come in multiple Sizes, each with
its own Price, you may need to split this into two related tables.

*** Products In the Chain Store *** One market has 2 extra items so I guess
I have to list every market and every skuu available in that market.
Market Number - Primary Key
Part Number
Sku Number

MarketNumber CANNOT be the Primary Key; doing so would limit you to
one and only one product per store, because the Primary Key is by
definition unique in the table. Select both fields and make them a
joint two-field primary key.

Is the SKU number in fact unique to a store? or is it properly an
attribute of the Product - i.e. does every store selling Widgets use a
different SKU for its widget?

*** Sales Data Table ***
AutoGenerated - Primary Key
StoreNumber - Foriegn Key
Sku Number - Foreign Key
Inventory On Hand

POSSIBLY ok - though some inventory systems calculate the inventory on
hand dynamically.

*** Purchase Order Data Table ***
Store Number-
Sku Number
Purchase Order Number
Quantity Ordered
Due Date

John W. Vinson[MVP]

 

[Wired Hosting News]

John... Thanks. I have now changed all field to CamelCase just as in all
other programming I have done. I am sure it makes it much more portable
also. Thanks. I also did the same to the table names.

Here are the answers to the question you had asked. Does that Change any of
your thoughts on my plight

Q.Is the SKU number in fact unique to a store? or is it properly an
attribute of the Product - i.e. does every store selling Widgets use a
different SKU for its widget?
A.The SKU number is assigned by a store chain and each item has a unique SKU
number throughout the chain, in my case, 1800 stores.
We also sell to other retailers and they all have their own SKU numbers.
Our part number or the UPC number never changes.

Q.MarketNumber CANNOT be the Primary Key; doing so would limit you to
one and only one product per store, because the Primary Key is by
definition unique in the table. Select both fields and make them a
joint two-field primary key.
A.I see. So basically each item in the "Active" list would be identified by
a combination key of market and SKU.

Q.If a given product PartNumber can come in multiple Sizes, each with
its own Price, you may need to split this into two related tables.
A. Each size has its own part number, UPC number, and SKU number

**********************************************************************************************

 

[John Vinson]

John... Thanks. I have now changed all field to CamelCase just as in all
other programming I have done. I am sure it makes it much more portable
also. Thanks. I also did the same to the table names.

Sounds good. Again - not essential, but probably prudent.

Here are the answers to the question you had asked. Does that Change any of
your thoughts on my plight

Q.Is the SKU number in fact unique to a store? or is it properly an
attribute of the Product - i.e. does every store selling Widgets use a
different SKU for its widget?
A.The SKU number is assigned by a store chain and each item has a unique SKU
number throughout the chain, in my case, 1800 stores.
We also sell to other retailers and they all have their own SKU numbers.
Our part number or the UPC number never changes.

Ok... so it sounds like the SKU field should not be in the Parts
table, since the same part will have a different SKU depending on
who's selling it.

Q.MarketNumber CANNOT be the Primary Key; doing so would limit you to
one and only one product per store, because the Primary Key is by
definition unique in the table. Select both fields and make them a
joint two-field primary key.
A.I see. So basically each item in the "Active" list would be identified by
a combination key of market and SKU.

Exactly. Note that you don't want to create a redundant concatenated
field; Access can accept up to ten fields as a joint Primary Key.

Q.If a given product PartNumber can come in multiple Sizes, each with
its own Price, you may need to split this into two related tables.
A. Each size has its own part number, UPC number, and SKU number

Good. Keeps it simple.

Good luck, and don't hesitate to post back if you have further
questions!

John W. Vinson[MVP]