Creating a form and report to track communication

Question

Hello,

I am in the preliminary stages of building my database. In the database, I will need to track participant communication (e.g., phone calls and emails to participants)-- so there would be multiple records for each ID. I was wondering if anyone knows what the best approach would be to handle this? 

So for instance, if I communicate with a participant I would enter in all the info (e.g. date/ time, outcome of communication--e.g., did not pick up, etc) and then I would have to do this every time I communicated with them. Does anyone know the best way to handle this? Also, I would need to generate a report as well to see who I need to contact. For instance, if 3 days have passed, I would need to generate a report to see who has not picked up and it's been 3 days. Is this possible to do? It would also be nice if the report could show the history as well, for instance if it's been 9 days, I could look at the report to see all of the interaction up until that point.

Does anyone have any suggestions/ or templates on how to handle this?

Thanks in advance!

Nicole

Answer 1

What you have in relational database terms is a ternary (3-way) relationship type between Participants, ComunicationTypes and Outcomes.  This many-to-many relationship type is modelled by a table which resolves it into three one-to-many relationship types.

So, in broad outline the model would comprise tables like this:

Participants
....ParticipantID (PK)
....FirstName
....LastName
....etc

CommunicationTypes
....CommunicationTypeID  (PK)
....CommunicationType

Outcomes
....OutcomeID  (PK)
....Outcome

And to model the relationship type between them:

Communications
....CommunicationID  (PK)
....ParticipantID (FK)
....CommunicationTypeID  (FK)
....OutcomeID  (FK)
....CommunicationDate
....Subject

CommunicationDate and Subject are attributes of the relationship type.  Any other attributes you wish to record can be represented by further columns in the table.

For data entry you would probably have a participants form, in single form view, and within it a subform, in continuous forms view, based on a query on the Communications table which orders the communications by date.  The subform would be linked to the parent form on ParticipantID, so would show only those communications with the participant currently selected in the parent form.  Adding a new communication would simple be a case of entering data into a new record in the subform.

For a report, first create a query which joins all of the tables on the keys.  Then group and order the report as appropriate, e.g. grouped by participant and ordered by CommunicationDate.  By default this will return all communications by all participants.  You can build a dialogue form from which the report is opened via a command button, with unbound controls in the form to filter the report by means of the WhereCondition argument of the OpenReport method.  This will enable you to open the report to show only those records which match the criteria which you've described.

---

Ken Sheridan, Stafford, England

Answer 2

Thanks for your speedy response, Ken!

Is there a reason to keep communications date in a separate table-- l was just wondering if it would make more sense to keep everything communication-related in one table (e.g., put put communication type under communications as well? Or is it better to keep them separate?

Also-- i seem to be getting the following error message when I go to enter data in my subform: The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data...

But how should I have it structured then if i want to track multiple records per participant ID?

Any help on this end would be great!

Nicole 

Answer 3

As I said in my initial reply, in relational database terms Communications is a many-to-many relationship type between Participants, CommunicationTypes and Outcomes.  Like any entity type a relationship type has attributes which are functionally determined by the table's key.  The type of communication is such an attribute, so belongs in the Communications table as a column.  In this case the column is a foreign key referencing the primary key of the CommunicationTypes table.

If you were to dispense with the CommunicationTypes table, and simply enter the type of communication into a text column in Communications, not only would this be more time-consuming for the user, it would undermine the integrity of the database as it would not allow the value entered to be constrained by the enforcement of referential integrity in the relationship between  and CommunicationTypes.  This would allow the same type of communication to be entered differently in different rows, as it would be entirely at the whim of the user.  I once came across an example of this in a database where my name had been entered in three different ways as author of technical papers in my own field of work due to the absence of an Authors table in which my name would appear once only.

As regards the key violation error, the primary keys of Participants, CommunicationTypes and Outcomes should be autonumber columns, but the corresponding foreign keys in Communications should be of straightforward long integer number data type, each indexed non-uniquely (duplicates allowed).

For an example of a ternary relationship type like this take a look at StudentCourses.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the StudentCourses table models the relationship type between Students, Courses, and Statuses.  The demo illustrates a number of different interfaces (in response to requests here and elsewhere over the years)  but the only sensible one is the conventional form/subform.  Note BTW how a new course name not currently represented in the database can be entered directly into the combo box in the subform, and transparently added as anew row to the Courses table by code in the combo box's NotInList event procedure.  You can do the same with the three two combo boxes you'll have in the subform to select a communication type and an outcome.  You'd also have text boxes for the communication date and subject.  You might want to set the DefaultValue of the former to Date() in the table design; this would insert the current date by default, though the user can change it if necessary.

---

Ken Sheridan, Stafford, England

Answer 4

Hi Ken,

Thanks again for your input-- appreciate it very much. 

How would I make the variables you mentioned in your previous response foreign keys? Would I join them in relationships to make them foreign keys?

Thanks,

Nicole

Answer 5

It is not necessary to define a column (field) in a referencing table as a foreign key in table design view.  When a relationship between two tables is created in the relationships window you simply drag from the primary key of one table to the column in the other table with the mouse.  The column in the referencing table becomes a foreign key by virtue of its participation in the relationship.  The relationship dialogue will open, and you should select 'Enforce Referential Integrity'.

Where the primary key is an autonumber, as is usually the case, there is no need to select 'Cascade Updates'.  Whether or not to select 'Cascade Deletes' is a matter of judgement.  If selected, when a row is deleted from the referenced table, all matching rows in the referencing table will be automatically deleted also.  This is often appropriate, but sometimes not.  If in doubt, don't select it.

The relationships window in my StudentCourses demo is as below:



Yours should look similar to this, with the table modelling the many-to-many relationship type, being related to each of the other three tables.  You'll notice that the relationship lines between the tables have an infinity symbol (like an 8 on its side) at the end where the line joins the table modelling the relationship type, StudentCourses above.  This indicates that the relationship type is one-to-many, with the infinity symbol at the 'many'  end (the referencing table in the relationship type).  It also means that referential integrity is enforced; if it were not, the infinity symbol would not show.  The many-to-many relationship type between the three tables has been resolved into three one-to-many relationship types.

---

Ken Sheridan, Stafford, England

Answer 6

Hi Ken,

Thanks for your response.

In order to track communication for each participant over time, rather than showing all of my information in a table, I would like it to show arrows that say add new record and then all of the information can be added then you can click on the arrow and add a new communication record for that participant and then you enter information into the corresponding fields like Data of communication, Attempt # etc. Is there a way to do this? I have an image of exactly what I want it to look like but can't seem to upload it here as it says my account is not verified.

Any help is greatly appreciated!

Thanks,

Nicole

Answer 7

More specifically, is there a way to get a sub-form to appear essentially the way it looks design view in form view? Sub-form seems to only appear in datasheet view when the main form is in form view.

Thanks!

Answer 8

More specifically, is there a way to get a sub-form to appear essentially the way it looks design view in form view? Sub-form seems to only appear in datasheet view when the main form is in form view.

A subform can be in single form view, continuous forms view, or datasheet view.  It's just a matter of setting the form's DefaultView property when you design the source form object.  The parent form must be in single form view, however.

If you want a form to be in continuous forms view, and to have a subform also in continuous forms view, whose recordset is reloaded as you navigate from row to row in the first form, so that it shows only those rows which reference the current row in the first form, then you can use correlated subforms within a parent form, which can be bound or unbound.  You'll find an example in CorrelatedSubs.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

This little demo file illustrates the use of both nested and correlated subforms, using Northwind data.  In the latter case two subforms are correlated by basing the second on a query which references the key of the first subform as a parameter.  The second subform is requeried in the Current event procedure of the first.

---

Ken Sheridan, Stafford, England

Answer 9

Great, thanks so much!

I also have another question for you that is a bit of a puzzle to try to figure out. I am in the process of recruiting schools. I have a list of information for the 2017/2018 school year (i.e., principal first name, last name, phone numbers, school name, school address etc). However, I will not have the most up to date information for the school district until October (the 2018/2019 file). The plan is to start calling the secretaries in advance of October to come up with each school's most recent principal and email address. However, all secretaries may not give us the email when we call which is why we need to rely on the 2018/2019 dataset which will have all the updated information for the school year. 

So where I am at right now is that I will start with the 2017/2018 version (load that one into Access) and then when September rolls around I will need to contact the secretaries and update this information in access (however, I am assuming I would need to create a separate set of variable names  compared to my variables that I have in the 2017/2018 data set. But then when October rolls around, and we need to contact those principals that we do not have the most up to date information for, we will have to upload the 2018/2019 information in. So I am wondering if there is a way to keep the variable names the same and only have the "year" variable change but still not overwrite the the information that I am updating when I am talking to the secretary. Then if we ever need to contract a principal down the road we will be directed to the most recent information but still have on file the 2017/2018 data. 

I am just wondering what the best approach would be to lay this all out-- is there a way that I can use multiple records to track each instance (i.e., 2017/2018, updates from secretary, 2018/2019 list)... is there a way that I could keep the same variable names or would I have to have different ones?

I really hope this makes sense and if you need any other information at all, please let me know as I am not sure how clear this is.

Thanks!

Nicole

Answer 10

Let's get the terminology right first.  A relational database is made up of tables, each of which models an entity type.  Tables have columns (fields) and rows (records).  Each column represents an attribute of the entity type which the table models, and each row represents an entity of the entity type.  Data is stored at column positions in the rows, and each datum is a legitimate value of the attribute represented by the column.

I assume that by variable you are referring to an attribute, i.e. a column.  This is a little confusing as the word variable is usually understood to mean something closer to its mathematical sense, a symbol to which a variable quantity is assigned.  In VBA and other languages a variable is first declared by name, following which it can then be assigned changing values.

AcademicYears constitute an entity type, of which AcademicYear is an attribute, and therefore a column in an AcademicYears table.  This will be referenced by other tables which represent entity types of which AcademicYear is an attribute.  Data for each academic year must not be stored as separate tables.  Nor must each academic year be represented by separate columns in a table.  Both would be encoding data as object names.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

Attributes of a school which will be unchanged from year to year, e.g. address would be represented by columns in a school table.  Where attributes per school can change over time, e.g. the principal's names, in database terms there is a many-to-many relationship type between schools and academic years.  A many-to-many relationship type is modelled by a table which resolves the relationship type into two or more one-to-many relationship types.  So in the case of principal names the relationship type would be modelled by a table like this:

SchoolPrincipals
….SchoolID  (FK)
….AcademicYear  (FK)
....PrincipalID  (FK)

The table would probably have other columns which represent attributes of the relationship type.  Its primary key is a composite one made up of the three foreign key columns.  The PrincipalID  column would reference the primary key of a Principals table which would have columns for the person's attributes as a principal.  It is likely that this table would be a sub-type of another table of people of other categories of people than principal.  You'll find an example of how to model a type hierarchy TypeHierarchy.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

Other attributes per school which can change over time would be modelled in the same way.  As an example the image below is the model for my StudentLog demo in the same OneDrive folder.  As you can see there are relationship types between AcademicYears, Students, Teachers and CourseNames, modelled by three tables StudentYears, TeacherYears and Courses.




With your example of school principals, a principal who remains in post for a number of years could either be represented by a separate row in SchoolPrincipals for each of those years, or by one row for the first year in post.  While the latter avoids the need to enter a new row each year, the former would make processing of the data simpler, and would be the approach I'd recommend.  In the above model this is done in the case of students and teachers.

---

Ken Sheridan, Stafford, England

Answer 11

Hi Ken,

I am at a point in my database where I would like to have a 2017/2018 ministry file (locked) a 2018/2019 ministry file (locked) and a 2018/2019 update file that I am able to edit. I have created a subform that has the 3 records per school (one for each year range) but I am wondering if there is a way where I can keep the 2017/2018 and the 2018/2019 ministry files locked but have the 2018/2018 update file able to edit. I was thinking of creating another subform beneath and only having that one editable but I am not sure how to do this because I currently have it set up that I have one table where all of my data goes (For each participant there is a Academic year variable as you mentioned which distinguishes one from the other) but I am not sure how to get it so that only 2018/2019 update file can be edited and the other ones remained locked. Any suggestions?

As an additional side note, I would like the 2018/2019 update file to contain the 2017/2018 information since we will only be updating those principal's names and email addresses if applicable when we call the secretary.

Thanks so much!!

Nicole

Answer 12

You do not have 'files' for each year.  A row in a table might be applicable to one academic year, or it might be applicable to multiple years.  In my StudentLog model, which I posted earlier, a row in students would be applicable to each academic year a student is registered in the StudentYears table.  A row in Courses, however, is applicable to only one academic year, as determined by the value at the AcademicYear column position in the row.

To restrict a form say to data which does not apply to the current academic year, you need to be able to determine if a data falls within the current academic year.  For this you can first add a function which returns the academic year for any date into a standard module:

Public Function GetAcademicYear(DateVal As Date, MonthStart As Integer, DayStart As Integer) As String

    Dim dtmYearStart As Date
    
    If MonthStart = 1 And DayStart = 1 Then
        ' academic year is calendar year, so return single year value
        GetAcademicYear = Year(DateVal)
    Else
        ' get start of academic year in year of date value
        dtmYearStart = DateSerial(Year(DateVal), MonthStart, DayStart)
        
        ' if date value is before start of academic year
        ' academic year starts year previous to date's year,
        ' otherwise it starts with date's year
        If DateVal < dtmYearStart Then
            GetAcademicYear = Year(DateVal) - 1 & "/" & Year(DateVal)
        Else
            GetAcademicYear = Year(DateVal) & "/" & Year(DateVal) + 1
        End If
    End If

End Function

Using the tables in my demo as an example, the following query would return only those students for the current academic year, where the academic year starts on 1st September:

SELECT Students.*, AcademicYear
FROM Students  INNER JOIN StudentYears
ON StudentYears.StudentID = Students.StudentID
WHERE AcademicYear = GetAcademicYear(DATE(),9,1);

To return those students registered in a specific year or years you can just use the literal value for the academic year, e.g.

SELECT Students.*, AcademicYear
FROM Students  INNER JOIN StudentYears
ON StudentYears.StudentID = Students.StudentID
WHERE AcademicYear = "2017/2018" OR AcademicYear = "2018/2019";

Note, however, that these would not actually work in my demo as I format the academic year differently.  I've amended the function's code and the above SQL statements to work with your format of 2017/2018 etc.

So, in your database you'd need to use queries which restrict the results like this whenever you want to work with data for the current, or any specific academic year.  This does not mean you need tom use a separate form or subform in each case; you'd amend the form or subform's RecordSource in code executed via the user interface to return data for a selected year.

To make only the current year's data editable you can set a form or subform's AllowEdits property to False in any year but the current year.  With a form based on the above query, for instance, you could put the following in the form's Current event procedure, again assuming the academic year starts on 1st September:

Me.AllowEdits = (Me.AcademicYear = GetAcademicYear(Date(),9,1))

You can do the same with the AllowDeletions property.

---

Ken Sheridan, Stafford, England