I've been working on a procedure to step through the recordset and add the data one record at a time so I can get rid of the duplicates.
I've tried a few approaches, but this is where I'm at now.
Code:
Dim rs As DAO.Recordset
Dim rsHH As DAO.Recordset
Dim rsPhone As DAO.Recordset
Dim rsEmail As DAO.Recordset
Dim rsAddress As DAO.Recordset
Dim rsPerson As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
I have to deal with string arrays that store text. I need info on copying, comparing, appending arrays. Also on passing arrays as parameters to subs or functions. Where I can get to this info quickly without having to browse through many screens.
In addition to this I have some questions:
I have to find the duplicates of values in an array. Here is the code that I use.
Code: Sub FindDuplicates() Dim I As Integer, J As Integer, IEND As Integer, text() As String ReDim text(IEND) For I = 1 To IEND - 1 For J = I + 1 To IEND If text(I) = text(J) Then text(J) = "" Next J Next I End Sub
It works but is not performing well. Are there more efficient ways of doing this?
Ref# Rev 97 b 98 c 99 c 99 e 100 c 100 b 101 a 102 b
I need to create a simple report but remove the duplicates (ex. Ref# 99,100). I need to delete the older Rev's (Ex Ref# 99 Rev C, Ref# 100 Rev B).Is this done throughRecordsets? will an SQL query do the trick?
I have code written which imports excel data to a access table but after the first import it fails due to duplicates, how can i tell it to ignore duplicates in the table and only copy new records?
code is below.
Code: Function SyncEmployes() Dim lngColumn As Long Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
I'm trying to parse the following into an Array by splitting the csv file using a "," comma separator. There should be 63 different data pieces in this File. When I do a count of them from the (ubound array) i only get 54. The last data piece on each row gets concatenated to the first data piece of the next line. Is there a way to stop this from happening? This is causing problems with working with the data.
I've done some basic work with arrays.. writing array data to form list objects.. How to use an array as a data source for a report?
Would i need to create a recordset and populate it with the array, then bind the report to it ?
The reason I am asking is the previous developer here built every app using arrays and UDT's... the apps are completely disconnected from the data. Everything is loaded in to arrays..
I have a query linked to a main frame database. One of the fields is [significance] and gets a number 1-20. Usually when this data is entered, it gets multiple significance numbers. This causes my query to return separate records for each significance number. For example if case number 123 is given significance codes 1, 5 and 12, then my query returns 3 records.
I need a query that will show all records one time that have a significance code other than 12. This would be easy if there were not duplicate entries for the same case number because I could simply say "Not 12". So in the example above, my query returns 2 records showing significance codes 1 and 5. But I don't want to see the record for case number 123 because it also has a 12 significance code.
If have a table with billing occurances and one of the fields is "business name" how would I need to setup a make table query which lists unique occurances within that data?
Essentially, I want to remove duplicates. If ABC Pet Store has 5 billing occurances and XYZ Pet Store has 1... I want both to only be listed once.
I hope that makes sense. Thanks for any help as to how to set this up!
I would like to remove duplicates from the following query. I would like it to display only one record depending on the InvoiceID. So only show one unique record based on Invoices.InvoiceID. Thanks!
SELECT DISTINCT Invoices.InvoiceID, Invoices.CustomerID, InvoiceDetails.InvoiceDetailID, Invoices.InvoiceDate FROM InvoiceDetails INNER JOIN Invoices ON InvoiceDetails.InvoiceID = Invoices.InvoiceID WHERE (((Invoices.InvoiceDate) Between #8/1/2004# And #8/31/2004#) AND ((InvoiceDetails.DeliverBy)=0)) ORDER BY Invoices.InvoiceID;
Hi all, total newb here with a question i can't find answered anywhere. (you are my only hope) This database contains over 70k records and is a collection of user registrations over the years. Heres the issue.
After running the find duplicates query and get my list (over 8000 dupes) i get a sample like below. I cannot now run the append query as every other site or article says to. I can't set the primary key to surname because there are so many records that have the same last name but diff. first name. I need it to remove the dupes based on the EditDate, keeping the newest record.
I have the below code but I want to add a grouping to it so that if there is a duplicate building number it will not list it. Is there a way to add a grouping into the code to do this?
I have a table of 50,000 reccords. The table has 8 fields of theses eight fields I want to remove duplicates based on 4 of the fields in a query. Is there a way I can do this and keep the uniqe identifyer so that after the duplicates are removed from those 4 fields I can match them back up with the other four fields????
I have the following set up on a form to pull together a query (by form) and resulting report:
Publication Sector Product Region
Each publication can have multiple sectors/products/regions. The resulting query therefore duplicates the publication, for example:
Publication 1 Sector 1 Product 1 Region 1 Publication 1 sector 2 product 1 region 1 publication 1 sector 2 product 2 region 1
etc etc...
The report ONLY has publication on it, but as it is based upon the query utilising the 4 factors above, the resulting report is: Pub 1 Pub 1 Pub 1 Pub 2 etc
I want the report to only list the publications once - i have tried "hide duplicates" from the form field properties, however this hides the text but leaves a big gap on the resulting report when generated where the duplicate would be if it were not hidden.
There must be a simpler way to acheive this than getting another query to create a table based on the first query which (the table) only includes the publication name, and is filtered to remove duplicates through a primary key...
I am working on a database to manage newsletter subscriptions. Each subscriber record has the option of having up to four email addresses registered to his/her name.
Is there a way to check for duplicate email addresses in the entire database? It would have to compare all values in all four fields of all records.
Any ideas on how to implement such a thing? I'm clueless...
I am trying to design a delete query that has an additional criteria needed.
I want to delete identical IDs in one column only if there are identical values in an adjacent column.
So for example, in the table below I want to delete the last row where the duplicate ID is "2700023" because the Code field has identical values, "LRAC". I do not want to delete the second row because the Code field is different for the row.
I have a table called Stock Levels which contains 3 fields. (ID, ProductID, StockLevel) ID is the Pkey, ProductID contains duplicates and StockLevel which contains different stock levels
and I am trying to remove the duplicates and retain the the data so I am left with the correct stock number
what I have done is the following, but I am still getting duplicate values in productid and stocklevels
SELECT DISTINCTROW id, productid, stocklevel into mynewtable from stocklevels
I have a large table with many fields and many rows. There is no primary key. I'll call one field ParentPN, and another field ChildPN. There are many other fields as well. I want to identify all rows where BOTH the ParentPN and ChildPN occur more than once. I know how to create a query to identify duplicates of ONE field in the table, but not two. I can solve this with VBA: I will read the two fields of interest in the first row, then compare both values with every other row. If it finds another row with BOTH ParentPN and ChildPN identical with the first, that's a "hit". Then, repeat with all the other rows. I could find ways to make this run faster, but I was wondering if there are any build in functions to accomplish this. I looked at the Find Duplicates query builder, and all I see is I can select ONE field to search for dupes, not two.
I have 4 or 5 tables. Most of the fields are exactly the name but they all have at least 1 to possibly 5 or six fields that are not in the other table. Additionally there are some duplicates within the individual tables as well as across tables.
i.e.
I have a
Student Table - with all the info on the student as well as a column called student that identifies them as such however it does not have the columns parent, donor, appeal, designation..... Parent Table - with all the info on the student as well as a column called parent that identifies them as such however it does not have the columns student, donor, appeal, designation..... Donor Table - with all the info on the student as well as a column called donor that identifies them as such however it does not have the columns student, parent, appeal, designation..... Appeal Table - with all the info on the student as well as a column called appeal that identifies them as such however it does not have the columns student, parent, donor, designation.....
-A person can be within one of these tables more than once but with all the same information. -A person can also fall into all of these parameters so they could be on every table with the same information in addition to the missing columns,=.
Question 1 : what is the best way to dedupe and delete the individual tables (they all have account numbers) Question 2: I was thinking create a new table with all the columns available, however how do i dedupe across tables while populating the additional columns from each?
I have two tables, one is of departments, and one is of people (with a FK denoting what department this person is in). Now consider the fact that there are duplicates in the departments table, and I would like to remove these duplicates. However, the duplicates have related records (in the people table). So, before removing the duplicates, I must update the FKs in the table of people (this is the step I'm having trouble with).
Here's an example:
As you can see, the "Sales" department is there twice. And both have a related record. What I want to do is: Update all DepartmentIDs (in tblPeople) to not point to duplicate records. In this example, that would be PersonID 2; Joe. His DepartmentID should update to "1" (as both "1" and "2" are "Sales").Delete the duplicates in tblDepartments (in this case, DepartmentID 2, "Sales").
The second step is no problem, it is only the first I am struggling with.
Also, the example posted here is just an example, the data I actually need to do this for is significantly more complex and there are many more records! In the attached database:
qry1: Simple query to find all duplicates (just used the query wizard) qry2: Just the first row of each duplicated departments (duplicates that shouldn't be deleted). In the example above, this would be the "2", "Sales" row in the tblDepartments table. qry3: Basically all qry1 rows that don't appear in qry2 qry4: All qry3 values, and their respective qry2 value.
This is what each of the (soon to be deleted) duplicate values' related records' DepartmentID should be updated to... There's no simpler way to phrase that, so using the example above, qry4 would return "2","1". This indicates that all people with a DepartmentID of "2" should be changed to "1" (so we can subsequently erase the department with the ID of 2.
This is as far as I have gotten. My next step is: Update all FKs in tblPeople based on qry4 (You can't set an update query's criteria to pull from another query, nor can you use the second query for the update value... or maybe you can, but I don't know about it).
This has been a nightmare all around. Because I can't get 40 licenses for Access I'm stuck doing transferspreadsheets every day, which has worked out mostly, but I'm reaching the absolute limits of my knowledge. I can't even think straight and need help with a solution.
Here is the table data I'm bringing in (in a simple way)
Editor Client ResearchHits HitsSent AltEditor Pruned Edited J. Doe Coca Cola 1000 500 J. Doe Coca Cola J. Schmoe 200 100
This data is brought in from two imports of data (there is actually a date column too that I didn't include). Now, what I need to do is have it subtract the "Pruned" from the "ResearchHis" and the "Edited" from the "HitsSent" while only showing Coca Cola and J. Doe once... is this possible?
I have a report with 2 access tables (1 Master table and another a daily feed table)
The Master table keeps a log of all incoming records. (once append it to this table, should not show in future reporting)
The Daily feed information within the last 48 hours. (uploaded from an excel report into access temporary table)
When the daily feed table gets completed, I append the records and updated them into the Master to avoid duplication.
When I upload the daily feed table and I match it against the Master table to find duplicates, how can I delete the duplicates from the Daily Feed table?
This is my code to find duplicates:
SELECT CMPreport.ID, CMPreport.MbrName, tblMaster.ID FROM CMPreport LEFT JOIN tblMaster ON CMPreport.ID = tblMaster.ID WHERE (((tblMaster.ID) Is Not Null));
I have a query which gets information from 2x tables where the I'd on one table is the reference number on the second table.I would like to know how I can remove the duplicates on my reference number field?
I have two tables, tblCandidate and tblNewHire. I am trying to transfer candidates to tblNewHire based on the date they are hired. However, I would like the database to skip any records that are already in tblNewHire, just in case someone inadvertently puts in the wrong date when running the query.
I created an append query (SQL below) but this enters the data regardless of duplication. I need to be able to check both SSN and LastName against the new table and can't set either one to be unique values as it's possible there could be two candidates with the same last name.
Any way to do this by creating a recordset and looping through to check for dupes.
INSERT INTO tblNewHire ( SSN, FirstName, MiddleName, LastName, Phone, Email, EOD, HiringMechanism ) SELECT tblCandidate.SSN, tblCandidate.FirstName, tblCandidate.MiddleName, tblCandidate.LastName, tblCandidate.Phone, tblCandidate.Email, tblCandidateTracking.ActionDate, tblCandidateTracking.HireMechanism FROM tblCandidate INNER JOIN tblCandidateTracking ON tblCandidate.SSN = tblCandidateTracking.SSN WHERE (((tblCandidateTracking.ActionDate)=[forms]![frmNewHireMain]![txtEODDate]) AND ((tblCandidateTracking.LastAction)="EOD"));
I'm trying to build a query that will normalize some graphical data along the x-axis. To do this I need all the peaks to be at the same point. I'll just talk in generic terms of FieldX and FieldY for the X and Y variables (resp)
In my mind the steps require me to 1) Determine FieldX at Max(FieldY) 2) Determine difference between FieldX and arbitrary normalization point (probably 100k) 3) Shift FieldX at all points to put Max(FieldY) over 100k (its a log scale so I'll be dividing)
Steps 2 and 3 are easy, but step 1 is giving me grief.
Ok, so I know how to find Max(FieldY), easy enough. Now how do I find FieldX? My first thought is a complex series of subqueries. At some point in the past, where I was smarter than I am today, I created a query that would do numerical integration. Looking back at the query I can't understand it, but I think the principle will be similar.
