Merge Like Records
Sep 26, 2007
Hi I hope someone can help.
I have a table with records in like the follwowing examples.
Record 1
ID - 1
AccountID - 123
Name - Mr Smith
Address - 38 The Close
PetName - Tilly
Record 2
ID - 2
AccountID - 123
Name - Mr Smith
Address - 38 The Close
PetName - Chloe
Record 3
ID - 3
AccountID - 124
Name - Mr Jones
Address - 49 The Avenue
PetName - Francis
I would like to be able to run an update query that would merge the records with the same Account ID by PetName so that my table would now look like this
Record 1
ID - 1
AccountID - 123
Name - Mr Smith
Address - 38 The Close
PetName - Tilly & Chloe
Record 3
ID - 3
AccountID - 124
Name - Mr Jones
Address - 49 The Avenue
PetName - Francis
So record 2 would have been removed but the Pet that belongs merged with an & into record 1.
It's had me puzzled me for some time.
Thanks to any replies in advance.
theoldskoolninja
View Replies
ADVERTISEMENT
Mar 8, 2006
I have a table with the following fields for each record:
StudentID CsrID Average FinalExam
For most StudentID/CsrID there are two records, one with the Average and a null FinalExam, one with the FinalExam and a null Average. If there is no FinalExam, no record is generated.
How can I merge the records so that I will have only one record for each StudentID/CsrID that includes both the Average and the FinalExam?
View 2 Replies
View Related
Jan 11, 2007
I need some to PLEASE point me in the right direction.
I have a database of magazine articles that are listed like this:
Magazine Date Article
Time 2003-01 Iran War
Time 2003-01 Pres Campaign
Time 2003-01 Polution
Time 2003-02 Senate Race
Time 2003-02 New Cars
Time 2003-02 Cold Wave Hits
I need to merge that down to look like this:
Time 2003-01 Iran War,Pres Campaign,Polution
Time 2003-02 Senate Race,New Cars,Cold Wave Hits
Can anybody help?
View 2 Replies
View Related
Jul 15, 2014
The highlighting shows what records are duplicates. Those are the only records that need to change. Not every field of the record is a duplicate, and I don't want some of the fields that are duplicates to be combined.
If the result I want is not possible, or way too complicated for me is there a way to just have this: [URL] ....
Basically, I'm just getting rid of the sum within the merged record.
Note: I want this information to show up in a report. I put the information in the images into excel just to make it easier to show. Also, if you end up testing this yourself could you provide me with your Access document.
View 1 Replies
View Related
May 12, 2005
Hi I wonder if anyone can help? I have looked through prevous posts and cant find any answers that fit what I need to do. For reference I am using Access 97 and Word 97.
I want to be able to have a client record open in a form and click a button and for that particular record to merge with a preformatted word doc. I have created a query that gives me all the fields I need (as they are over multiple tables) and I can successfully combine this into a mail merge. But it currently takes *all* the records and I want it to only do the current one. I also want user to be able to click a button somewhere on the form that automates this process for him. Is this possible?
I have investigated transfertext and filled in what I think I should have but it seems to do nothing. It creates another (unopenable) file in the same folder as the word doc and does nothing else. But I am using the 'export word for windows merge' in the tranfertext action?
If I can only crack this I will be happy! Many thanks in advance for any help
DD
View 3 Replies
View Related
Jun 24, 2013
I have a report that is derived from a table (tblDocs) the structure of the table is:
EmpId docType docDate comments
(number) (number) (Date) (text)
This table will have several entries for each employee. There will only be a few times that a comment will be entered. On my report, I will have a field for the comments and I would like ALL of the comments for each employee combined into one text box in the report There will be blank entries in most of the records). I think the control source for this might be a query, but not sure. I set the properties of the text box to can grow.
View 3 Replies
View Related
Jun 9, 2014
Is there a way to merge duplicate/similar Access 2010 records into one record?
I have an Access table with 1,000 duplicate records, although they are similar and not exact duplicates. As you can see below, some records contain information that other records do not. Yet, the primary key is the same for all duplicate records. I want to find a way to merge data from filled cells of duplicate records into empty cells for each duplicate record. I do not want to concatenate the data (i.e. combine last and first name, etc.). I only want to fill empty cells if there is a match for it in a duplicate record. I will delete the newly exact duplicate records later. Short of correcting the records by hand.
Example
Code:
LastName FirstName SSN Address Phone Email
Doe John 123-45-7891 123 Anywhere St. NULL john(at)gmail.com
Doe John 123-45-7891 NULL (123)456-7890 NULL
Desired Result
Code:
LastName FirstName SSN Address Phone Email
Doe John 123-45-7891 123 Anywhere St. (123)456-7890 john(at)gmail.com
Doe John 123-45-7891 123 Anywhere St. (123)456-7890 john(at)gmail.com
View 2 Replies
View Related
Jan 5, 2006
I have 1 a table named Products and i'm using group by, i have a field named etc witch contains free text and i want to merge all data from that field to 1 filed called all_etc (from all records i mean)
so my table looks like this :
id name code qua etc
1 prod_1 001 1 etc_1
1 prod_1 001 5 etc_2
1 prod_1 001 1 etc_3
1 prod_1 001 1 etc_4
1 prod_1 001 1 etc_5
and my query :
id name code qua etc
Group By Group By Group By Sum ???????????
1 prod_1 001 1 etc_1
1 prod_1 001 5 etc_2
1 prod_1 001 1 etc_3
1 prod_1 001 1 etc_4
1 prod_1 001 1 etc_5
i want to have 1 record like this :
id name code qua etc
1 prod_1 001 9 etc_1etc_2.....
Any ideas ???
Thanks In advane
And Happy new Year
View 3 Replies
View Related
Jul 20, 2005
When applicable, I have letters in Word already set up to show the cc: information at the bottom. However, Management needs a statement added to the bottom of the letters that ONLY show the cc:
I am having a slight problem trying to figure out how to make this statement show up only on certain letters.
Does anyone have any thoughts that could help me? I'd appreciate it very much.
View 2 Replies
View Related
Dec 28, 2005
I am totally new to Access and this is my first database.
I have a database for volunteers in which table A is basically a calendar and table B a list of volunteers with their details.
What I want to do is construct a report that will allow me to produce a mail merge letter such that:
1. One volunteer, with his/her information, is selected from table B and inserted into a mail merge letter. (The volunteer selected will change from letter to letter).
2 Certain days are selected from table A and added to the mail merge letter. This information will be the same in all letter.
I have posted a separate thread asking how I can make the selection of days in (2) above so you can assume that I know how to make the selection within table B.
How do I proceed?
If anyone knows a good example of this type of report then i will be extremely grateful.
View 3 Replies
View Related
Jan 17, 2006
I have 3 tables: tbl_1, tbl_2, tbl_3
All 3 have the same 3 columns: name, email, code
Can I write some queries or a code or something that can create one "tbl_MASTER" with the same 3 columns?
Ex:
tbl_1 - 3 records
tbl_2 - 7 records
tbl_3 - 21 records
tbl_MASTER - 31 records. Now the information in the other 3 tables changes everyday so I want it to all be automatic, I dont want to execute 3 different queries or anything like that. Is this doable. Thanks.
Travis
View 9 Replies
View Related
Jan 23, 2006
I have a table that has, among others, Address1, Address2 and Address3. If Address2 andor Address3 are empty for a particular record, I get blank lines in the Word doc. How do I prevent these blank lines?
View 2 Replies
View Related
Apr 12, 2006
Hi, i have set up a mail merge between a query and a word document. My system is a system that processes transactions from an online cd shop. I have made it so that each cd that is bought by each customer is a different transaction in a transaction table, and each transaction is given an order number to group them. How do i get the mail merge to show all the transactions in an order on the invoice in my word doc because at the moment, even if i add two of the title name fields, they both show the same transaction.
View 4 Replies
View Related
Apr 27, 2006
Im having a problem getting this code to work.
It is meant to fire up Word (which it does), open up a new doc based on the template (which it also does) and then populate a field in the doc with data from the query table (which it doesn't).
I've put the fields in the Word doc via firstly, defining them as Custom Document Properties in the File, Properties box, and then as "{ DOCPROPERTY "<fieldname>" *MERGEFORMAT }" codes in the text.
The field in the Word doc is type 'text' and the field in Access is type 'text', so I am stumped by the "Type Mismatch" error it gives when attempting to get/open the recordset.
It hasnt even got to transferring the values across to the doc.?
Ive not done this before (Access newbie) and even found creating the custom fields in Word not as straighforward as I expected!
Please can someone help?
Private Sub butDocPreview_Click()
Dim dbs As Database
Dim objDocs As Object
Dim objWord As Object
Dim prps As Object
Dim rst As Recordset
Dim strClient As String
Dim strAccountManager As String
On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Err.Number = 429 Then
'Word is not running; creating a Word object
Set objWord = CreateObject("Word.Application")
Err.Clear
End If
On Error GoTo cmdWord_ClickError
DoCmd.SetWarnings False
'Open query..
DoCmd.OpenQuery "qryClientDocHdr_Export"
intCount = DCount("*", "tmpClientDocHdr")
Debug.Print "Number of Text items: " & intCount
' Check that there is at least one line..
If intCount < 1 Then
MsgBox "No text to process; cancelling"
Exit Sub
End If
Set dbs = CurrentDb
'==stops here with "Type Mismatch" error==
Set rst = dbs.OpenRecordset("tmpClientDocHdr", dbOpenDynaset)
With rst
strClient = Nz(![Client])
strAccountManager = Nz(![AccountManager])
End With
rst.Close
...
...
...
View 3 Replies
View Related
Aug 20, 2006
Hi,
I have been searching for related posts to this question; however, there either were not any posts that answer it or I can't find any.
Basically, I need to do something exactly similar to mail merge, but with email. Is that possible in Access?
Any help will be very much appreciated.
B
View 3 Replies
View Related
Nov 12, 2006
I have a database with two tables, Buyer and Products, with one-to-many relationship. The key of Buyer is an autonumber ID, which is the foreign key to table Products.
The problem is that i have two copies of this database with different records (with ID starting from 1 to each one) and i want to merge the databases into one.
Any ideas how to do this without lose the connection of the records?
Thank you in advance.
View 2 Replies
View Related
Mar 30, 2007
Hi,
How do I merge these two datbases? I have downloaded them from Microsoft online templates, here
http://office.microsoft.com/en-gb/templates/TC010178391033.aspx?CategoryID=CT101426031033&av=ZAC000
and here
http://office.microsoft.com/en-gb/templates/TC010185481033.aspx?CategoryID=CT101426031033&av=ZAC000
and I want to have the two databases as one so that there will be a fourth tab on the cintact management database with the order form on? I want to be able to link the two so I can have all the customers details and orders together and print off reports. I really have not dealt with Access before so I really dont have a clue. Any help would be truly appreciated. I am sure it is easy but I just cant work out how to do it.
Thanks very much
Danny :)
View 3 Replies
View Related
Apr 2, 2007
Anyone have a good online resource for generating a single thank you letter, eg. by way of a mail merge using MS Access 2003 with Word 2003? I know you need to write a form letter first with various fields that conincide with fields in Access. I have done this many times with large mailings but need to know how to write individual letters for thank yous, invites, etc.
Thanks in advance
View 4 Replies
View Related
Nov 1, 2007
I'm trying to merge 3 tables into 1
Made new table which contains all the fields from the 3 source tables
Exported source table 1 to xls
On importing to destination table I get "an error has occurred. Data not imported"
Can anyone shed light on how I can do this or waht teh problem might be?
Many thanks
Paul
View 3 Replies
View Related
Dec 10, 2005
I need to merge two Access DBs but I need to know if it is even possible before I start learning how to do it. It is not easy for me to express the problem but here goes:
DB A:
-Flat Table with numbered indexes
-will use a unique field as my index for the merge
(not the primary key)
DB B:
-The destination
-One to Many relation (table 1 to table 2)
-primary key not numbered field (I think)
-will match primary key to the unique field in DB A
-a few fields from DB A will be appended to table 2
....................DB B
Table1----------------Table2
******---------------************
*-----*-one to many-*-------------*
******---------------*-------------*
-----------------------*-------------*----append----------DB A
-----------------------************...<<<<<<<......***********
--------------------------------------------------------*-----------*
--------------------------------------------------------***********
View 1 Replies
View Related
Sep 15, 2006
I have a very simple question which is anoying me already for a very long time. I have two tables both with a key field, a common field and a table specic field. Like :
Key,common,spec1
222,AAAA,20000
333,BBBB,0000
444,CCCC,22000
Key,common,spec2
111,EEEE,PRO
222,AAAA,PRV
777,FFFF,PSR
Which I want to merge to one table like this :
Key,common,spec1,spec2
111,EEEE,-----,PRO
222,AAAA,20000,PRV
333,BBBB,10000,---
444,CCCC,22000,---
777,EEEE,----- ,PSR
If I set a relation on the keyfield, where both keys should be equal, the result is :
222,AAAA,20000,PRV
If I set a relation on the keyfield, wiith all records from table 1 and from table 2 where both keys are equal, the result is :
222,AAAA,20000,PRV
333,BBBB,10000,---
444,CCCC,22000,---
If I set a relation on the keyfield, with all records from table 2 and from table 1 where both keys are equal, the result is :
111,EEEE,-----,PRO
222,AAAA,20000,PRV
777,EEEE,----- ,PSR
How do I solve this?
View 6 Replies
View Related
May 2, 2007
I have database A & database B, which is a copy of the first.
I have made some changes to database B but need to merge one table back into the original database.
Any ideas.
Thanks.
View 2 Replies
View Related
Apr 1, 2008
How can I merge 4 fields from 1 table into new field which will look like:
field1,firld2,field3,field4 ....
View 3 Replies
View Related
Oct 3, 2006
Hi,
I want to create an expression in a query, which is a combination of one or more fields in a table. e.g., Concatenate function in excel. Is there any such function in access. Please help me.
Thanks & Regards
View 2 Replies
View Related
Feb 15, 2007
Hi,
I am editing the design of a census, and I would like to be able to mail merge form the query directly to a new document in microsoft word...so you can create the document and then merge to it, is this possible using a macro or VBA? I can't work out how to do it!! The query name is "List of Families" and I want to merge to a blank document, so I can create a different document everytime...as the letters aren't circulars!
Chris
View 1 Replies
View Related
Dec 16, 2007
Howdy, a newbie here, and new to access, but pretty strong computer skills..
need you're help please....
trying to "merge" fields - i think merge is the right name for it...
here's an example of the data...
100 ___
200 200
300 ___
___ 400
500 500
600 ___
___ 700
where the ___ = empty
I need it to look like this, a single field..
100
200
300
400
500
600
700
the values in the two fields are always the same if the exist in both fields..
thanks in advance!!!
Happy Holidays!
View 13 Replies
View Related
