473,327 Members | 2,103 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Combining two similar records

Okay... I have a query with addresses for clients in which there may be two clients at the same address. I am attempting to combine them so that I can have everyone living at the same address in one record.

This is exported to an Excel file which is used for a mail merge.

Right now we have:

Mr. Smith; 111 valley drive; etc.
Mrs. Smith; 111 valley drive, etc.

Want:
Mr. & Mrs. Smith, valley drive; etc.

Is there an easy way to do this in either Access or Excel? Or I guess, when doing the mail merge?

Thanks,

Tara P
Nov 28 '07 #1
6 1467
Jim Doherty
897 Expert 512MB
Okay... I have a query with addresses for clients in which there may be two clients at the same address. I am attempting to combine them so that I can have everyone living at the same address in one record.

This is exported to an Excel file which is used for a mail merge.

Right now we have:

Mr. Smith; 111 valley drive; etc.
Mrs. Smith; 111 valley drive, etc.

Want:
Mr. & Mrs. Smith, valley drive; etc.

Is there an easy way to do this in either Access or Excel? Or I guess, when doing the mail merge?

Thanks,

Tara P
Hi Tara,

I think basically Tara you have a design issue here. If you are dealing with a whole bunch of data can you be sure that you will get all the results you expect. You could build in a whole bunch of rules to massage the data and look for hierarchy ie: Mr must come before Mrs in any usual salutation string and so on... and if we have a combination of both at any one address combine the two fielded elements by concatenating their titles ie Mr & Mrs and appending only the surname to it etc. But..... you are entering into the arena of consistency checking of your data using VBA code before you can do anything else. Is that the way you want to go? Are you familar with coding?

Regards

Jim :)
Nov 28 '07 #2
Hi Tara,

I think basically Tara you have a design issue here. If you are dealing with a whole bunch of data can you be sure that you will get all the results you expect. You could build in a whole bunch of rules to massage the data and look for hierarchy ie: Mr must come before Mrs in any usual salutation string and so on... and if we have a combination of both at any one address combine the two fielded elements by concatenating their titles ie Mr & Mrs and appending only the surname to it etc. But..... you are entering into the arena of consistency checking of your data using VBA code before you can do anything else. Is that the way you want to go? Are you familar with coding?

Regards

Jim :)
I have limited experience with coding- IOW- not NONE, but not a lot!

For the most part it will be ALL Mr. & Mrs.... but there are a few Dr (some a the same address) but if I can get at least the majority to follow a rule, I can manually correct the few oddballs. But I don't want to manually go through several hundred records!

Tara P
Nov 28 '07 #3
Jim Doherty
897 Expert 512MB
I have limited experience with coding- IOW- not NONE, but not a lot!

For the most part it will be ALL Mr. & Mrs.... but there are a few Dr (some a the same address) but if I can get at least the majority to follow a rule, I can manually correct the few oddballs. But I don't want to manually go through several hundred records!

Tara P
ok.... so in order to understand the very nature of what it is that is required to be done here it will be necessary for you to post the metadata of your tables, in other words the structure, exact table names, exact field names, datatypes and any relationships for the working parts some sample data might be helpful (without real names to addresses make sure of that but at least an example of the type of data entries you have.

Regards

Jim :)
Nov 28 '07 #4
ok.... so in order to understand the very nature of what it is that is required to be done here it will be necessary for you to post the metadata of your tables, in other words the structure, exact table names, exact field names, datatypes and any relationships for the working parts some sample data might be helpful (without real names to addresses make sure of that but at least an example of the type of data entries you have.

Regards

Jim :)
I was just hoping to get pointed to a tutorial or sample code or something to help me figure it out...

It may be silly, but I wouldn't feel comfortable posting the field/table names since it is a work database...

And the query is fairly complicated, because I had to pare the list down to exclude a lot of clients based on several characteristics...

But... the datasheet view when the query is run to export to the excel file has just four fields: Salutation (ie Mr. Smith), address 1, address 2, city/state/zip.

I guess I was just hopint there was some simple if/then command I could type up.

Thinking of the structure of the query, though, I am thinking maybe this would be easier (if at all possible) done once it is exported...

Thanks,

Tara P
Nov 28 '07 #5
Jim Doherty
897 Expert 512MB
I was just hoping to get pointed to a tutorial or sample code or something to help me figure it out...

It may be silly, but I wouldn't feel comfortable posting the field/table names since it is a work database...

And the query is fairly complicated, because I had to pare the list down to exclude a lot of clients based on several characteristics...

But... the datasheet view when the query is run to export to the excel file has just four fields: Salutation (ie Mr. Smith), address 1, address 2, city/state/zip.

I guess I was just hopint there was some simple if/then command I could type up.

Thinking of the structure of the query, though, I am thinking maybe this would be easier (if at all possible) done once it is exported...

Thanks,

Tara P
Thats understandable :) personally I wouldn't be able to determine the overall logic for you where it is based purely on your data ie from my perspective I would have to consider events like what happens where you might have three clients at the same address Mr Mrs and Ms or maybe even more that that or duplicates even?. There may well not be that inconsistency in your data... but I couldn't assume that I don't think from my end.

I don't think there is a simple solution for you Tara I'm afraid unless someone else contributes something. The IF logic can certainly handle the true false scenario but determining the logic is as I say based on your data and handling the potentially numerous variables one might encounter with it.

Like I said originally, you might wish to consider a 'design' change in that something on your system ought to be used as a field a simple check mark even to determine 'Joint client same address' or something like that at the initial input stage which would make it much easier then to grab the data and do what you wished with it later on

Regards

Jim :)
Nov 29 '07 #6
Thanks for the help!

I have deferred the problem to our computer guy... was hoping I could muddle through it myself, as I am trying to become more independent in Access. Since it isn't my primary duty... unfortunately I don't have unlimited time to hash stuff out. Usually I am able to figure out a solution, even if it isn't necessarily elegant, but I think this one just went a little beyond my "expertise"...

And I am on vacation after today :)

This site is great, though, and I am glad I discovered it! I have learned a lot reading through posts and will probably be here often!

Tara P
Nov 29 '07 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

8
by: Ilan | last post by:
Hi all I need to add data from two Excel sheets (both on the same workbook) to an existing table in my SQL DB. The problem is that each sheet holds different fields for the same record, though...
1
by: Heather | last post by:
I know this is a painful question and I have searched the group for past responses and none of the responses seem to cover my specific need. We have a table that contains fields like ID, Yr,...
4
by: Tony Williams | last post by:
I want to combine two tables into one. I have a table with all the field definitions from two other tables. I now need to update this new table with the data from the other two tables.However both...
4
by: mike | last post by:
I have a database table like the following: id|name|item_id|sequence and the following instance data: 1|Apple|419841|1 2|Orange|419841|2 3|Banana|935890|1 4|Lime|959081|1
2
by: Confessor | last post by:
I have two *very* similar groups of related procedures in my program, and I'd like to try combining them, but I'm running into one big problem. Private Sub TileSearchCluster(ByVal...
1
by: ferraro.joseph | last post by:
Hi, I'm querying Salesforce.com via their AJAX toolkit and outputting query results into a table. Currently, their toolkit does not possess the ability to do table joins via their structured...
1
by: scolivas | last post by:
Hi, I have 2 databases - ALdata & SPdata I have 2 tables - vewDemoData (in ALData) & vewDemoDataSP in (SPData) both of these tables are identical in design, only the records are diff. I need...
5
by: Tristan Miller | last post by:
Greetings. Is it possible using HTML and CSS to represent a combining diacritical mark in a different style from the letter it modifies? For example, say I want to render Å‘ (Latin small letter...
3
by: Ken Fine | last post by:
This is a question that someone familiar with ASP.NET and ADO.NET DataSets and DataTables should be able to answer fairly easily. The basic question is how I can efficiently match data from one...
1
by: dlee360 | last post by:
Hello! So I've been trying to figure out how to do the following in T-SQL: Orig Table: Col1 Col2 Col3 Set A ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.