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
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
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 -->
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
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
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 -->
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
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
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.