design problem re linking communications to later orders

[Catherine Jo Morgan]

This is for a recreational tree climbing database. When a climb is arranged,
it often begins with one or more inquiries by phone or email. Sometimes it
takes several communications to answer questions, set dates, etc.

At some point an inquiry gets definite enough to become a JobProposal, with
a specific type of climb, dates and fee set, etc. Usually JobProposals
become Climbs when the inquirer signs a contract, pays the deposit or fee,
shows up and participates in the climb. So JobProposal can be a FK in the
Climbs table.

It's actually more complicated because sometimes the tree climbing
organization initiates a climb or course, and often groups and organizations
and families are involved. But my primary problem right now is the
relationship between a JobProposal and all those earlier communications.

Tracking the communications is doable. But by the time it's clear that an
inquirer is serious about a JobProposal, several earlier communications have
often gone back and forth. How to link those after the fact, to the
JobProposal? I guess the only real link is the name of the inquirer. Earlier
I thought the specific service(s) mentioned in the earlier inquiries could
be a link, but often the first inquiries are quite vague. "Could our family
come next month to do a climb?" No climb type mentioned.

Maybe these early, vague communications (and their answers) should be
ignored? Or for any JobProposal, be able to call up a list of all the
communications back and forth (with dates and subjects and notes) linked to
the person or persons involved? (Usually there's one main contact person,
but sometimes two people communicate independently about the same job.)

Sorry if my problem seems unclear. Maybe your questions will help me clarify
the problem and get unstuck. TIA

[Allen Browne]

Your Communcations table will have fields:
PersonID foreign key to the person table;
ProposalID foreign key to the proposal.

When a person contacts you, you will enter them into the Person table, and
then use their PersonID in the Communcations table. If the communication is
not yet part of a proposal, you will leave the ProposalID foreign key blank.
Later, when you do create a proposal, you want to go back and manually
modify the earlier communications by including the ProposalID.

If that's the idea, the important bit is providing an interface where the
user can easily find the communications from this person that are not tied
to any proposal. That means providing some kind of filter on the
communications form.

Perhaps you could include an unbound combo box named cboFindPerson at the
top of the form.
Set its After Update property to
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.
Between the "Private Sub..." and "End Sub lines enter:

Dim strWhere As String
If Me.Dirty Then 'Save before filter
Me.Dirty = False
End If
If IsNull(Me.cboFi ndPerson) Then
Me.FilterOn = False 'Noone chosen: show all.
Else
strWhere = "([PersonID] = " & Me.cboFindPerso n & _
") AND ([ProposalID] Is Null)"
Me.Filter = strWhere
Me.FilterOn = True
End If
Note: If PersonID is a Text type field, you need extra quotes:
strWhere = "([PersonID] = """ & Me.cboFindPerso n & _
""") AND ([ProposalID] Is Null)"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Catherine Jo Morgan" <cj******@hemc. net> wrote in message
news:W_******** ************@he mc.net...

This is for a recreational tree climbing database. When a climb is arranged, it often begins with one or more inquiries by phone or email. Sometimes it
takes several communications to answer questions, set dates, etc.

At some point an inquiry gets definite enough to become a JobProposal, with a specific type of climb, dates and fee set, etc. Usually JobProposals
become Climbs when the inquirer signs a contract, pays the deposit or fee,
shows up and participates in the climb. So JobProposal can be a FK in the
Climbs table.

It's actually more complicated because sometimes the tree climbing
organization initiates a climb or course, and often groups and organizations and families are involved. But my primary problem right now is the
relationship between a JobProposal and all those earlier communications.

Tracking the communications is doable. But by the time it's clear that an
inquirer is serious about a JobProposal, several earlier communications have often gone back and forth. How to link those after the fact, to the
JobProposal? I guess the only real link is the name of the inquirer. Earlier I thought the specific service(s) mentioned in the earlier inquiries could
be a link, but often the first inquiries are quite vague. "Could our family come next month to do a climb?" No climb type mentioned.

Maybe these early, vague communications (and their answers) should be
ignored? Or for any JobProposal, be able to call up a list of all the
communications back and forth (with dates and subjects and notes) linked to the person or persons involved? (Usually there's one main contact person,
but sometimes two people communicate independently about the same job.)

Sorry if my problem seems unclear. Maybe your questions will help me clarify the problem and get unstuck. TIA