Sorry about being vague (I'm new to this)
Thanks for posting back. Just remember - we're volunteers, donating
our time to the newsgroup; and we can see the message you type but
nothing else about your application!
I work in a call center and am attempting to track duplicate rollouts to
CSR's. Each log in the database is stamped with an employee ID, starting
with a different prefix depending on the reps skill set, for instance one ID
might be we12345, another might be rt45678. I am attempting to find
interactions that meet this criteria: spoke to a "we" rep and then an "rt"
rep on the same day. I do not need any data about these dup records, just
how many times this occured over a month for instance.
Tricky! Assuming that:
- that the table is named Interactions
- there is a field CallerID and you want to find duplicates within a
single day
- that you have a field CallDateTime with the date and time of the
call
then a self-join query should work.
SELECT A.CallerID, A.CallDateTime, A.EmployeeID, B.EmployeeID
FROM Interactions AS A INNER JOIN Interactions.B
ON A.CallerID = B.CallerID
WHERE A.EmployeeID LIKE "we*" AND B.EmployeeID LIKE "rt*"
AND DateValue(A.CallDateTime) = DateValue(B.CallDateTime);
You could then change this to a Totals query to count occurances.
Just one concern - Your EmployeeID violates the basic "atomic" rule of
relational design. Storing two pieces of information, a unique
personal identifier and a skill set, is not ideal! They should
probably be stored in two separate fields; or, if you really want to
reflect the reality that a given employee might be multitalented, in
three tables: Employees, Skills, and SkillSets.
John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps