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)

 

[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