Help with what may be a basic question...

[Melvis]

Hi,

I am designing a database to track interactions with customers. In the main
table, I have TrackingID as the primary key. In order to keep track of
multiple interactions with the same customer, I have a RelatedTrackingID
field. This field is then linked back to the primary key TrackingID.

I may be having a brain cramp, but I can't figure out a way for users to be
able to enter the related tracking ID. We do not have a customer table
because we deal with a third party to affect the interaction with the
customer. All we store about the customer is first and last name.

We do not have a table for the "go-betweens" either. What needs to happen is
the employees who use the database need to be able to see if the same
customer name came in before to track it. They are not necessarily told "This
is the second attempt to do this" but they need to be able to know that it
was tried before.

Sorry if I am not explaining this well. If there is any more information I
can give to help you help me let me know. I will be back in the office
tomorrow.

Any help is greatly appreciated...

~MATT

 

[Allen Browne]

You have a main form bound to a table that has TrackingID as primary key.
It has a subform bound to the RelatedTracking table, which has fields:
- RelatedTrackingID primary key (autonumber), and
- TrackingID foreign key to the main Tracking table.
You want to put an unbound text box on the main form, where the user can
enter the RelatedTrackingID number, and it will display the correct record.

If that's the idea, the AfterUpdate event procedure of this text box will:
1) look up the TrackingID for this record;
2) display that record in the main form;
3) move focus to the correct record in the subform.
That's because the desired record won't be in the subform until you find the
correct record in the main form.

Aircode:

Private Sub txtFindRelatedTrackingID_AfterUpdate()
Dim frm As Form
Dim strWhere As String
Dim strWhereSub As String
Dim varTrackingID As String

If Not IsNull(Me.txtFindRelatedTrackingID) Then
If Me.Dirty Then 'save before move.
Me.Dirty = False
End If

'1. Look up the TrackingID.
strWhereSub = "RelatedTrackingID = " & Me.txtFindRelatedTrackingID
varTrackingID = DLookup("TrackingID", "Tracking", strWhereSub)
If IsNull(varTrackingID) Then
MsgBox "Not found."
Else

'2. Display that record in the main form.
strWhere = "TrackingID = " & varTrackingID
With Me.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
If Me.FilterOn Then
Me.FilterOn = False
End If
End If
If .NoMatch Then
MsgBox "Not found in main form."
Else
Me.Bookmark = .Bookmark

'3. Find the subform record.
Set frm = Me.[NameOfYourSubformControlHere].Form
With frm.RecordsetClone
.FindFirst strWhereSub
If .NoMatch Then
MsgBox "Not found in subform"
Else
frm.Bookmark = .Bookmark
End If
End With
Set frm = Nothing
End If
End With
End If
End If
End Sub

 

[Melvis]

Allen,

Thanks so much for the time on this, especially considering the lack of
clarity in the question I posed. Your solution has several items that will
help, but let me explain further what the database is supposed to do...

First, the tables:
AppsUnderReview
TrackingID (PK)
MiddleMan
EmployeeID (FK -> references employee table)
Date
TitleNum
CustLName
CustFname
Comments
RelatedTrackingID (FK -> references TrackingID in this table)

AppReviewReasons
ReviewReasonID (PK)
TrackingID (FK -> references AppsUnderReview table)
ReasonID (FK -> references Reasons table)
Notes

Reasons
ReasonID (PK)
ReasonCategory
SpecificReason
Notes

Employees
EmployeeID (PK)
EmpLName
EmpFName
Notes

What this does is track when the "MiddleMan" comes in to have something done
for a given customer with a TitleNum. We need to be able to track if the same
MiddleMan comes in for the same customer and TitleNum. I already have
searches that will allow the employees to look up the TitleNum and customer
name and even a report of all MiddleMan activity between selected dates.

What I feel like I am missing is some relatively simple way to link one
record in the AppsUnderReview table back to an older record in the same table
with matching customer and title information in order to track repetitions.
This will be used to make sure the MiddleMan is not bringing us the same
information every week for the same customer and getting it rejected every
week. We need some way to show a kind of "paper trail" without the paper.

Is there any way to do this? Am I explaining enough of what is required of
the database? Please let me know if there is any more information that would
help you understand my problem...

~MATT

You have a main form bound to a table that has TrackingID as primary key.
It has a subform bound to the RelatedTracking table, which has fields:
- RelatedTrackingID primary key (autonumber), and
- TrackingID foreign key to the main Tracking table.
You want to put an unbound text box on the main form, where the user can
enter the RelatedTrackingID number, and it will display the correct record.

If that's the idea, the AfterUpdate event procedure of this text box will:
1) look up the TrackingID for this record;
2) display that record in the main form;
3) move focus to the correct record in the subform.
That's because the desired record won't be in the subform until you find the
correct record in the main form.

Aircode:

Private Sub txtFindRelatedTrackingID_AfterUpdate()
Dim frm As Form
Dim strWhere As String
Dim strWhereSub As String
Dim varTrackingID As String

If Not IsNull(Me.txtFindRelatedTrackingID) Then
If Me.Dirty Then 'save before move.
Me.Dirty = False
End If

'1. Look up the TrackingID.
strWhereSub = "RelatedTrackingID = " & Me.txtFindRelatedTrackingID
varTrackingID = DLookup("TrackingID", "Tracking", strWhereSub)
If IsNull(varTrackingID) Then
MsgBox "Not found."
Else

'2. Display that record in the main form.
strWhere = "TrackingID = " & varTrackingID
With Me.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
If Me.FilterOn Then
Me.FilterOn = False
End If
End If
If .NoMatch Then
MsgBox "Not found in main form."
Else
Me.Bookmark = .Bookmark

'3. Find the subform record.
Set frm = Me.[NameOfYourSubformControlHere].Form
With frm.RecordsetClone
.FindFirst strWhereSub
If .NoMatch Then
MsgBox "Not found in subform"
Else
frm.Bookmark = .Bookmark
End If
End With
Set frm = Nothing
End If
End With
End If
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I am designing a database to track interactions with customers. In the
main
table, I have TrackingID as the primary key. In order to keep track of
multiple interactions with the same customer, I have a RelatedTrackingID
field. This field is then linked back to the primary key TrackingID.

I may be having a brain cramp, but I can't figure out a way for users to
be
able to enter the related tracking ID. We do not have a customer table
because we deal with a third party to affect the interaction with the
customer. All we store about the customer is first and last name.

We do not have a table for the "go-betweens" either. What needs to happen
is
the employees who use the database need to be able to see if the same
customer name came in before to track it. They are not necessarily told
"This
is the second attempt to do this" but they need to be able to know that it
was tried before.

Sorry if I am not explaining this well. If there is any more information I
can give to help you help me let me know. I will be back in the office
tomorrow.

Any help is greatly appreciated...

~MATT

 

[Allen Browne]

Okay the tables you have sound good, and it sounds like you need a table of:
- Middleman, so you can uniquely identify each middleman;
- TitleNum, so you don't have to repeat CustLName and CustFName;
- Issue, so you can track issues.

With those tables you could identify the issues raised by a MiddleMan for a
TitleNum, compare the issues, and then create reports or make decisions.

Without those tables, it seems to me that you would be groping in the dark.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks so much for the time on this, especially considering the lack of
clarity in the question I posed. Your solution has several items that will
help, but let me explain further what the database is supposed to do...

First, the tables:
AppsUnderReview
TrackingID (PK)
MiddleMan
EmployeeID (FK -> references employee table)
Date
TitleNum
CustLName
CustFname
Comments
RelatedTrackingID (FK -> references TrackingID in this table)

AppReviewReasons
ReviewReasonID (PK)
TrackingID (FK -> references AppsUnderReview table)
ReasonID (FK -> references Reasons table)
Notes

Reasons
ReasonID (PK)
ReasonCategory
SpecificReason
Notes

Employees
EmployeeID (PK)
EmpLName
EmpFName
Notes

What this does is track when the "MiddleMan" comes in to have something
done
for a given customer with a TitleNum. We need to be able to track if the
same
MiddleMan comes in for the same customer and TitleNum. I already have
searches that will allow the employees to look up the TitleNum and
customer
name and even a report of all MiddleMan activity between selected dates.

What I feel like I am missing is some relatively simple way to link one
record in the AppsUnderReview table back to an older record in the same
table
with matching customer and title information in order to track
repetitions.
This will be used to make sure the MiddleMan is not bringing us the same
information every week for the same customer and getting it rejected every
week. We need some way to show a kind of "paper trail" without the paper.

Is there any way to do this? Am I explaining enough of what is required of
the database? Please let me know if there is any more information that
would
help you understand my problem...

~MATT

You have a main form bound to a table that has TrackingID as primary key.
It has a subform bound to the RelatedTracking table, which has fields:
- RelatedTrackingID primary key (autonumber), and
- TrackingID foreign key to the main Tracking table.
You want to put an unbound text box on the main form, where the user can
enter the RelatedTrackingID number, and it will display the correct
record.

If that's the idea, the AfterUpdate event procedure of this text box
will:
1) look up the TrackingID for this record;
2) display that record in the main form;
3) move focus to the correct record in the subform.
That's because the desired record won't be in the subform until you find
the
correct record in the main form.

Aircode:

Private Sub txtFindRelatedTrackingID_AfterUpdate()
Dim frm As Form
Dim strWhere As String
Dim strWhereSub As String
Dim varTrackingID As String

If Not IsNull(Me.txtFindRelatedTrackingID) Then
If Me.Dirty Then 'save before move.
Me.Dirty = False
End If

'1. Look up the TrackingID.
strWhereSub = "RelatedTrackingID = " &
Me.txtFindRelatedTrackingID
varTrackingID = DLookup("TrackingID", "Tracking", strWhereSub)
If IsNull(varTrackingID) Then
MsgBox "Not found."
Else

'2. Display that record in the main form.
strWhere = "TrackingID = " & varTrackingID
With Me.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
If Me.FilterOn Then
Me.FilterOn = False
End If
End If
If .NoMatch Then
MsgBox "Not found in main form."
Else
Me.Bookmark = .Bookmark

'3. Find the subform record.
Set frm = Me.[NameOfYourSubformControlHere].Form
With frm.RecordsetClone
.FindFirst strWhereSub
If .NoMatch Then
MsgBox "Not found in subform"
Else
frm.Bookmark = .Bookmark
End If
End With
Set frm = Nothing
End If
End With
End If
End If
End Sub

Hi,

I am designing a database to track interactions with customers. In the
main
table, I have TrackingID as the primary key. In order to keep track of
multiple interactions with the same customer, I have a
RelatedTrackingID
field. This field is then linked back to the primary key TrackingID.

I may be having a brain cramp, but I can't figure out a way for users
to
be
able to enter the related tracking ID. We do not have a customer table
because we deal with a third party to affect the interaction with the
customer. All we store about the customer is first and last name.

We do not have a table for the "go-betweens" either. What needs to
happen
is
the employees who use the database need to be able to see if the same
customer name came in before to track it. They are not necessarily told
"This
is the second attempt to do this" but they need to be able to know that
it
was tried before.

Sorry if I am not explaining this well. If there is any more
information I
can give to help you help me let me know. I will be back in the office
tomorrow.

Any help is greatly appreciated...

~MATT

 

[Melvis]

Some clarification:
- Middleman table sounds good - I have thought of it, but it's just a number
that refers to an office across the street, and I'm not sure how often or IF
this changes - might/might not (hate to be cryptic, but some of this is
semi-sensitive)
- TitleNum does not define the customer necessarily. Think of tranferring a
car title from one person to another - the title number stays the same, the
customer changes
- Issue - you lost me here. This sounds like what the Reasons table does.
When an application is rejected, they put in the reason(s) for rejecting it.
These are put into the AppReviewReasons table so as to form a many-to-many
relationship. The reasons for rejections are predefined and categorized in
the Reasons table. So I am a bit lost as to what you would have the Issues
table do.

Please let me know if I am following you here. I feel like I am very close
thanks to you. You guys are always great!

~MATT

Okay the tables you have sound good, and it sounds like you need a table of:
- Middleman, so you can uniquely identify each middleman;
- TitleNum, so you don't have to repeat CustLName and CustFName;
- Issue, so you can track issues.

With those tables you could identify the issues raised by a MiddleMan for a
TitleNum, compare the issues, and then create reports or make decisions.

Without those tables, it seems to me that you would be groping in the dark.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks so much for the time on this, especially considering the lack of
clarity in the question I posed. Your solution has several items that will
help, but let me explain further what the database is supposed to do...

First, the tables:
AppsUnderReview
TrackingID (PK)
MiddleMan
EmployeeID (FK -> references employee table)
Date
TitleNum
CustLName
CustFname
Comments
RelatedTrackingID (FK -> references TrackingID in this table)

AppReviewReasons
ReviewReasonID (PK)
TrackingID (FK -> references AppsUnderReview table)
ReasonID (FK -> references Reasons table)
Notes

Reasons
ReasonID (PK)
ReasonCategory
SpecificReason
Notes

Employees
EmployeeID (PK)
EmpLName
EmpFName
Notes

What this does is track when the "MiddleMan" comes in to have something
done
for a given customer with a TitleNum. We need to be able to track if the
same
MiddleMan comes in for the same customer and TitleNum. I already have
searches that will allow the employees to look up the TitleNum and
customer
name and even a report of all MiddleMan activity between selected dates.

What I feel like I am missing is some relatively simple way to link one
record in the AppsUnderReview table back to an older record in the same
table
with matching customer and title information in order to track
repetitions.
This will be used to make sure the MiddleMan is not bringing us the same
information every week for the same customer and getting it rejected every
week. We need some way to show a kind of "paper trail" without the paper.

Is there any way to do this? Am I explaining enough of what is required of
the database? Please let me know if there is any more information that
would
help you understand my problem...

~MATT

You have a main form bound to a table that has TrackingID as primary key.
It has a subform bound to the RelatedTracking table, which has fields:
- RelatedTrackingID primary key (autonumber), and
- TrackingID foreign key to the main Tracking table.
You want to put an unbound text box on the main form, where the user can
enter the RelatedTrackingID number, and it will display the correct
record.

If that's the idea, the AfterUpdate event procedure of this text box
will:
1) look up the TrackingID for this record;
2) display that record in the main form;
3) move focus to the correct record in the subform.
That's because the desired record won't be in the subform until you find
the
correct record in the main form.

Aircode:

Private Sub txtFindRelatedTrackingID_AfterUpdate()
Dim frm As Form
Dim strWhere As String
Dim strWhereSub As String
Dim varTrackingID As String

If Not IsNull(Me.txtFindRelatedTrackingID) Then
If Me.Dirty Then 'save before move.
Me.Dirty = False
End If

'1. Look up the TrackingID.
strWhereSub = "RelatedTrackingID = " &
Me.txtFindRelatedTrackingID
varTrackingID = DLookup("TrackingID", "Tracking", strWhereSub)
If IsNull(varTrackingID) Then
MsgBox "Not found."
Else

'2. Display that record in the main form.
strWhere = "TrackingID = " & varTrackingID
With Me.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
If Me.FilterOn Then
Me.FilterOn = False
End If
End If
If .NoMatch Then
MsgBox "Not found in main form."
Else
Me.Bookmark = .Bookmark

'3. Find the subform record.
Set frm = Me.[NameOfYourSubformControlHere].Form
With frm.RecordsetClone
.FindFirst strWhereSub
If .NoMatch Then
MsgBox "Not found in subform"
Else
frm.Bookmark = .Bookmark
End If
End With
Set frm = Nothing
End If
End With
End If
End If
End Sub

Hi,

I am designing a database to track interactions with customers. In the
main
table, I have TrackingID as the primary key. In order to keep track of
multiple interactions with the same customer, I have a
RelatedTrackingID
field. This field is then linked back to the primary key TrackingID.

I may be having a brain cramp, but I can't figure out a way for users
to
be
able to enter the related tracking ID. We do not have a customer table
because we deal with a third party to affect the interaction with the
customer. All we store about the customer is first and last name.

We do not have a table for the "go-betweens" either. What needs to
happen
is
the employees who use the database need to be able to see if the same
customer name came in before to track it. They are not necessarily told
"This
is the second attempt to do this" but they need to be able to know that
it
was tried before.

Sorry if I am not explaining this well. If there is any more
information I
can give to help you help me let me know. I will be back in the office
tomorrow.

Any help is greatly appreciated...

~MATT

 

[Allen Browne]

Okay: so TitleNum identifies *what* you are tracking, but over time there
may be different issues associated with the same TitleNum. For exmaple, a
TitleNum may transfer from A to B, and later from B to C, and those are
different issues. I am not clear about the rejections, e.g. if you refuse to
all a transfer from A to D, so B sells to C, and now D tries to buy from C,
is this the same issue that has previously been rejected, or is this a
completely different issue that might be okay now?

I may not have understood your scenarios at all, but it seems to me that you
are saying that a TitleNum issue may be presented to you multiple times, and
that is what you are trying to identify/avoid. If so, then it make sense to
somehow identify the issues associated with a TitleNum, so that you are tell
if you are getting a repeat of the same issue or a new issue.

Clear as mud?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Some clarification:
- Middleman table sounds good - I have thought of it, but it's just a
number
that refers to an office across the street, and I'm not sure how often or
IF
this changes - might/might not (hate to be cryptic, but some of this is
semi-sensitive)
- TitleNum does not define the customer necessarily. Think of tranferring
a
car title from one person to another - the title number stays the same,
the
customer changes
- Issue - you lost me here. This sounds like what the Reasons table does.
When an application is rejected, they put in the reason(s) for rejecting
it.
These are put into the AppReviewReasons table so as to form a many-to-many
relationship. The reasons for rejections are predefined and categorized in
the Reasons table. So I am a bit lost as to what you would have the Issues
table do.

Please let me know if I am following you here. I feel like I am very close
thanks to you. You guys are always great!

~MATT

Okay the tables you have sound good, and it sounds like you need a table
of:
- Middleman, so you can uniquely identify each middleman;
- TitleNum, so you don't have to repeat CustLName and CustFName;
- Issue, so you can track issues.

With those tables you could identify the issues raised by a MiddleMan for
a
TitleNum, compare the issues, and then create reports or make decisions.

Without those tables, it seems to me that you would be groping in the
dark.

Allen,

Thanks so much for the time on this, especially considering the lack of
clarity in the question I posed. Your solution has several items that
will
help, but let me explain further what the database is supposed to do...

First, the tables:
AppsUnderReview
TrackingID (PK)
MiddleMan
EmployeeID (FK -> references employee table)
Date
TitleNum
CustLName
CustFname
Comments
RelatedTrackingID (FK -> references TrackingID in this table)

AppReviewReasons
ReviewReasonID (PK)
TrackingID (FK -> references AppsUnderReview table)
ReasonID (FK -> references Reasons table)
Notes

Reasons
ReasonID (PK)
ReasonCategory
SpecificReason
Notes

Employees
EmployeeID (PK)
EmpLName
EmpFName
Notes

What this does is track when the "MiddleMan" comes in to have something
done
for a given customer with a TitleNum. We need to be able to track if
the
same
MiddleMan comes in for the same customer and TitleNum. I already have
searches that will allow the employees to look up the TitleNum and
customer
name and even a report of all MiddleMan activity between selected
dates.

What I feel like I am missing is some relatively simple way to link one
record in the AppsUnderReview table back to an older record in the same
table
with matching customer and title information in order to track
repetitions.
This will be used to make sure the MiddleMan is not bringing us the
same
information every week for the same customer and getting it rejected
every
week. We need some way to show a kind of "paper trail" without the
paper.

Is there any way to do this? Am I explaining enough of what is required
of
the database? Please let me know if there is any more information that
would
help you understand my problem...

~MATT

:

You have a main form bound to a table that has TrackingID as primary
key.
It has a subform bound to the RelatedTracking table, which has fields:
- RelatedTrackingID primary key (autonumber), and
- TrackingID foreign key to the main Tracking table.
You want to put an unbound text box on the main form, where the user
can
enter the RelatedTrackingID number, and it will display the correct
record.

If that's the idea, the AfterUpdate event procedure of this text box
will:
1) look up the TrackingID for this record;
2) display that record in the main form;
3) move focus to the correct record in the subform.
That's because the desired record won't be in the subform until you
find
the
correct record in the main form.

Aircode:

Private Sub txtFindRelatedTrackingID_AfterUpdate()
Dim frm As Form
Dim strWhere As String
Dim strWhereSub As String
Dim varTrackingID As String

If Not IsNull(Me.txtFindRelatedTrackingID) Then
If Me.Dirty Then 'save before move.
Me.Dirty = False
End If

'1. Look up the TrackingID.
strWhereSub = "RelatedTrackingID = " &
Me.txtFindRelatedTrackingID
varTrackingID = DLookup("TrackingID", "Tracking", strWhereSub)
If IsNull(varTrackingID) Then
MsgBox "Not found."
Else

'2. Display that record in the main form.
strWhere = "TrackingID = " & varTrackingID
With Me.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
If Me.FilterOn Then
Me.FilterOn = False
End If
End If
If .NoMatch Then
MsgBox "Not found in main form."
Else
Me.Bookmark = .Bookmark

'3. Find the subform record.
Set frm = Me.[NameOfYourSubformControlHere].Form
With frm.RecordsetClone
.FindFirst strWhereSub
If .NoMatch Then
MsgBox "Not found in subform"
Else
frm.Bookmark = .Bookmark
End If
End With
Set frm = Nothing
End If
End With
End If
End If
End Sub

Hi,

I am designing a database to track interactions with customers. In
the
main
table, I have TrackingID as the primary key. In order to keep track
of
multiple interactions with the same customer, I have a
RelatedTrackingID
field. This field is then linked back to the primary key TrackingID.

I may be having a brain cramp, but I can't figure out a way for
users
to
be
able to enter the related tracking ID. We do not have a customer
table
because we deal with a third party to affect the interaction with
the
customer. All we store about the customer is first and last name.

We do not have a table for the "go-betweens" either. What needs to
happen
is
the employees who use the database need to be able to see if the
same
customer name came in before to track it. They are not necessarily
told
"This
is the second attempt to do this" but they need to be able to know
that
it
was tried before.

Sorry if I am not explaining this well. If there is any more
information I
can give to help you help me let me know. I will be back in the
office
tomorrow.

Any help is greatly appreciated...

~MATT