Design Query - Long question!

[Matt Lunn]

Hello all!

Help!!

I'm designing a database which among other things will track sales. The raw
sales data is to be extracted from a data warehouse every week and imported
into Access. For example, I'll retreive - AccountNumber, SalesPersonName and
SalesVolume every week.

The data warehouse only holds the current relationship between AccountNumber
and SalesPersonName. So if accounts move from one salesperson to another, so
do all of their historic sales. I'd like to be able to keep track of which
sales each salesperson made in each specific week, rather than which sales
the accounts they look after in the current week made.

The only options I can think of is to create a table showing AccountNumber
and SalesPersonName for each week or create one table with AccountNumber,
SalesPersonName and Week. I've calculated that the second table would have
over a million rows by end of year (22,000 accounts x 52 weeks).

What would be the best way to implement.

Thanks all!

Matt

 

[Jeff Boyce]

Matt

I vote for "door 2". And you can make it even tighter.

Access is a relational database -- use that to help yourself.

The history table you are describing needs three fields (plus a key?) at a
minimum.

First, keep two additional tables (this is where the "relational" comes in),
one with SalesReps (SalesRepID, SalesRepLastName, SalesRepFirstName), and
the other with Accounts (AcctNumber, AcctName).

Then your history table contains:

trelAccountHistory
AccountHistoryID
AccountNumber
SalesRepID
AccountHistoryDate

Note that you'd be storing the date value, rather than a "week" value.
Here's why ... the first week in January OF EVERY YEAR will be Week1. If
you want to be able to use this over time, you need the actual date. And if
you want week-of-year, you can use a query and the DatePart() function.

A million rows with those 3-4 fields (deep, but narrow), if indexed (you
could set relationships, this would index the join fields), should be quite
fast.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[David F. Cox]

I may be missing the point, but to me it looks to me that you need another
table:


account number
person minding sale
date started minding
date finished minding

David F. Cox

 

[Matt Lunn]

Jeff,

Thanks for the advice. Instinct told me that the second option would be best
but I thought that a million rows would slow things down. I'll design the
table as you suggest.

If it's OK could you please expand on what you mean by this?

"you could set relationships, this would index the join fields"

Thanks again,

Matt

 

[Matt Lunn]

Hi David,

Was wondering how best to implement this. i.e either one tables for each
week or one table holding data for all weeks (Was concerned that table would
be too big)

Thanks,
Matt

 

[Jim]

You really should look at the database Microsoft ships with installations of
MS Access for free called Northwinds.mdb (sometimes Nwinds.mdb).

It is a great example of a fully functioning sales database from start to
finish and ALL the relationships are completed and normalized to Boyce-Codd
third normal from.

I hope that helps.

 

[Jeff Boyce]

Matt

You could either use the relationships window to relate (primary key/foreign
key) the tables, or use the table designer mode to set indexes on the
tables. Read up on this, as it will affect performance.

Regards

Jeff Boyce
Microsoft Office/Access MPV