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
|
|||
|
|||
Tables Question
I have currently have multiple tables with the following:
tbl2003Plans Salesperson 2003SalesPlan CommittedBus (filled in as the year progresses) ProjectedBus (filled in as the year progresses) tbl2004Plans Salesperson 2004SalesPlan CommittedBus (filled in as the year progresses) ProjectedBus (filled in as the year progresses) I know this is not good table design. I do not know how to best design the tables so that I do not have to create a table for each new year of business. I have a salesperson table with the names of the sales people. Thanks for any advice. |
#2
|
|||
|
|||
Just an idea, but what about:
tblPlans Year Salesperson SalesPlan CommittedBus (filled in as the year progresses) ProjectedBus (filled in as the year progresses) These records could later be sorted and filtered on the year field if desired. -----Original Message----- I have currently have multiple tables with the following: tbl2003Plans Salesperson 2003SalesPlan CommittedBus (filled in as the year progresses) ProjectedBus (filled in as the year progresses) tbl2004Plans Salesperson 2004SalesPlan CommittedBus (filled in as the year progresses) ProjectedBus (filled in as the year progresses) I know this is not good table design. I do not know how to best design the tables so that I do not have to create a table for each new year of business. I have a salesperson table with the names of the sales people. Thanks for any advice. . |
#3
|
|||
|
|||
They may have CommittedBus for 2004 and 2005. Not sure how to separate the
years. " wrote: Just an idea, but what about: tblPlans Year Salesperson SalesPlan CommittedBus (filled in as the year progresses) ProjectedBus (filled in as the year progresses) These records could later be sorted and filtered on the year field if desired. -----Original Message----- I have currently have multiple tables with the following: tbl2003Plans Salesperson 2003SalesPlan CommittedBus (filled in as the year progresses) ProjectedBus (filled in as the year progresses) tbl2004Plans Salesperson 2004SalesPlan CommittedBus (filled in as the year progresses) ProjectedBus (filled in as the year progresses) I know this is not good table design. I do not know how to best design the tables so that I do not have to create a table for each new year of business. I have a salesperson table with the names of the sales people. Thanks for any advice. . |
#4
|
|||
|
|||
Hi Karen,
Looks like some more info is needed about what you want the Plan table to record. My first look led me to think that for each year there would be a single record for each Salesperson with a SalesPlan (dollar target?) for that year and accumulating fields for the CommittedBus and ProjectedBus (dollar amounts?) for that year. Question: What do you envision as the field types and what are they meant to hold? Question: For the scenario you describe for CommittedBus across more than one year (generated in 2004 but not due until 2005?), how did you intend to record that?... A new record in tbl2005Plans holding just the CommittedBus amount accumulated so far for that year by that Salesperson? Question: Does future CommittedBus and ProjectedBus count against current plan or future plan? Would be delighted to try to help more; let's keep the dialog going. I'm beginning to think you might need related tables: One for Salespeople, one for Plans, one for CommittedBus and one for ProjectedBus, all containing a Salesperson ID number as the common link. This might be more than you want for openers unless you're comfortable with building queries, forms and reports with joined tables. A lot depends on how "sophisticated" you want this to be - I can assure you that "simple" is better. If I can't help you work out something solid in a couple more exchanges, then you might need to retreat a bit and dig more into basic Access design principles. Howard -----Original Message----- They may have CommittedBus for 2004 and 2005. Not sure how to separate the years. " wrote: Just an idea, but what about: tblPlans Year Salesperson SalesPlan CommittedBus (filled in as the year progresses) ProjectedBus (filled in as the year progresses) These records could later be sorted and filtered on the year field if desired. -----Original Message----- I have currently have multiple tables with the following: tbl2003Plans Salesperson 2003SalesPlan CommittedBus (filled in as the year progresses) ProjectedBus (filled in as the year progresses) tbl2004Plans Salesperson 2004SalesPlan CommittedBus (filled in as the year progresses) ProjectedBus (filled in as the year progresses) I know this is not good table design. I do not know how to best design the tables so that I do not have to create a table for each new year of business. I have a salesperson table with the names of the sales people. Thanks for any advice. . . |
#5
|
|||
|
|||
You need to design your tables and database... First put similar fields together.. then identify keys for these tables.. Determine the relationship between the tables and add foreign keys as needed to handle one to many relaionships .. or many-to-many relationships. I can see several tables: Salesman Personal info Earnings Earnings type (key) Real/Projected You can carry the type values in a third table. Follow the database normalization processes. Ron Erwin (206) 465-8484 ================================================== ================== Business Administration (Information Systems) Music Minor (Voice, Keyboard) ================================================== ================== On Thu, 30 Sep 2004, [Utf-8] Karen wrote: They may have CommittedBus for 2004 and 2005. Not sure how to separate the years. " wrote: Just an idea, but what about: tblPlans Year Salesperson SalesPlan CommittedBus (filled in as the year progresses) ProjectedBus (filled in as the year progresses) These records could later be sorted and filtered on the year field if desired. -----Original Message----- I have currently have multiple tables with the following: tbl2003Plans Salesperson 2003SalesPlan CommittedBus (filled in as the year progresses) ProjectedBus (filled in as the year progresses) tbl2004Plans Salesperson 2004SalesPlan CommittedBus (filled in as the year progresses) ProjectedBus (filled in as the year progresses) I know this is not good table design. I do not know how to best design the tables so that I do not have to create a table for each new year of business. I have a salesperson table with the names of the sales people. Thanks for any advice. . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Linking Tables | Martin | Setting Up & Running Reports | 4 | September 9th, 2004 03:17 PM |
Querying from multiple tables - newbie question | Simon Pleasants | General Discussion | 2 | August 26th, 2004 03:02 PM |
A newby question to create a query from 2 tables | Android | Database Design | 8 | June 21st, 2004 06:32 PM |
Join all records for two tables? ( Performance question) | Ivan | Running & Setting Up Queries | 4 | June 11th, 2004 04:06 AM |
Separate database for tables? | Holly Clifton | Database Design | 3 | May 18th, 2004 06:20 PM |