Database Design Advice

[Bruce D.]

Hi All,

I am having problems with my database design. I have a total of 10 tables. 6
of the tables are named month 1,2,3,4,5,6. They all contain the same fields
with a primary key field of Sales Person ID. The other 4 tables are named
overall score,closing comments,survey,compliance. They all have a primary key
of Employee Name. My problem is trying to join all of these tables into a
query which will be the source for the form. The issue is that some of the
months will contain new sales people as the year progresses on. My query is
not linking with those. For instance, I have 7 people for months 5 and 6 with
only 5 people for months 1-4. My query is only showing the first 5 people.
Month 1 is linked to all the other tables as to include all records from
month 1 and only those records that are equal to the other tables. Also, in
my form some of the comment fields will not allow me to enter any data. My
quess is that it is not linking with a name record. I need to see month by
month the info in the form with the ability to add info to the other 4 tables
like comments,scores, etc.

Any ideas for this situation is greatly appreciated.

BD

 

[Jeff Boyce]

Bruce

Based on your description, you have a ... spreadsheet! You don't need (or
want) to use "repeating fields" or "repeating tables" in Access, which is a
relational database. You won't get the best use of Access features and
functions if you try to feed it 'sheet data.

Before you go any further, I urge you to look into the topic of
"normalization" if you want to keep using Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Vinson]

Hi All,

I am having problems with my database design. I have a total of 10 tables. 6
of the tables are named month 1,2,3,4,5,6. They all contain the same fields
with a primary key field of Sales Person ID.

This is EXTRREMELY bad design. Storing data in a tablename is most
unwise. Is table 1 January 2006? What data will it contain four months
from now? MUCH better would be one single table with all of the data
in these six (or twelve?) tables, with an additional Date/Time field
containing the first of the month. The SalespersonID should NOT be the
primary key (unless your salespeople have only one sale a month... and

The other 4 tables are named
overall score,closing comments,survey,compliance. They all have a primary key
of Employee Name.

This poses a couple more problems. Can you ABSOLUTELY guarantee that
you will never have two employees who happen to have the same name?
Names aren't unique, and can change. You really should have a table of
Employees with a unique EmployeeID.

Also, if you have the employee as a Primary Key, then you are saying
that the employee can have one and only one record in each of these
tables (that's what a primary key does). If that's the case, why not
just put a [ClosingComments] field in the Employee table? Or, if there
can be multiple [Survey] records, use a one to many relationship; the
Survey table should have its own Primary Key, and have the EmployeeID
as a foreign key.

If the Overall Score is a calculated value, then it should not be
stored in any table at all, but calculated on the fly in a Query
instead.

My problem is trying to join all of these tables into a
query which will be the source for the form.

Not a good idea. Normally you would use a Form based on the "one" side
table of a relationship, with one or more Subforms for the "many".

The issue is that some of the
months will contain new sales people as the year progresses on. My query is
not linking with those. For instance, I have 7 people for months 5 and 6 with
only 5 people for months 1-4. My query is only showing the first 5 people.
Month 1 is linked to all the other tables as to include all records from
month 1 and only those records that are equal to the other tables. Also, in
my form some of the comment fields will not allow me to enter any data. My
quess is that it is not linking with a name record. I need to see month by
month the info in the form with the ability to add info to the other 4 tables
like comments,scores, etc.

You need to STOP. You are designing a spreadsheet system, not a
properly normalized relational database! Read up on the database
design suggestions, especially from the Database Design 101 links on
Jeff's site:

Jeff Conrad's resources page:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html


John W. Vinson[MVP]