Tables Question

[Karen]

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.

 

[Guest]

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.

 

[Karen]

They may have CommittedBus for 2004 and 2005. Not sure how to separate the
years.

 

[Guest]

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

 

[Ron Erwin]

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)

====================================================================