If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Design Query - Long question!
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 |
#2
|
|||
|
|||
Design Query - Long question!
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 http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Matt Lunn" wrote in message ... 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 |
#3
|
|||
|
|||
Design Query - Long question!
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" wrote in message ... 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 |
#4
|
|||
|
|||
Design Query - Long question!
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 "Jeff Boyce" wrote: 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 http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Matt Lunn" wrote in message ... 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 |
#5
|
|||
|
|||
Design Query - Long question!
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 "David F. Cox" wrote: 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" wrote in message ... 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 |
#6
|
|||
|
|||
Design Query - Long question!
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. "Matt Lunn" wrote in message ... 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 |
#7
|
|||
|
|||
Design Query - Long question!
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 "Matt Lunn" wrote in message ... 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 "Jeff Boyce" wrote: 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 http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Matt Lunn" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
More than 3 conditional formats? | Ltat42a | General Discussion | 12 | January 6th, 2006 11:26 AM |
Format cells with a formula (7 conditions). | danindenver | General Discussion | 3 | January 2nd, 2006 02:40 PM |
Error when running Query, but not Report | Blaze | Running & Setting Up Queries | 29 | September 9th, 2005 05:40 AM |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |
Troubleshoot the SendObject method | Nikky_Pickles | General Discussion | 1 | August 30th, 2004 07:40 AM |