Database Design Question

[Guest]

I am trying to figure out the best way to design a database. I have the
following elements:
1.) Sales Representatives
2.) Dealers
3.) SKU #'s and the number of units sold by each rep through each dealer
4.) Monthly results
5.) yearly summary page
The following information should be found in the database:
1.) 2004 Information by Rep by Dealer by SKU
2.) 2005 Information by Rep by Dealer by SKU
This information is updated monthly and will be kept going forward. What
would be the best way to set this database up?
I was thinking of making a table for each rep for each month/year however
there are 21 Representatives, 12 months and 2 years of data so far. Setting
up queries and reports is stumping me as well. Any and all assistance you
can provide will help me out so much. Doing this in excel right now is so
time consuming.
Thanks so much in advance.
Regards,
Dawn

 

[John Vinson]

I am trying to figure out the best way to design a database. I have the
following elements:
1.) Sales Representatives
2.) Dealers
3.) SKU #'s and the number of units sold by each rep through each dealer

ok... these are all fine...

4.) Monthly results
5.) yearly summary page

.... but these should NOT be stored in any table; they should be
calculated on the fly.

The following information should be found in the database:
1.) 2004 Information by Rep by Dealer by SKU
2.) 2005 Information by Rep by Dealer by SKU

Very straightforward, given proper table design.

This information is updated monthly and will be kept going forward. What
would be the best way to set this database up?
I was thinking of making a table for each rep for each month/year however
there are 21 Representatives, 12 months and 2 years of data so far. Setting
up queries and reports is stumping me as well. Any and all assistance you
can provide will help me out so much. Doing this in excel right now is so
time consuming.

Excel is a spreadsheet, an excellent one. Access is a relational
database. THEY ARE DIFFERENT and require different design and a
different mindset! For one thing, in Excel it's routine to have
calculated fields in a spreadsheet (that's its main power in fact). In
an Access Table, you should not store ANY calculated fields except in
very unusual circumstances.

You should instead have one Table for each type of "Entity" -
real-life person, thing, or event. A Sales Rep is an entity; so is a
Dealer; so is a Product (identified by a SKU); so is a Sale. Each
entity will have "attributes" - these will be stored in fields in the
table. For example, a Dealer will have (I presume) a name, an address,
a phone number, perhaps some other information that your application
needs to record about a dealer. I'm guessing that a Sale will have a
SalesRepID (who sold it), a DealerID (who bought it), a SKU (what was
sold), a SaleDate (when), a Quantity (how many were sold), and a
UnitPrice (for how much per unit). The total value of the sale would
NOT be stored anywhere, since it can be calculated on demand in a
Query.

By storing this data in this way you can "slice and dice" it in many
ways. Storing data (such as dates or representatives) in table names
or field names is *THE WRONG WAY TO GO* and will get you into no end
of trouble!


John W. Vinson[MVP]

 

[Guest]

HIRE SOMEONE ELSE

EXCEL IS YOUR BEST BET STILL, TRY THE MULTIPAGE

IF YOU USE ACCESS, YOU MUST DO ALL TABLES TWICE