Adding Multiple Dates to One Record (No Duplicates)

[TomP]

I have a table built with names, control no. and notes. I do not want to
duplicate the names, control no. and notes in that table. I have another
table with 2 fields that I would like to allow duplicates (follow-up &
outcome) and link that data to the other table using a sub-form. Overall, I
would like to be able to retrieve one name in the database showing a history
of follow-up dates and outcome in the sub-form and not sure how to make that
work.

Thank you for your help!

 

[Jeff Boyce]

Tom

Your description sounds vaguely "spreadsheetly". If you want to get the
best use of Access' relationally-oriented features and functions, you need
to feed it well-normalized data.

Step back from "how" for a moment and describe your underlying table
structure in a bit more specific detail. For example, I might describe a
student enrollment database structure like (oversimplified, I know!):

tblStudent
StudentID
FirstName
LastName
DOB

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
StudentID
ClassID
EnrollmentDate

This design allows for one student to enroll in multiple classes, and one
class to have multiple students enrolled.

Now, try describing your SITUATION (not your current table structure) along
similar lines...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[TomP]

Below is my table structure. Any time a call is made or received, the user
would call up the name in the datase. If it is not in the dbase, then a new
record will be created. The follow-up field is used to keep a track of phone
calls made (which can be many) and outcome field will look for possible
action taken for that date. I hope this makes sense.

Tble1
Initial Call date
firstname
lastname
control no.
notes

Tble2
follow-up (date field)
outcome (combo field) - looks up data from tbl3

Tble3
Outcome (list of possible actions)

 

[Jeff Boyce]

I see nothing in the second table that connects a person from the first
table to the outcome (from the third).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[TomP]

I understand now... I added control no. to tbl2. Is there a way to pass the
control no. value to tble2 and not be visible? Tble3 is just one field with
a list of possible action. The outcome field in tble2 uses the list from
tble3. Thank you

Tble1
Initial Call date
firstname
lastname
control no.
notes

Tble2
control no.
follow-up (date field)
outcome (combo field) - looks up data from tbl3

Tble3
Outcome (list of possible actions - dropdown menu for the tbl 2 outcome field)

 

[Steve]

Hi Tom,

Consider revising your tables to the following:
TblClient
ClientID
FirstName
LastName
Control no.

TblClientNote
ClientNoteID
ClientID
NoteDate
Note

TblCallToClient
CallToClientID
ClientID
CallToClientDate
CallSubject

TblOutCome
OutcomeID
Outcome (list of possible actions)

TblCallToClientFollowUp
CallToClientFollowupID
CallToClientID
CallToClientFollowupDate

TblCallToClientFollowUpOutcome
CallToClientFollowUpOutcomeID
CallToClientFollowupID
OutcomeID
OutcomeDate

With the above tables, you can do the following:
1. Record multiple notes about a client in TblClientNote including
recording the date you made each note
2. Record each call you make to a client in TblCallToClient including the
date of each call and the subject of each call
3. Record each followup call to each call you record in #2 in
TblCallToClientFollowUp including the date of the followup call
4. Record one or more outcomes to each followup call you record in #3 in
blCallToClientFollowUpOutcome including the date of the outcome

To set this all up ........
1. Create a form/subform and base the main form on TblClient and base the
subform on TblClientNote. This will give you a list of notes for each
client along with the date for each note
2. Create a form/subform and base the main form on
TblCallToClientFollowUp and base the subform on
TblCallToClientFollowUpOutcome. This will give you a list of outcomes for
each followup.
3. Create a form/subform and base the main form on TblCallToClient and
make the main form in #2 the subform. This will give you a list of followups
for each call you record in TblCallToClient
4. Make the main form in #3 a subform of the main form in #1. This will
give you a list of calls you make to a client. You will now have two
subforms for the main form in #1:
(a) one to record notes about a client
(b) one to record calls made to a client

Steve

 

[Jeff Boyce]

Tom

I'll assume you are asking about setting up forms to do this, since working
directly in the tables would both be more difficult and would offer less
control (not to mention being a generally bad idea)...

Create a form that displays records from your first table.

Create a form that display records from your second table.

Embed the second form (as a sub-form) in your first form (do this in design
view). Tell Access how to know which records from the second table "belong"
to the main form (the first one) ... it looks like [ControlNo] is the common
link.

Regards

Jeff Boyce
Microsoft Office/Access MVP