Table relationships & auto population question

Tiosylanyl

New Member
Joined
Jul 31, 2006
Messages
48
Hello,

I'm building a user entry form to help some of our field reps to use as a tracking tool for their schedules. Currently, I have the following tables:

*: Designates primary key

CA Table
CA ID*
CA Name
MCA Name

Weekly Schedule Table
Schedule ID*
CA Name
MCA Name
Year
Week #
Date
In office (Y/N)
Location if outside office
Best way to reach
Persons contacting
Planned tasks

Client visit table
Visit ID*
CA Name
MCA Name
Year
Week #
Visit Date
client ID
Client Name
Purpose of visit

Client Call Table
Client Call ID*
CA Name
MCA Name
Year
Week #
Call Date
Client ID
Client Name
Purpose of Call

Here are the questions I have before I get too far:

What would be the best way to set up relationships between these tables or, conversely, would it be better to just set this up onto one table? What are the pros and cons to setting up separate tables with relationships over setting it up on one table?

Additionally, I intend on inserting a calendar so that, depending on the date selected, the date fields will auto populate. That much I know how to do - I would additionally like to have the "Year" and "Week #" fields update accordingly without relying on user entry in those fields - is there a way to set this up?
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,915
Office Version
  1. 365
Platform
  1. Windows
Why are you repeating CA Name and MCA Name in every table?

You should use the CA ID from the CA Table in the others as a foreign key.

Also why 2 different tables for Visit/Call.

They seem to exactly the same type of data, all you need to add really is a field indicating whether the contact was a call or a visit.

Also why seperate fields for Year, Week # and Date?

1 date field should suffice and if you need to extract the Year/Week # then that can be done easily in a query using an expression.

Finally it's always a good idea to avoid using field names like Date to avoid confusion with for example the Date function.
 

Tiosylanyl

New Member
Joined
Jul 31, 2006
Messages
48
Why are you repeating CA Name and MCA Name in every table?

You should use the CA ID from the CA Table in the others as a foreign key.

Also why 2 different tables for Visit/Call.

They seem to exactly the same type of data, all you need to add really is a field indicating whether the contact was a call or a visit.

Also why seperate fields for Year, Week # and Date?

1 date field should suffice and if you need to extract the Year/Week # then that can be done easily in a query using an expression.

Finally it's always a good idea to avoid using field names like Date to avoid confusion with for example the Date function.

I had started with the intention of setting up separate tables and setting up relationships between them using the CA Name - admittedly, I set up the tables before I understood how I wanted them to be linked.

As for the dates question, I am trying to set it up so that the users can filter an entire week's schedule on the user form.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,915
Office Version
  1. 365
Platform
  1. Windows
The easiest way to link the tables would be by CA ID.

As to allowing filtering, like I said you should be able to get the year/week using expressions in a query.
 

MyBoo

Board Regular
Joined
Aug 9, 2006
Messages
217

ADVERTISEMENT

You don't really provide enough information for others to "properly" address your questions regarding design and relationships.

For instance:

Each table has CA Name listed as a field, but none have this field listed as the Primary key. Therefore, 1. you're duplicating information and 2. you can't set up any one-to-many relationships between the tables.... as you have them defined.

A one-to-many relationship requires the primary key in the one table to be a foreign key in the many table.

Example: If you wanted to define a one-to-many with the CA table and the Weekly Schedule table (as is), you would need to create a field in the Weekly Schedule table to hold the CA ID from the CA table and define your relationship using these two fields in these two tables.

Your reps should be in a ONE table.
Your Schedule(s) should be in a MANY table.
Your Activities should be in a MANY table.
There should be a relationship defined between these tables.... so that you have many schedules assigned to each rep and each rep can perform mulitple activites per schedule.

You could combine Call and Visit tables... as was mentioned by Norie.... Create one field - Activity... identifies call or visit, then one Activity Date... etc. You can name them anything you want to.

Bottom line: Table design is tedious and boring to some.... but done right will save you tons of grief down the way.
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Norie and MyBoo have touched on some good points. Just a follow up to MyBoo's last comment :

Table design is tedious and boring to some.... but done right will save you tons of grief down the way.

I cannot stress this enough. A bad table / relationship design will cause more problems than it solves. The following webpage is easy reading and describes the 'normalisation' process that you should probably undertake:

http://datamodel.org/NormalizationRules.html

Also, before designing tables etc you should give some thoughts as to the expected outputs from this system (think of it in a report format, rather than in terms of tables). This will go some way to helping you decide what data you need to capture, and provided you follow the normalisation process, the table & relationship design won't be such a daunting task.

HTH, Andrew
 

Tiosylanyl

New Member
Joined
Jul 31, 2006
Messages
48
Thanks for the input..

I started from scratch and combined some of the items with similar criteria.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,986
Messages
5,545,347
Members
410,679
Latest member
rolandbianco
Top