Tracking Table 2

[misuser2k7]

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.

 

[dhookom]

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

 

[Andrzejek]

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.

 

[misuser2k7]

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.

 

[dhookom]

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

 

[Andrzejek]

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.

 

[misuser2k7]

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.

 

[dhookom]

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