Really need help quick Duplicate Query

  • Thread starter Thread starter Patrick Graham
  • Start date Start date
P

Patrick Graham

Hello, I kinda need help in a rush:

I have a table tblIPS that has ClientIDs (<- unique)and
ClientLName, ClientFName(<-unique), IPSID(<-duplicates).

I need a query that will combine ClientLName and
ClientFName of 2 or more people into one record and then
drop their individual records, based on similar IPSIDs.

So A table like this:
Kelly Robinson - 143
Mark Robinson - 143
Ted Robinson - 143
Andrew Mark - 200
Fred Derst - 450
Tessy McClockin - 117

Would end up like this:

Kelly Robinson, Mark Robinson, Ted Robinson - 143
Andrew Mark - 200
Fred Derst - 450
Tessy McClockin - 117
 
I'm guessing I could use a function to get what I want but
would like to keep it simpler then that.
 
Patrick,

Do a search on the term 'Concatenate' in this forum. You will
eventually find the concatenate function written by Duane Hookom that
should meet your needs.

If you don't find anything here search Google.com for Hookom and
Concatenate.

--
HTH

Dale Fye


Hello, I kinda need help in a rush:

I have a table tblIPS that has ClientIDs (<- unique)and
ClientLName, ClientFName(<-unique), IPSID(<-duplicates).

I need a query that will combine ClientLName and
ClientFName of 2 or more people into one record and then
drop their individual records, based on similar IPSIDs.

So A table like this:
Kelly Robinson - 143
Mark Robinson - 143
Ted Robinson - 143
Andrew Mark - 200
Fred Derst - 450
Tessy McClockin - 117

Would end up like this:

Kelly Robinson, Mark Robinson, Ted Robinson - 143
Andrew Mark - 200
Fred Derst - 450
Tessy McClockin - 117
 
Heres what i've done

I took tblClient and made a Find Duplicates Query

Then I made Remove Duplicate from 'Find Duplicates Query'
to give me 1 record for those that had duplicate values
for IPSID.

Then I used tblClient and Find Duplicates Query to give me
all the tblClient records that are not duplicate. This
query is called: tblClient Without Matching 'Find
duplicates for tblClient'

NOW I want to append the Remove Duplicate from 'Find
Duplicates Query' with the tblClient Without
Matching 'Find duplicates for tblClient'

This qould give me 1 record for every IPSID. How do I
append one query to another.
 
This is a lot easier using a concatenation function. I've modified it
here from the original that I had. You might have to tweak it a
little but this should help. I usually pass it a field name, just
like in DLOOKUP, but since you have to merge two fields, I've removed
that portion of the code and replaced it. You would call this
function in your query, something like the following (don't forget to
replace 'yourTableName' with the actual name of your table).

SELECT T.[IPSID], Concatenate("yourTableName", "IPSID = " & T.[IPSID])
as ClientNames
FROM (SELECT DISTINCT [IPSID] FROM yourTableName) as T

Public Function Concatenate(TableName As String, _
Criteria As String, _
Optional Linebreak As
Boolean) As String

Dim strSQL As String
Dim rst As DAO.Recordset

If IsMissing(Linebreak) Then Linebreak = False
Concatenate = ""

strSQL = "SELECT [ClientFName] " & " " & "[ClientLName] as
ClientName " _
& "FROM [" & TableName & "] " _
& "WHERE " & Criteria

Set rst = CurrentDb.OpenRecordset(strSQL)
If rst.BOF Or rst.EOF Then
'don't do anything
Else
While Not rst.EOF
Concatenate = Concatenate & ", " & rst(ClientName)
rst.MoveNext
Wend
Concatenate = Right(Concatenate, Len(Concatenate) - 1)
End If

ExitFunction:
rst.Close
Set rst = Nothing

End Function
 
Back
Top