Filter Contact First Name Last Name

[supportt]

I have a small query built and it is extracting the correct data, but I would like to take it one step further. I have two tables in the query and the following columns: Created Date (the criteria is using the between start date and end date function), First Name, Last Name, Ticket Number and Work Phone. When I run this query for a date range it returns all the tickets for the between date. What I would like to see is: right now it might show a record for contact Bill Williams one time, well, is there a way to filter the query to only show the records if there is more than one Ticket. In another words, I don't want to see if Bill Williams has called in once, only if there is more than one record.
Hope this makes sense.

Thanks

David

 

[Tiro Cupidus]

If [Ticket Number] is the number of times he has called then you add a field to your query that looks like this:

MoreThanOne: IIf([Ticket Number] > 1, "Yes", "No")

Not sure if that's what you were looking for, but it will display "Yes" if there is more than one ticket and "No" if there is not.

 

[supportt]

The ticket number only reprersents a unique number for each call that comes in, not how many times a customer calls in. I am trying to sort out the single calls, only want to see if a customer has called in more than once. Hope this is a little clearer, thanks for the quick reply.

David

 

[sambo]

Create a new field FullName:

FullName: =FirstName + LastName

Now create the aforementioned field MoreThanOne, but replace TicketNumber with FullName:

MoreThanOne: IIf([FullName] > 1, "Yes", "No")

This will filter out Full Names. That way if Patrick Henry calls once and then Patrick Swayze calls once, they are both uniquely identified by their full name.

Finally,
If Patrick Swayze does call tell him I loved his work in Road House... Top Notch Pat... Top Notch

 

[Tiro Cupidus]

MoreThanOne: IIf([FullName] > 1, "Yes", "No")

How can this work if [Full Name] is a string?

 

[sambo]

Touche,

My brain hurts too much to think about this right now.

Algorithmic solution:
Follow the previously mentioned steps and then count the number of times a full name appears (maybe w/ another query), then...

If the full name appears more than once, filter it out.

 

[supportt]

Well, I created a new field in my query and added the criteria: MoreThanOne: IIf([FullName] > 1, "Yes", "No") but as soon as I step off the field I get an error:
The Expression you entered has an invalid . (dot) or operator or invalid parentheses. You may have an invalid or typed parentheses following the Null constant.

Any suggestions????

Thanks

David

 

[Tiro Cupidus]

Hmm... certainly there's an easier way. Here's some code you can put behind the Click Event of a button on a form. (myQry is a query only has a list of names, sorted in ascending order) This should give you a list of only names that occur more than once and list them once for every occurence:

Private Sub Command0_Click()
  Dim rstDupes As Recordset
  Dim db1 As Database
  Dim holdFld, holdStr, SQL As String
  Dim qryDef As QueryDef
  Set db1 = CurrentDb
  Set rstDupes = db1.OpenRecordset("myQry", dbOpenForwardOnly)
  holdFld = ""
  holdStr = ""
  Do Until rstDupes.EOF
    If holdFld <> "" Then
      If holdFld = rstDupes.Fields(0) Then
        If holdStr = "" Then
          holdStr = "[Full Name] LIKE '" & holdFld & "'"
        Else
          holdStr = holdStr & " OR [Full Name] LIKE '" & holdFld & "'"
        End If
      End If
    End If
    holdFld = rstDupes.Fields(0)
    rstDupes.MoveNext
  Loop
  SQL = "SELECT * FROM myQry WHERE " & holdStr

  On Error Resume Next
  db1.QueryDefs.Delete "qryDupes"
  On Error GoTo 0
    
  Set qryDef = db1.CreateQueryDef("qryDupes", SQL)
  DoCmd.OpenQuery "qryDupes"
  holdStr = ""
  rstDupes.Close
  db1.Close
End Sub

 

[Jon K]

You can create a new query based on your original query (type/paste in the SQL View of a new query, replacing with the correct query name and field names):

SELECT *
FROM yourQueryName
WHERE [First Name] & [Last Name] in (Select [First Name] & [Last Name] from yourQueryName group by [First Name] & [Last Name] having count(*)>1);

And run this query.

-----------------------------
Or better still, incorporate:

[First Name] & [Last Name] in (Select [First Name] & [Last Name] from TableName group by [First Name] & [Last Name] having count(*)>1)

into the Where Clause of your original query.