Yet another duplicate record dilemma

I have a table with records where one field are duplicates.  I'm able to 
query to find duplicates and delete them, however what I need to do is find 
the duplicates, produce a total from another field, delete the duplicates and 
update the record field with the new total.
0
Utf
1/13/2008 9:08:01 PM
access 16762 articles. 3 followers. Follow

7 Replies
465 Views

Similar Articles

[PageSpeed] 54

Use the Find duplicates wizard, the build an Update query and either add to 
the field:

Update MyTable Inner Join Querty1.ID On MyTable.ID Set MyField = MyField + 
Query1.MyField

or just update it:

Update MyTable Inner Join Querty1.ID On MyTable.ID Set MyField = 
Query1.MyField

Then delete the duplicate data.
-- 
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"dk_cub" <dk_cub@discussions.microsoft.com> wrote in message 
news:CDC2FD49-71B5-4071-A5BC-6438A6CC6E55@microsoft.com...
>I have a table with records where one field are duplicates.  I'm able to
> query to find duplicates and delete them, however what I need to do is 
> find
> the duplicates, produce a total from another field, delete the duplicates 
> and
> update the record field with the new total. 


0
Arvin
1/13/2008 9:40:59 PM
Thanks Arvin,  however, I get an error when saving the update query - "Join 
expression not supported".   Hmmm...


"Arvin Meyer [MVP]" wrote:

> Use the Find duplicates wizard, the build an Update query and either add to 
> the field:
> 
> Update MyTable Inner Join Querty1.ID On MyTable.ID Set MyField = MyField + 
> Query1.MyField
> 
> or just update it:
> 
> Update MyTable Inner Join Querty1.ID On MyTable.ID Set MyField = 
> Query1.MyField
> 
> Then delete the duplicate data.
> -- 
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.mvps.org/access
> http://www.accessmvp.com
> 
> "dk_cub" <dk_cub@discussions.microsoft.com> wrote in message 
> news:CDC2FD49-71B5-4071-A5BC-6438A6CC6E55@microsoft.com...
> >I have a table with records where one field are duplicates.  I'm able to
> > query to find duplicates and delete them, however what I need to do is 
> > find
> > the duplicates, produce a total from another field, delete the duplicates 
> > and
> > update the record field with the new total. 
> 
> 
> 
0
Utf
1/13/2008 11:01:00 PM
On Sun, 13 Jan 2008 15:01:00 -0800, dk_cub <dkcub@discussions.microsoft.com>
wrote:

>Thanks Arvin,  however, I get an error when saving the update query - "Join 
>expression not supported".   Hmmm...

I think Arvin just was typing a bit too fast... should be

Update MyTable Inner Join Query1.ID 
On MyTable.ID = Query1.ID
Set MyTable.MyField = MyTable.MyField + 
Query1.MyField;

but I'm not certain it's going to work if Query1 has multiple records or a
group by.





             John W. Vinson [MVP]
0
John
1/14/2008 1:07:47 AM
Yes I went too fast, thanks for the catch John.
-- 
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:qbdlo39c1n1923g19nvho8g0qj3dhct1rv@4ax.com...
> On Sun, 13 Jan 2008 15:01:00 -0800, dk_cub 
> <dkcub@discussions.microsoft.com>
> wrote:
>
>>Thanks Arvin,  however, I get an error when saving the update query - 
>>"Join
>>expression not supported".   Hmmm...
>
> I think Arvin just was typing a bit too fast... should be
>
> Update MyTable Inner Join Query1.ID
> On MyTable.ID = Query1.ID
> Set MyTable.MyField = MyTable.MyField +
> Query1.MyField;
>
> but I'm not certain it's going to work if Query1 has multiple records or a
> group by.
>
>
>
>
>
>             John W. Vinson [MVP] 


0
Arvin
1/14/2008 1:26:23 AM
Sorry, it appears to add all the "MyField" values.  I think I'll need to use 
VBA to loop through the records and sum the desired field values...  
What I've got:

Field1     Field2
ab1          2
ab2          5
ba1          1
ab1          2
ba1          1

Desired result:

Field1     Field2
ab1          4
ab2          5
ba1          2

Thanks for all the patience!

"Arvin Meyer [MVP]" wrote:

> Yes I went too fast, thanks for the catch John.
> -- 
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.mvps.org/access
> http://www.accessmvp.com
> 
> "John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
> news:qbdlo39c1n1923g19nvho8g0qj3dhct1rv@4ax.com...
> > On Sun, 13 Jan 2008 15:01:00 -0800, dk_cub 
> > <dkcub@discussions.microsoft.com>
> > wrote:
> >
> >>Thanks Arvin,  however, I get an error when saving the update query - 
> >>"Join
> >>expression not supported".   Hmmm...
> >
> > I think Arvin just was typing a bit too fast... should be
> >
> > Update MyTable Inner Join Query1.ID
> > On MyTable.ID = Query1.ID
> > Set MyTable.MyField = MyTable.MyField +
> > Query1.MyField;
> >
> > but I'm not certain it's going to work if Query1 has multiple records or a
> > group by.
> >
> >
> >
> >
> >
> >             John W. Vinson [MVP] 
> 
> 
> 
0
Utf
1/14/2008 6:55:00 AM
No, a second query, based on the first will do the job:

SELECT tblMyData.Field1, Sum(tblMyData.Field2) AS SumOfField2
FROM tblMyData
GROUP BY tblMyData.Field1;
-- 
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"dk_cub" <dkcub@discussions.microsoft.com> wrote in message 
news:8E24943C-FF07-4CCE-B331-E593DEDB0839@microsoft.com...
> Sorry, it appears to add all the "MyField" values.  I think I'll need to 
> use
> VBA to loop through the records and sum the desired field values...
> What I've got:
>
> Field1     Field2
> ab1          2
> ab2          5
> ba1          1
> ab1          2
> ba1          1
>
> Desired result:
>
> Field1     Field2
> ab1          4
> ab2          5
> ba1          2
>
> Thanks for all the patience!
>
> "Arvin Meyer [MVP]" wrote:
>
>> Yes I went too fast, thanks for the catch John.
>> -- 
>> Arvin Meyer, MCP, MVP
>> http://www.datastrat.com
>> http://www.mvps.org/access
>> http://www.accessmvp.com
>>
>> "John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
>> news:qbdlo39c1n1923g19nvho8g0qj3dhct1rv@4ax.com...
>> > On Sun, 13 Jan 2008 15:01:00 -0800, dk_cub
>> > <dkcub@discussions.microsoft.com>
>> > wrote:
>> >
>> >>Thanks Arvin,  however, I get an error when saving the update query -
>> >>"Join
>> >>expression not supported".   Hmmm...
>> >
>> > I think Arvin just was typing a bit too fast... should be
>> >
>> > Update MyTable Inner Join Query1.ID
>> > On MyTable.ID = Query1.ID
>> > Set MyTable.MyField = MyTable.MyField +
>> > Query1.MyField;
>> >
>> > but I'm not certain it's going to work if Query1 has multiple records 
>> > or a
>> > group by.
>> >
>> >
>> >
>> >
>> >
>> >             John W. Vinson [MVP]
>>
>>
>> 


0
Arvin
1/14/2008 3:21:34 PM
Excellent!  Works great!  Thank You!  Your assistance and patience is very 
much appreciated.

Thanks again,
Doug

"Arvin Meyer [MVP]" wrote:

> No, a second query, based on the first will do the job:
> 
> SELECT tblMyData.Field1, Sum(tblMyData.Field2) AS SumOfField2
> FROM tblMyData
> GROUP BY tblMyData.Field1;
> -- 
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.mvps.org/access
> http://www.accessmvp.com
> 
> "dk_cub" <dkcub@discussions.microsoft.com> wrote in message 
> news:8E24943C-FF07-4CCE-B331-E593DEDB0839@microsoft.com...
> > Sorry, it appears to add all the "MyField" values.  I think I'll need to 
> > use
> > VBA to loop through the records and sum the desired field values...
> > What I've got:
> >
> > Field1     Field2
> > ab1          2
> > ab2          5
> > ba1          1
> > ab1          2
> > ba1          1
> >
> > Desired result:
> >
> > Field1     Field2
> > ab1          4
> > ab2          5
> > ba1          2
> >
> > Thanks for all the patience!
> >
> > "Arvin Meyer [MVP]" wrote:
> >
> >> Yes I went too fast, thanks for the catch John.
> >> -- 
> >> Arvin Meyer, MCP, MVP
> >> http://www.datastrat.com
> >> http://www.mvps.org/access
> >> http://www.accessmvp.com
> >>
> >> "John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
> >> news:qbdlo39c1n1923g19nvho8g0qj3dhct1rv@4ax.com...
> >> > On Sun, 13 Jan 2008 15:01:00 -0800, dk_cub
> >> > <dkcub@discussions.microsoft.com>
> >> > wrote:
> >> >
> >> >>Thanks Arvin,  however, I get an error when saving the update query -
> >> >>"Join
> >> >>expression not supported".   Hmmm...
> >> >
> >> > I think Arvin just was typing a bit too fast... should be
> >> >
> >> > Update MyTable Inner Join Query1.ID
> >> > On MyTable.ID = Query1.ID
> >> > Set MyTable.MyField = MyTable.MyField +
> >> > Query1.MyField;
> >> >
> >> > but I'm not certain it's going to work if Query1 has multiple records 
> >> > or a
> >> > group by.
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >             John W. Vinson [MVP]
> >>
> >>
> >> 
> 
> 
> 
0
Utf
1/14/2008 7:59:02 PM
Reply:

Similar Artilces:

SUM 2 fields and show the result in another
Hi; I need sum 2 field values(textboxes) and show the resul in another field. I´m programming this, but not work.. OnChange { var value1 = crmForm.all.estimatedvalue.DataValue; var value2 = crmForm.all.CFCMonto_de_Venta_Servicio.DataValue; var result = value1 + value2; crmForm.all.actualvalue.Datavalue= result; } What is wrong?, How is? Miguel Have you forgotten to use the complete custom field schema names including prefix eg:- crmForm.all.actualvalue.Datavalue should actually be crmForm.all.new_actualvalue.Datavalue (if new_ is your custom attribute prefix) Peter "...

blank subreports appearing on every record of report
I have a report with many records, and only on a few records there will be a subreport displayed. This works fine, except that a little sliver of the subreport shows up on every record of the report. Each little sliver is blank, but adds a bit of height to each row, and generally looks ugly. I've set all of the 'Can Shrink' properties to yes, including the various sections, controls, and subreport reference on the main report. All the header & footer sections are zeroed out. Isn't this what the 'Filter On Empty Master' property is supposed to do? It ...

Trying to avoid duplicate board position titles
Using WinXP & Access2K2 In my database board members are coded as President, Vice-President, Secretary, and so on. There are a total of 7 positions. Two tables take care of this, one table for the "entity" and one table for the "position". An "Add/Edit" form for the Entities and a subform for the Positions handles the data. Each year, one or more of these entities become board members, non-board members, or they change board positions. As these changes are being entered into the database, there is a strong possibility that adding or changing a position...

Missing TenderEntry record
Have a customer with V1.3 on Win XP SP2 workstations and SBS with SQL Server 2000. Picked up a discrepancy between the Z Report and a special sales report I wrote for them where the sales number was lower on the Z Report. After investigation, I found that the TenderEntry record for the transaction was missing. I haven't been able to duplicate the problem, but the situation raises serious questions about data integrity. Has anyone seen a problem like this? Thanks to all for your comments and ideas. -- Rich ...

How To Transfer An Investment From One Money File to Another?
Is there a simple, effective and straightforward way to transfer transactions relating to an investment from one Microsoft Money 2004 file to another (there are other investments in the account). My wife and I have several mutual funds mixed up between us and would dearly like to eliminate the confusion. TIA -- Growing old is mandatory, growing wiser is optional. In microsoft.public.money, vigilantz <balls69bc@yahoo.ca(donotspam)> wrote: >Is there a simple, effective and straightforward way to transfer transactions >relating to an investment from one Microsoft Money 2004 f...

Updating a cell with data validation when another cell is changed.
In my sheet I have cell Y2, which when it contains "y", I want it to update the validation list =IF(Y2="y",DogList,NAList) That works, but if i delete the "y" from cell Y2, the validation drop- down list doesn't change until I click onto the cell I wrote some code to try and work around this: Private Sub WorkSheet_Change(ByVal Target As Range) Application.Calculation = xlCalculationManual If Target.Address = "Y2" Then Range("AJ2").Value = "" Application.Calculate End Sub But still doesn't do that I want, when I change t...

Outlook duplicating read/deleted e-mails
My outlook is dulicating every e-mail since last Monday over and over again. What is going on? Any ideas? What version of Outlook do you have? What sort of mail account(s)? -- Jeff Stephenson Outlook Development This posting is provided "AS IS" with no warranties, and confers no rights "craig" <craig@salesreality.com> wrote in message news:a04201c35c43$2035b220$a001280a@phx.gbl... > My outlook is dulicating every e-mail since last Monday > over and over again. What is going on? > > Any ideas? ...

Link to a row of another worksheet?
The way I arrange my books record in Excel is have different categores in different worksheet. Information about each book is in one row. Sometimes one book may belong to multipel categories. Is there a way to link the books from another worksheet instead of copy-and-pasting the row from aonther worksheet? Thanks, cpliu On Jan 30, 3:20 pm, "cpliu" <spamfree...@yahoo.com> wrote: > The way I arrange my books record in Excel is have different categores > in different worksheet. > Information about each book is in one row. Sometimes one book may > belong to mult...

Find duplicate records in two tables.
I need to find employees names that show up in two tables (duplicates). I have a table called Did Not Receive Gift Card and a table called Tax List (the table names are saved with the spaces exactly as shown). I need to find employees whose name is on the Did Not Receive Gift Card table AS WELL AS on the tax list table. Both tables use the field of name. This field is the employees full name. I did not set up the table this way, I inherited it so I cannot change it now. In essence what I am looking for are those employees who did not receive a gift card (Name on Did Not Receive G...

How to move Outlook mail folder to another machine?
Hokey Smokes, I can't believe I'm having so much trouble with this :) I have computer A with Outlook 2000 running for a couple of years. There are lots of Outlook mail folders on machine A. Now I've installed Outlook 2000 on machine B and I want to migrate those folders from machine A to machine B. Where the heck are the Outlook mail files kept?!!! (machine A is Windows NT 4.0 sp6) There does not appear to be an "automatic" import in Outlook 2000 to grab mail folders from another machine. Does anyone know how I can do this????? Thanks in advance, Mark-the-bewilde...

recording audio in notebook not working
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) hi, when i try to record a college lecture while typing my notes i cannot hear anything when i replay the file. i know my microphone works because i have used to for ichat and other things. what would be causing this? ...

Duplicate invoice number for different customers
I have a duplicate invoice number for 2 different customers: Cust A: SOPNUMBE = 100 SOPTYPE = 3 Valid (SOP30200) Cust B: SOPNUMBE = 100 SOPTYPE = 2 Want to remove / clear (SOP10100) This was due to server failure that caused dex tables in tempdb to wipe off. Since Cust B is not a valid record and I would like to remove it, but I am not sure what other tables should I look for in the process of cleaning up for customer B beside SOP10100. Is there any tool to fix this issue? I looked in CustomerSource but couldn't find any tool for this. Any suggesti...

counta formula that doesn't count duplicates
I have excel 2003 How can I create a formula that counts items in a column without counting duplicates? sorry if this comes through twice, I entered it once and it doesn't seem to come up even though other new questions have popped in. thanks Meenie Hi, Try this with the range adjusted to suit =SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&"")) Mike "Meenie" wrote: > I have excel 2003 > How can I create a formula that counts items in a column without counting > duplicates? > sorry if this comes through twice,...

Using a datasheet subform as record selector
Dear all, I have an unlinked datasheet subform which I'm using as a quick view list / record selector for the main (detail) form. Basically I'm trying to duplicate split form functionality, but I can't use a split form because it destorys my formatting. So far I have the following code: In the subform: With Me.Parent.RecordsetClone .FindFirst ("daID = " & Me.daID) If Not .NoMatch Then Me.Parent.Recordset.Bookmark = .Bookmark End If End With In the mainform: With Me.sfcAlertSplit.Form.RecordsetClon...

Unable to relay dilemma-production issue
I have an Exchange server running server 2003 with Exchange 2003. We host email for multiple domains. I have been moving domains over to this new server from an old server a little at a time. The old server finally took its last breath and died so I have been moving domains over as fast as I can. It seems that the domains that I had moved earlier are doing fine but some of the domains that I have moved in the last 2 days keep bouncing back with this message... "----- The following addresses had permanent fatal errors ----- <XYZ@somedomain.org> (reason: 550 5.7.1 Unable to r...

copy sheets to another workbook withou formulas using VBA
I want to make a "hard copy" of 3 named sheets in my workbook to a new workbook. The new workbook should contain only "values" no formulas. If possible the user should give the new sheet a name. Regards -- Henk Hi use 'Edit - Paste Special - Values' -- Regards Frank Kabel Frankfurt, Germany Henk van Walbeek wrote: > I want to make a "hard copy" of 3 named sheets in my workbook to a new > workbook. > The new workbook should contain only "values" no formulas. > If possible the user should give the new sheet a name. > > Re...

having one field display when another field changes
I have a field CHSTAT that updates in the original database I want a new field that will display something different as the CHSTAT updates and changes ie. CHSTAT = "10" new field = "Printed"......CHSTAT = 20 new field= "Located" Cheers!! In tables and fields you should have a table that has a field that has the CHSTAT values and a field that has the equivalent text values. Then you join that table to the current table in a query on the CHSTAT values. If you want to do this on a form, you could use a combobox to select the values. The source ...

Data - Filter - Advanced
Hello all I'm doing some spring cleaning on the terminology lists I use in my work as a translator (German to English). These lists are in three columns entitled German - English - Subject. Some of the lists are quite large (several thousand entries) and over the years a lot of duplicates have crept in. I want to find (and delete) only those entries that are identical throughout, i.e. source term AND target term AND subject area. I have applied <Data> <Advanced filers> <Unique records only>. This hides - but does not delete - the offending duplicates; these are st...

Accessing Outlook from another partition
I have a dual boot XP system. The main drive has a corrupted registry and I cannot boot to it. How can I get my existing Outlook with its messages accessed from the other boot drive until I get the registry fixed? Just point Outlook to the .pst file on the other partition and start using it. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, howard@brazee.net asked: | I have a dual boot XP sys...

How do I record a macro with relative cell addresses?
When running a macro to enter date in several different cells, the entries always are made in the original cell addresses that were used during recording. I want to record a macro that allows "relative" cell movement instead of "absolute" (with reference to the starting cell). I seem to recall there was an option available to do this at one time, but I can't recall what it was. I'm using MS Office Excel 2003. What you need is the Stop Recording toolbar, it has the relative button. It's a little hard to find: 1. start recording a dummy macro 2. pull-d...

calendar to record my workouts
Does anyone know a calendar that I can record my workouts in preferably .xls so I cqan add up the calories, minutes, heart rate, etc. Thank you! See following link if helps. http://www.palmpilotarchives.com/diet_fitness_3.html Regards Jed "madeline" <madeline@discussions.microsoft.com> wrote in message news:F0825CEA-A5F7-476F-A676-30FC42D934FA@microsoft.com... > Does anyone know a calendar that I can record my workouts in preferably ..xls > so I cqan add up the calories, minutes, heart rate, etc. Thank you! Thank you, Jed! "Jed" wrote: > See fol...

Report to launch another report
I want to make a report that basically contains no data but rather only is used to launch other hidden reports. Basically the report would be a page with three of four action buttons that would then launch the correspodning reports based on the user input. Is it possible to do this in CRM and if so how? Thanks If you are working with Reporting Services, this is what we did: Put a text box at the report sheet In text box Properties, select Actions, then go to Links There you can select a Report or URL, etc You will see all the reports in your project. Hope this helps. Ana "xxdcmas...

Best way to delete duplcate records in a table.
I have a table that has duplicate records in it. I ran a duplicate query that returned all duplicates. What is the best way to delete the duplicates since you apprantly can not do so while in the query? Any responses are greatly appreciated. Ghost, Without any specific information about your table/fields, that would be hard to say. Give us a bit of info about the relevant fields in your table, and... how you would determine that a record is a duplicate or not. Then we might be able to find some logic (if possible) to assist in their removal. As a very general rule though, d...

Yet another RW suggestion
Report writer strings should be allowed to be 255 characters -- TRM ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=942dbbcc-3b29-4328-a4b0-c5eb62c80a1f&dg=microsoft.p...

Another question on VS2003 to VS2005
I am converting my VS2003 to VS2005 and with the help of this group I got most of all the errors resolved except the following two messages both at the following line of the code extern "C" LONG GSFAR GSPASCAL GSEXPORT RegApp(short nfYear, short ncYear, LPCSTR path, LPCSTR lpath, BOOL ball, HWND hWnd) { the errors are 1. syntax error : 'int' should be preceded by ';' 2. missing type specifier - int assumed. Note: C++ does not support default-int BOOL is typedef int and others are stright forward. I can not figure out why is it failing. Thanks. "DBC User&q...