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?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top