Finding Repeat Complaints

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The database I am currently working on incorporates complaints delivered to
me, complaints to the stores and complaints called in to our 1800 number. I
would like to be able to run a query or report to help me discover "problem
complainers" - callers who complain multiple times to try to get freebies.
Currently I enter my complaints to tblGuestComplaints using
frmGuestComplaints. This pulls info from my tblRestaurantInfo and
tblComplaintType in order to have the necessary information. The fields that
are filled in are first name, last name, address 1, address 2, City, State,
Zip, Phone, email and date of visit. How would I track all of the following -
Phone numbers, street addresses, email addresses and the first and last name
combination? (I have noticed some false names and other different information
for the same complaint and visit date).

Any Ideas would be terrific!

Jennifer
 
Your question is not clear.... what do you mean by track? The database
probably already contains these data, so it's already tracking the data. Are
you looking for a way to filter the data and show you specific
information -- such as the "repeat" callers? If yes, what will be a "unique
match" for the data? What do you want to see in a report?
 
I have over 1000 complaint calls already stored in the database. Since we
have particular locations that have 'repeat complainers', I need to locate
these repeats (without using their name or complaint number as a primary key
- we don't want to block them out of the database, only pinpoint potential
problems). Repeat callers always have 1 of the following things the same -
Full name, Full address, phone number or email address. These callers may use
a different name and the same address, phone and email many times or they may
use the same name and have things sent to multiple addresses; sometimes they
use a different name but the same phone number or the same email address or
both (we do not always get addresses in the complaints called in to the 1800
number - we only require a method of contacting them to be included so that
we can try to resolve the situation later). So in answer to your question -
any of the above can be used as a unique match. In a report I would want to
see something like a count (if one of the above appears more than two or
three times in the past year - I do have a date function as well) and then
the details of the different calls. I simply do not know how to query for
repeats and for the details of those records. Would something like a count
work with multiple fields? and if so, how would I combine them?

Thank you!

Jen
 
I'm not 1005 certain about what you want to show in the final outcome, but
let's start with how you can show counts of things and go from there.

Based on the fact that full name, full address, phone number or email
address could be the thing that has many entries, we could write a Union
query to give you a list of such items where they occur more than once in a
year period. For example:

SELECT "Full Name" AS WhatIsIt, FullNameField AS EntryValue,
Count([FullNameField]) AS HowMany
FROM TableName
WHERE DateField Between #1/1/2005# And #12/31/2005#
AND Count([FullNameField]) > 1
GROUP BY "Full Name", FullNameField
UNION ALL
SELECT "Full Address", FullAddressField,
Count([FullAddressField]) AS HowMany
FROM TableName
WHERE DateField Between #1/1/2005# And #12/31/2005#
AND Count([FullAddressField]) > 1
GROUP BY "Full Address", FullAddressField
UNION ALL
SELECT "Phone Number", PhoneNumberField,
Count([PhoneNumberField]) AS HowMany
FROM TableName
WHERE DateField Between #1/1/2005# And #12/31/2005#
AND Count([PhoneNumberField]) > 1
GROUP BY "Phone Number", PhoneNumberField
UNION ALL
SELECT "Email Address", EmailAddressField,
Count([EmailAddressField]) AS HowMany
FROM TableName
WHERE DateField Between #1/1/2005# And #12/31/2005#
AND Count([EmailAddressField]) > 1
GROUP BY "Email Address", EmailAddressField
ORDER BY WhatIsIt, HowMany, EntryValue;

From this query, you get a list of all the types of entries that are
duplicated at least once. You then could use this query to get all the
records that have a specific type of entry.
 
Thank you very much!

Jennifer

Ken Snell (MVP) said:
I'm not 1005 certain about what you want to show in the final outcome, but
let's start with how you can show counts of things and go from there.

Based on the fact that full name, full address, phone number or email
address could be the thing that has many entries, we could write a Union
query to give you a list of such items where they occur more than once in a
year period. For example:

SELECT "Full Name" AS WhatIsIt, FullNameField AS EntryValue,
Count([FullNameField]) AS HowMany
FROM TableName
WHERE DateField Between #1/1/2005# And #12/31/2005#
AND Count([FullNameField]) > 1
GROUP BY "Full Name", FullNameField
UNION ALL
SELECT "Full Address", FullAddressField,
Count([FullAddressField]) AS HowMany
FROM TableName
WHERE DateField Between #1/1/2005# And #12/31/2005#
AND Count([FullAddressField]) > 1
GROUP BY "Full Address", FullAddressField
UNION ALL
SELECT "Phone Number", PhoneNumberField,
Count([PhoneNumberField]) AS HowMany
FROM TableName
WHERE DateField Between #1/1/2005# And #12/31/2005#
AND Count([PhoneNumberField]) > 1
GROUP BY "Phone Number", PhoneNumberField
UNION ALL
SELECT "Email Address", EmailAddressField,
Count([EmailAddressField]) AS HowMany
FROM TableName
WHERE DateField Between #1/1/2005# And #12/31/2005#
AND Count([EmailAddressField]) > 1
GROUP BY "Email Address", EmailAddressField
ORDER BY WhatIsIt, HowMany, EntryValue;

From this query, you get a list of all the types of entries that are
duplicated at least once. You then could use this query to get all the
records that have a specific type of entry.
--

Ken Snell
<MS ACCESS MVP>


Jen said:
I have over 1000 complaint calls already stored in the database. Since we
have particular locations that have 'repeat complainers', I need to locate
these repeats (without using their name or complaint number as a primary
key
- we don't want to block them out of the database, only pinpoint potential
problems). Repeat callers always have 1 of the following things the same -
Full name, Full address, phone number or email address. These callers may
use
a different name and the same address, phone and email many times or they
may
use the same name and have things sent to multiple addresses; sometimes
they
use a different name but the same phone number or the same email address
or
both (we do not always get addresses in the complaints called in to the
1800
number - we only require a method of contacting them to be included so
that
we can try to resolve the situation later). So in answer to your
question -
any of the above can be used as a unique match. In a report I would want
to
see something like a count (if one of the above appears more than two or
three times in the past year - I do have a date function as well) and then
the details of the different calls. I simply do not know how to query for
repeats and for the details of those records. Would something like a count
work with multiple fields? and if so, how would I combine them?

Thank you!

Jen
Click to expand...
Click to expand...
 
Back
Top