INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Tracking Table

Tracking Table

(OP)
I am building a database for our sales team. This team needs to track its customer interactions in one table (preferably). Sales team meets customers on a monthly, quarterly and annual basis depending on who the customer is.

Senior management wants to track these interactions. It needs to know (1) when the scheduled (monthly, quarterly and annual) meetings (dates) are; (2) when the team actually met the customer (dates – to ensure sales team meets as close to schedule date as possible); (3) what was discussed in these meetings (meeting notes); and (4) how many new orders were secured during these meetings.

Different members of the sales team interact with different divisions in the customer’s organization and interactions are to be grouped under the same organization i.e. by customer organization.

My main issue is creating tables for each customer organization for the sales team. Some customers are contacted 12 times a year (monthly basis) and others 4 times (quarterly) or once a year (annually). This pattern is also adopted when sales team meets different divisions of the same customer company, that is, some divisions are contacted monthly while others on quarterly or annual basis. I have to develop a database in Access which will help the sales team members enter data about these interactions with the customer.

If I create a table by each interaction (say monthly) my table has 48 columns (12 months multiplied by 4 items Sr. Mgmt. wants to see reports on) for just one division in a company! Doing this for 4 or 5 divisions with different interval means creating a chaos of a table!

Also the sales team would like to enter this data through only one form that displays all the fields for the 4 items and 12 months on the same form!

I have managed to convince the sales team to use a report I will create which will show them their past interactions by dates (clicking on the dates opens up a smaller report for displaying each individual interaction) but they are adamant about the data entry through a single form.

The Senior Management also insists on receiving reports by customer organization, that is, one report per customer organization regardless of what the size of the paper the report printed on is!

Is there a way to create a table which can store all the data without having to create a massive table? I don’t mind how big the report gets in terms of page size as it is the Sr. Mgmt.’s problem

I have tried to split the table into smaller tables for different divisions but have been unsuccessful in linking them (one-to-one) to allow for data entry through a single form.

Thank you.

RE: Tracking Table

I would never allow what someone wants to see drive table structures. Consider tables

tblSalesPeople (one record per sales person)
tblCustomers (one record per customer with a field that stores the number of months between contacts)
tblCustDivision (one record per customer per division)
tblContacts (one record per contact of Sales person with a customer division with notes and date)

You should be able to create a single form to enter this information.

Duane
Hook'D on Access
MS Access MVP

RE: Tracking Table

Do NOT do that:
>my table has 48 columns (12 months multiplied by 4 items Sr. Mgmt. wants to see reports on) for just one division in a company! Doing this for 4 or 5 divisions with different interval means creating a chaos of a table!

To expend a little on Duane's note:
Table: tblContacts
SomePK_Field
Customer
FK to PK from tblCustDivision
Scheduled_Date (1) when the scheduled (monthly, quarterly and annual) meetings (dates) are;
Actual_Date (2) when the team actually met the customer (dates – to ensure sales team meets as close to schedule date as possible);
Notes (3) what was discussed in these meetings (meeting notes); and
New_Order_Nos (4) how many new orders were secured during these meetings.


Read more on Fundamentals of Relational Database Design

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Tracking Table

(OP)

Thank you Andy and Duane.

I am considering using the denormalized "chaotic" table because I have experienced problems in the past with linking multiple tables in a query (which becomes the recordsource for the data entry form) where one-to-one relationship is not possible as (to use the above example) one sales team has multiple interactions with the customer organization's division.

I was thinking of using specific data entry forms to enter data into the table (which has unique identifiers for interactions and divisions interacted with) so that I can minimize the number of columns by recording individual interactions by unique interaction numbers, that is, having more rows instead of columns.

I have hit a hurdle with the CASE statement (used to open specific data entry form when two conditions are satisfied) for which I have posted a thread in the 'Access Forms' forum.

RE: Tracking Table

I think you missed our point that table structure is not determined by interface. I guess if you want to ignore our advice about normalization it's your issue and not ours.

Duane
Hook'D on Access
MS Access MVP

RE: Tracking Table

48 columns (12 months X 4 items) for just one division in a company! Times 4 or 5 divisions. And that is for just one year. Next year: repeat the same, change code to reflect new fields for 2017 (or a new table?). When Company or Divisions are added, change the structure of the table. Reporting on last 5 years Sr. Mgmt. wants to see reports on will be... well, interesting.

Is it a 'job security'? "denormalized "chaotic" for sure. Not to mention a nightmare to maintain.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Tracking Table

(OP)
Duane and Andy, Thank you for your inputs.

The solution I came up for the issue of have everything in one table from your warnings is as follows:

Since customer organizations, their divisions and the interactions with them are unique and thankfully finite, I decided to identify them as such in the customer interaction table.

CustOrgID (integer), CustOrgDivID (integer), InteractNumber (integer), ScheduledMeetDate (Date), ActualMeetDat (Date), MeetingNotes (Long Text), OrderNumbers (integer), OrderDollarValue (currency).

Data will be entered through a form (based on customer interaction table) which has three combo boxes for CustOrgID, CustOrgDivID, InteractNumber and text boxes for the remaining fields.

Customer organization will be selected from the first combo box which will make the second combo box display list of its division (using if-then-else statement).

Once division is selected, depending on the division, the list of interaction numbers will be displayed in the third combo box (there will be 12 numbers for monthly, 4 for quarterly and so on; using if-then-else statement).

ScheduledMeetDates will be decided based on the first time the customer was contacted, that is, the first scheduled meeting with customer will be 30 days or so from the first contact date and subsequent meetings will be based on the business rule of monthly, quarterly, annual meeting by division from the first scheduled meeting date.

The table will have a composite PK made of CustOrgID, CustOrgDivID, InteractNumber, thereby ensuring uniqueness of each meeting.

A summary report (showing all the dates all the divisions are to be contacted) will be displayed when user opens the data entry form so that users know which dates the customer division meetings are scheduled.
OR
The appropriate ScheduledMeetDate will be displayed on the data entry form once the user selects the interaction number.

I feel this solution will limit the number of columns and tables while addressing the issue at hand and eliminate the need for having a “chaotic” table.

When more customers or divisions are added to the organization’s list of customers and divisions, they will only need to be added to the tables which are the rowsources for the combo boxes.

I would like to thank you both for your constructive criticism of a very stupid mistake I was making by adding columns to the customer interaction table.

RE: Tracking Table

misuser2k7,
This sounds much better than your earlier efforts. I'm not sure about your 3 column primary key. It seems there would be duplicates over more than one year.

I generally add a single column primary key on an autonumber. I will also create a unique index on combinations of fields to ensure data integrity.

Duane
Hook'D on Access
MS Access MVP

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close