Merge multiple records in a field, based on criteria?

Follow
Email
Is there an easy way (i.e. not TOO much code) to merge multiple records in a 
field, based on criteria in another field in the same table?

Sample table
LocNo        Name
100           John
100           Jay
101           Kevin
102           David
102           Kelly
102           Mark

What I need is to add a 3rd field that would merge all names that have like 
location numbers:

LocNo        Name           MultName
100           John             John; Jay
100           Jay               John; Jay
101           Kevin            Kevin
102           David            David; Kelly; Mark
102           Kelly             David; Kelly; Mark
102           Mark            David; Kelly; Mark

Any help??  THANKS!!

-- 
GD
0
Reply Utf 12/18/2009 3:43:01 PM
access.gettingstarted 618 articles. 0 followers. Follow

2 Replies
1123 Views

Similar Articles

[PageSpeed] 20
The only way I can think of to do this is to use a recordset.  Essentially 
you run a query in VBA code and "capture" the output as a recordset, which 
you can then manipulate using a set of recordset functions.  It's not that 
hard (although I'm not going to write the code for you!).  Have a look here:
http://www.devdos.com/vb/lesson4.shtml
and also here:
http://allenbrowne.com/ser-29.html

Be sure to include error-handling code if you don't want nasty surprises: 
see:
http://allenbrowne.com/ser-23a.html
(the first nine numbered lines are enough in most cases).

However, while it's perfectly ok to generate concatenations dynamically from 
fields in your database, it's perfectly barbarous to *store* data like this. 
Database design is founded on a number of essential principles, of which the 
first three "normal forms" are considered pretty essential.  If you store 
information which has "repeating values", your design will fail the very 
first of those "normalisation" principles.  This will have the effect of 
making some things you try to do later so damn difficult that your hair will 
turn grey and fall out, and attractive persons of your preferred gender will 
shun your bloodshot eyes.  Think of your genetic legacy before you consider 
this path.

Phil, London

"GD" <gd@discussions.microsoft.com> wrote in message 
news:78709F84-27C7-4F40-888A-A70EA59B464F@microsoft.com...
> Is there an easy way (i.e. not TOO much code) to merge multiple records in 
> a
> field, based on criteria in another field in the same table?
>
> Sample table
> LocNo        Name
> 100           John
> 100           Jay
> 101           Kevin
> 102           David
> 102           Kelly
> 102           Mark
>
> What I need is to add a 3rd field that would merge all names that have 
> like
> location numbers:
>
> LocNo        Name           MultName
> 100           John             John; Jay
> 100           Jay               John; Jay
> 101           Kevin            Kevin
> 102           David            David; Kelly; Mark
> 102           Kelly             David; Kelly; Mark
> 102           Mark            David; Kelly; Mark
>
> Any help??  THANKS!!
>
> -- 
> GD 

0
Reply Philip 12/18/2009 7:03:18 PM
Have a look at this solution:

http://www.mvps.org/access/modules/mdl0004.htm
-- 
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"GD" <gd@discussions.microsoft.com> wrote in message 
news:78709F84-27C7-4F40-888A-A70EA59B464F@microsoft.com...
> Is there an easy way (i.e. not TOO much code) to merge multiple records in 
> a
> field, based on criteria in another field in the same table?
>
> Sample table
> LocNo        Name
> 100           John
> 100           Jay
> 101           Kevin
> 102           David
> 102           Kelly
> 102           Mark
>
> What I need is to add a 3rd field that would merge all names that have 
> like
> location numbers:
>
> LocNo        Name           MultName
> 100           John             John; Jay
> 100           Jay               John; Jay
> 101           Kevin            Kevin
> 102           David            David; Kelly; Mark
> 102           Kelly             David; Kelly; Mark
> 102           Mark            David; Kelly; Mark
>
> Any help??  THANKS!!
>
> -- 
> GD 


0
Reply Arvin 12/18/2009 7:20:17 PM
Follow
Email
Reply:
Similar Artilces:

merging
Is it possible to create a document in publisher (mine is a certificate) and then merge the desired information from a select querry in access? Hi Tina (sttpreston@clyde.k12.oh.us), in the Microsoft� newsgroups you posted: || Is it possible to create a document in publisher (mine is || a certificate) and then merge the desired information from || a select querry in access? Yes, you can. Which version of Publisher and Windows are you using? -- Brian Kvalheim Microsoft Office Publisher MVP Official Publisher MVP Site: http://www.kvalheim.org This posting is provided "AS IS" wit...

Merge same transaction
I've had transactions that I've placed in my register with detailed info in the memo area only to have the same exact transaction download with no info and unrecognized as the same transaction. Traditionally I've copied and pasted the info into the downloaded transaction and then deleted my transaction. This is a real PAIN... Is there a way to tell Money that these two separate transactions are the SAME transaction and then have it merge them? It works exactly as intended when Money recognizes the transaction that's already in the register and then merges them beautiful...

Deleting records from an "Unmatched" query
Hoping someone can help me resolve a seamingly simple task. I have a query that finds records in Table1 that do not have matching fields in Table2 and then I want to delete those records from Table1. I'm obviously a novice but, I usually get by. Need help this time. Thanks. (btw, running Access 2000 & 2007) SImplest is one field in A matches to one field in B DELETE * FROM TableA WHERE TableA.SomeField IN (SELECT MatchingField FROM TableB) -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore Count...

Multiple Excels
Hello Group Here's a really easy question for you with what's probably a really easy answer, but Excel Help is no help so I need your help. I have two Windows shortcuts to open separate Excel files. What I'd like is that they open the files in two separate instances of Excel instead of in the one instance. When I'm working with two workbooks I find it easier to switch between two applications than to flick between two open workbooks using the toolbar option. Can anybody help? Best wishes Peter Kessler Kessler Associates E: digital@kessler-web.co.uk W: http://www.digital.ke...

how to combine multiple versions of single spreadsheet?
Well, the doomsday scenario has happened - two colleagues working on the same spreadsheet fail to communicate. Each has updated a project tracking sheet at the same time so we now have two new versions. Is there a way of Excel combining the latest data out of each sheet? To clarify: Person X opens spreadsheet version A to update and creates version B Person Y opens spreadsheet version A to update and creates version C There is a lot of work entailed in updating the sheets but luckily persons X and Y have their own projects so they do not update the same information. Is there a way to c...

Trying to combine multiple records into one record in a Query
I am new to Access, and very little knowledge about SQL. I read allot about normalization, need to know if I went to far with it with my Family table. Here is the structures for the tables I have, and of the query I want to create. Contact Table: tabContactID (Autonumber) ContactID (Create using VBA in a form) (PK) FirstName LastName etc.... Example: tabContactID ContactID FirstName LastName etc.... 0001 DonDuck Donald Duck 0002 MicMouse Mickey Mouse Next the Family Table: tabFamID (Autonumber)(PK...

Merging 2 Companies into 1
Are there any guidelines or outline for what tables you need to look at to gather the various information you need in order to merge 2 companies together as well as issues to consider when undertaking such a project? It is a project and a half. I got approval from a major client yesterday to proceed with just this project. You literally need to look at all of them! I understand that MS Professional Services will do this project for you. In my case the client cannot be down and has too many 3rd party products for us to pass it off. When does your project need to be completed? -- Ri...

Word mail merge
After printing a document there exists a activity with subject "Word Mail Merge" to the contact with status completed. Is there a possibility to change the subject name or to make a link to the original Word document? R, Joop. I think that the subjct name is hardcoded. The issue of only adding this paltry information to the activity and not showing what document was sent is a problem many have asked about. Microsofts workaround to this glaring gap in functionality is to then add a copy of the word document to the record using notes. Totally unworkable if you have just mail merg...

Opening Multiple files simultaneously
I am trying to open several excel files at once, ie. select 2 or more files, right click, then click open, but I cannot get them to open at the same time. I have to open each file separately. I can have several files open at once but they must be opened individually Hi Lothar, I am not sure of a VBA way to do this but you can certainly open multiple files when using Explorer. Just shift+click the files you require, right click and open files. Regards, Nick. >-----Original Message----- >I am trying to open several excel files at once, ie. >select 2 or more files, right cli...

Can I freeze multiple rows?
I'm working on a spreadsheet that has contact information in the farthest lefthand collumn and collumn headers at the top of the spreadsheet. Is there a way that I can freeze both of them so that I always know what I'm looking at without having to scroll all the way up or all the way over? Thanks a bunch! =) If you click on cell B2 and do Window > FreezePanes, then both column A and row 1 will be "frozen"....... Vaya con Dios, Chuck, CABGx3 " <littlestar@discussions.microsoft.com> wrote in message news:3B58B895-7C24-4418-87B3-B3B5F35AA682@microsoft.com....

Split "Firstname Lastname" text box into two columns, Multiple Aut
Hello, I've got a bibliography data base I'm trying to set up and would like to enter a name, "John Smith" into my form and have it add an entry into my Authors table as Column 1 - John, Column 2, Smith. Also, there are sometimes mulitple authors on any given paper. How can I enter mulitple authors for the same paper? Below is the VBA, SQL code I've been trying to work from. I get an error, "Number of query values and destination fields are not the same" when I try to run it. But it still would 't solve the mulitple author problem even if it wo...

Adding records, duplicate found but can't delete it.
I have a simple DB, one table named "Students". The table has three fields: studentID (unique, no duplicates, primary key) firstName (obvious use) lastName (obvious use) I enter data and all seems to be well until I enter a duplicate studentID. Of course, Access (2003 & 2007) complains about the duplicate and prompts me to change the duplicate field. No problem. However, if I don't want to change the "bad" record but only want to "delete" it and work on some other records already in the table, Access will not let me do this. I must click on the tabl...

mail merge/merging 2 address lists...
Is there a way to merge two of your address lists in Publisher 2002? I have two address lists in my data base folder and would like all the addresses merged into one as there are some addresses different in each and some the same. Thank you. Robin Hi Robin (vicary@kconline.com.NOSPAM), in the Microsoft� newsgroups you posted: || Is there a way to merge two of your address lists in Publisher 2002? || I have two address lists in my data base folder and would like all || the addresses merged into one as there are some addresses different || in each and some the same. No, you cannot. You nee...

adding data from multiple spreadsheets into one chart
I need to put information from 4 different spreadsheets into one graph. Can someone please help me? Produce a graph from your first spreadsheet. Copy the relevant data range from the second spreadsheet, then select the graph and edit/ paste special/ new series or new data points. Similarly for the third and fourth sheets. If you have difficulties getting exactly what you want that way, you can use edit Source Data in the graph to add an extra series or to change the X or Y data range for an existing sreries. -- David Biddulph "Marjory" <Marjory@discussions.microsoft.com&g...

assigning fields to different users (......not owner)
I can use advanced find option and assign particular records to another owner BUT Can i do the same with other fields? for example i have a field name 'primary consultant' and i want to select the records having the specific person primary consultant and then assigning primary consultant to someone else (in batch as i have thousands of records) Hi Aami, have you tried this with the workflow manager yet? Manual rule --> Check entity --> specific field with specific content --> update entity --> specific field with specific new content I hope this helps Andreas Don...

merge and compare
We have two versions of the same work sheet from differnt dates. Some of the cells have been changed in the latest version and we need to compare which cells have had changes and selectively merge the two. Is there any hope??? jimired wrote: > We have two versions of the same work sheet from differnt dates. > > Some of the cells have been changed in the latest version and we need > to compare which cells have had changes and selectively merge the two. > > Is there any hope??? Hi have a look at http://www.cpearson.com/Zips/Compare.ZIP An add-in which compares two worksh...

Merging worksheet into MS Word mail merge
I am merging a letter and an Excel worksheet. When I merge an Excel field that contains $ and commas separating the hundreds and thousands, the $ and the , do not show up in the merged letter. Any suggestions on how to solve this problem? -- Briggs Hi Instead of just referring to the cell e.g. A1 use =TEXT(A1,"[$$-409]#,##0.00") -- Regards Roger Govier "Briggs" <Briggs@discussions.microsoft.com> wrote in message news:2F326201-A50F-45E5-9767-4C189ACA5529@microsoft.com... > I am merging a letter and an Excel worksheet. When I merge an Exc...

Autopopulate Multiple Fields in Table Using a Form
I have a form based on a table where I want to store data. The data to store comes from combo boxes based on various lookup tables. The wrinkle is that there is one lookup table that contains 2 fields of data (questionnum and question) I want stored in the table once the questionnum is selected. The question field is formatted as 'memo'. When I create the combo box the question field is not an option for selection. I add it manually to the properties after completing the criteria for the combo box. I have been struggling with the code used to autopopulate more than one field but...

Sorting Multiple Sheets
I have a workbook with multiple sheets. The main sheet has 2 columns, 1 for first name, 1 for last name. The remaining sheets all have formulas to pull the first and last names automatically from the 1st sheet. If a user sorts the names on the first sheet, they will sort on subsequent sheets, but the information that goes with those names on subsequent sheets will not be included in the sort, thereby misplacing information. Is there anyway to remedy this, short of sorting each sheet each time there is an addition to any one sheet? ...

How can I get bar codes w/ Excel & mail merge?
I want to send out a bulk mailing with large postcards. I want the addresses printed on the postcards via mail merge. I am using Excel, but don't see where or how to add the bar code to the address, which would save me a lot of money. Can you help? >-----Original Message----- >I want to send out a bulk mailing with large postcards. I want the addresses >printed on the postcards via mail merge. I am using Excel, but don't see >where or how to add the bar code to the address, which would save me a lot of >money. Can you help? >. > hi, I not entirely...

Multiple Data Sets on Same Graph
In Excel is it possible to graph 3-x axis and 3-y axis values on the same graph? What I have is x-axis,y-axis Data Set 1: 30000,1.25 Data Set 2: 25000,1.40 Data Set 3: 8000,1.75 I've been using Excel 2000 and it can only do 1 Data Set with x & y on the same bar/line graph. Thanks, Chris Chris - Use an XY (Scatter) chart type. - Mike www.mikemiddleton.com ++++++++++++++++++++++++++++ "Chris" <socialism001@yahoo.com> wrote in message news:d172fd4e.0409292142.72d426db@posting.google.com... > In Excel is it possible to graph 3-x axis and 3-y axis v...

Error in Mail Merge when selecting Edit Individual Labels
We are noticing an interesting error when trying to perform a mail merge within CRM and Microsoft Word. We have created a view in contacts and we perform all the typical mail merge functionality using the Labels option. At the end, instead of printing the labels without any edits, we click on “Edit Individual labels” and we get the following error… C:\Program Files\Microsoft Office\Office12\OUTLOOK.EXE has encountered a problem and needs to close. We are sorry for the inconvenience. We are on CRM v4 Update Rollup 8 using Outlook 2007 and the CRM Outlook (Online only) client. Has an...

Need help with using Month as criteria for SUM(COUNTIFS(...
Hi all, I am fairly new to excel and experimenting some functions with my project. I have a SOLD datasheet with Sold Date (column W) in MM/DD/YYYY format). I am trying to count the items with a few criteria using =SUM(COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2,...criteria_rangeN, criteriaN)) Current formulas are working without any problem. Now, i want to narrow down by months and found out that MONTH functions does not work with current formulas. So i tried adding IF function in front of the current formula Jan=1, Feb=2, ... , Dec = 12 =IF(MONTH(SOLD!W:W)=1, SUM(CO...

Pasting into merged cells #3
I want to copy information from an area that doesn't have merged cells to an area that has merged cells. When I do this I either get an error message or when I use the Paste values and number formats it only pastes every other entry. Is there another way to paste that I don't know of that will paste into the merged cells? -- Thanks, Nikki Hi best would be to get rid of merged cells. You could of course paste cell by cell "Nikki" wrote: > I want to copy information from an area that doesn't have merged cells to an > area that has merged cells. When I do ...

Autofit Row & Merged Cells
Is this just me or is it a common problem: I have some text in a cell. The cell is merged along a row with the adjacent cells, I have put word wrap on. Now Autofit Row doesn't work and only shows the first line of text. Is there anything I can do, apart for manually adjust the row height? Regards Take a look at this macro by Jim Rech: http://google.com/groups?threadm=e1%241uzL1BHA.1784%40tkmsftngp05 If you need help getting started with macros, look at David McRitchie's Getting Started with Macros page: http://www.mvps.org/dmcritchie/excel/getstarted.htm In article...