×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Search & Concatenate VBA

thread700-1754960

Search & Concatenate VBA

Search & Concatenate VBA

(OP)
Hi there

Can anyone help. I have a database that has name and address details included.

I need to find exact address matches, then combine the recipients names at the same address into one record/field with the following criteria:

2 people with the same surname at same address:

Fname Sname Add
D Marri 12 Colin St
K Marri 12 Colin St

becomes

Fname Sname Add
D & K Marri 12 Colin St

More than 2 people with the same surname at same address:

Fname Sname Add
D Marri 12 Colin St
K Marri 12 Colin St
C Marri 12 Colin St

becomes

Fname Sname Add
The Marri Family 12 Colin St

2 or more people with the different surnames at same address:

Fname Sname Add
D Marri 12 Colin St
L Twight 12 Colin St

becomes

Fname Sname Add
To The Residents 12 Colin St

Firstly is this possible and I hate to ask but would anyone have any code that would achieve this?

Kind regards

Duncan

RE: Search & Concatenate VBA

To simplify this I would make a new table and import the results into there. If not you would have to merge and delete records. Would be more complicated.

Here is my table called tblAddress

CODE

ID	Fname	Lname	address
1	D	Marri	12 Colin St
2	K	Marri	12 Colin St
3	John	Smith	1st Street
4	Joan	Smith	1st Street
5	Brian	Smith	1st Street
6	Mike	Brown	Park Avenue
7	Karen	Black	Park Avenue
8	Bob	Jones	Elm Street

I then built a new table called tblAddressNew without any data.

Next built some helper queries
qryDistinctAddresses to get a list of each address

CODE --> 

SELECT DISTINCT tblAddress.address
FROM tblAddress;

address
12 Colin St
1st Street
Elm Street
Park Avenue
qryCountPeopleInHome to get the total count in the home

CODE

SELECT tblAddress.address, Count(tblAddress.Lname) AS PeopleInHome
FROM tblAddress
GROUP BY tblAddress.address;

address	PeopleInHome
12 Colin St	2
1st Street	3
Elm Street	1
Park Avenue	2
qryDistinctNamesInHome to get the distinct list of lastnames

CODE --> 

SELECT DISTINCT tblAddress.Lname, tblAddress.address
FROM tblAddress;

Lname	address
Black	Park Avenue
Brown	Park Avenue
Jones	Elm Street
Marri	12 Colin St
Smith	1st Street
qryCountNamesInHome to get the count of last names in a home

CODE --> 

SELECT qryDistinctNamesInHome.address, Count(qryDistinctNamesInHome.Lname) AS NamesInHome
FROM qryDistinctNamesInHome
GROUP BY qryDistinctNamesInHome.address;

address	NamesInHome
12 Colin St	1
1st Street	1
Elm Street	1
Park Avenue	2

Now these are referenced in the following code. It reads all the distinct addresses. Then based on the number of people in the home and the number of different last names it determines what to insert into the new table

CODE --> 

Public Sub UpdateAddresses()
  Dim rs As DAO.Recordset
  Dim strSql As String
  Dim address As String
  Dim lastName As String
  Dim firstNameOne As String
  Dim firstNameTwo As String
  Dim numberInHome As Integer
  Dim numberOfNames As Integer
  Dim moreThanOneName As Boolean
  
  
  'Add one distinct address
 ' strSql = "INSERT INTO tblAddressNew ( address ) SELECT DISTINCT tblAddress.address FROM tblAddress"
 ' CurrentDb.Execute strSql
  
  Set rs = CurrentDb.OpenRecordset("qryDistinctAddresses", dbOpenDynaset)
  Do While Not rs.EOF
    address = rs!address
    'need sing quotes
    address = "'" & address & "'"
    numberInHome = DLookup("PeopleInHome", "qryCountPeopleInHome", "address = " & address)
    namesinhome = DLookup("NamesInHome", "qryCountNamesInHome", "address = " & address)

    If numberInHome = 1 Then
      strSql = "INSERT INTO tblAddressNew SELECT tblAddress.* FROM tblAddress WHERE tblAddress.address = " & address
    ElseIf namesinhome > 1 Then
      strSql = "INSERT INTO tblAddressNew (Lname, address) values ('To the Residents'," & address & ")"
    ElseIf numberInHome > 2 Then
      lastName = DLookup("lname", "tblAddress", "address = " & address)
      lastName = "'The " & lastName & " Family'"
      strSql = "INSERT INTO tblAddressNew (Lname, address) values (" & lastName & "," & address & ")"
    ElseIf numberInHome = 2 Then
      lastName = DLookup("lname", "tblAddress", "address = " & address)
      firstNameOne = DLookup("fName", "tblAddress", "address = " & address)
      firstNameTwo = DLookup("fName", "tblAddress", "address = " & address & " AND Fname <> '" & firstNameOne & "'")
      lastName = "'" & firstNameOne & " & " & firstNameTwo & " " & lastName & "'"
      strSql = "INSERT INTO tblAddressNew (Lname, address) values (" & lastName & "," & address & ")"
    End If
    Debug.Print strSql
    CurrentDb.Execute strSql

    rs.MoveNext
  Loop
  
End Sub

Output

CODE --> 


Fname	Lname	          address
Bob	Jones	          Elm Street
	To the Residents  Park Avenue
	D & K Marri	  12 Colin St
	The Smith Family  1st Street

RE: Search & Concatenate VBA

(OP)
Thank you

What about if my original table structure is

Fname Sname Address1 Address2 Locality state Postcode

and both address fields required comparison

Could you show me the modified code for that schema?

RE: Search & Concatenate VBA

scuba,

This is called (in the professional world) 'intentional scope-creep' - may I suggest that you try, and tell us how you failed, which we can then advise you on?

This is not a 'get your code free' forum.


ATB,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close