Remove Duplicates From Query/form
Nov 10, 2005
Hi,
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...
Can anyone help!?
cheers
amx
View Replies
ADVERTISEMENT
May 14, 2015
I have a query that displays this
Column1 Column 2 Column 3 Column 4
TripTitleA TravelerA N/A N/A
TripTItleB TravelerB N/A N/A
TripTitleC TravelerC TravelerD TravelerE
TripTitleC TravelerD TravelerC TravelerE
TripTItleC TravelerE TravelerC TravelerD
TripTitleD TravelerF N/A N/A
I want to filter Column1 to have no duplicates so it looks like this
Column1 Column 2 Column 3 Column 4
TripTitleA TravelerA N/A N/A
TripTItleB TravelerB N/A N/A
TripTitleC TravelerC TravelerD TravelerE
TripTitleD TravelerF N/A N/A
Is this possible?
View 1 Replies
View Related
Apr 12, 2007
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.
So what is the best way to handle this?
I appreciate the help.
Jim
View 1 Replies
View Related
Jun 14, 2007
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!
View 1 Replies
View Related
Sep 3, 2004
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;
View 1 Replies
View Related
Mar 27, 2006
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.
List before dedupe:eek:
IDTag..................Surname......First......... ......EditDate
37659362.............Adams.........Mary........... ...2005.10.21
57506..................Adams.........Mary......... .....2005.04.25
37661858.............Adams.........Nichole........ ...2005.06.23
37661857.............Adams.........Nichole........ ...2005.06.23
37661795.............Adams.........Nichole........ ...2005.06.17
51176..................Adams.........Nichole...... .....2005.03.16
37657399.............Adams.........Rudo........... ...2006.02.15
54640..................Adams.........Rudo......... .....2005.02.08
List after dedupe:D
IDTag..................Surname......First......... ......EditDate
37659362.............Adams.........Mary........... ...2005.10.21
37661858.............Adams.........Nichole........ ...2005.06.23
37657399.............Adams.........Rudo........... ...2006.02.15
Any suggestions?
Newb
View 4 Replies
View Related
Dec 31, 2013
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?
SELECT Q_PI_Qrtly_Insp_Ct_Range.District, Q_PI_Qrtly_Insp_Ct_Range.Bldg_Number,
Q_PI_Qrtly_Insp_Ct_Range.Bldg_Name, Q_PI_Qrtly_Insp_Ct_Range.Address, Q_PI_Qrtly_Insp_Ct_Range.City,
Q_PI_Qrtly_Insp_Ct_Range.State, Q_PI_Qrtly_Insp_Ct_Range.FSL, Count(Q_PI_Qrtly_Insp_Ct_Range.Insp_Date) AS CountOfInsp_Date, Q_PI_Qrtly_Insp_Ct_Range.Due, Lookup_Tbl_Commander_Inspector.Commander
[code]...
View 1 Replies
View Related
Jun 6, 2005
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????
View 1 Replies
View Related
Jul 31, 2007
Hi everyone,
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...
Thanks!
View 2 Replies
View Related
Feb 27, 2014
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.
ID Code
2699974 BCC
2699974 SRAC
2700023 LRAC
2700023 LRAC
View 1 Replies
View Related
Jun 11, 2013
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 attached a screenshot of the table
View 3 Replies
View Related
Nov 19, 2013
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.
View 14 Replies
View Related
Sep 28, 2015
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?
View 9 Replies
View Related
Mar 18, 2014
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).
View 3 Replies
View Related
Jun 29, 2014
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));
View 9 Replies
View Related
Nov 4, 2013
I have a simple spreadsheet:
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?
View 7 Replies
View Related
Aug 28, 2013
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?
View 3 Replies
View Related
Dec 18, 2013
I have some data in an array that I need to normalize, remove duplicates, and import.
Original Table
Every record in the array has a person, all but a few have an address, most have a phone, and some have an email.
Person----- Address ----- Phone ----- Email
Tom ----- 10 A Ln ----- 789... ----- e@a
Sue ----- 20 B Ln ----- 256... ----- _____
Sam ----- 30 C Ln ----- _____ ----- _____
Dan ----- 40 D Ln ----- 478... ----- _____
Jan ----- 40 D Ln ----- 567... ----- e@d
Stu ----- 50 E Ln ----- _____ ----- _____
Syd ----- ______ ------ 224... ----- _____
New Data Structure
I want to group the data by HouseHold; which Address will serve to define for this import.
tblHouseHold
hhID
tblAddress
adrID, hhID, Address
tblPerson
prsID, hhID, Person
tblPhone
phnID, hhID, Phone
tblEmail
emlID, hhID, Email
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
[Code] .....
View 2 Replies
View Related
Apr 9, 2013
I have a form that is pulling data from a previous days transactions but most (not all) transactions are duplicated.
I created this form a couple of years ago and have not modified it for about 3 months and nobody else has made any changes. When I look at the query that the form is pulling nothing is duplicated. When the Recordset type is Dynaset (Inconsistent Updates) I get 94 records, when I change it to Dynaset, then I get the correct count of records of 80.
View 4 Replies
View Related
Jul 10, 2013
I have tbl_Proj, tbl_Notes, and tbl_Proj_City. They are all joined by the tbl_Proj's ID.I have a set list in tbl_City - tbl_Proj_City is 3 columns an ID for itself, tbl_City ID and tbl_Proj ID...In instances where a project covers more than one city, when I generate a report (or query) for that project I get each notes that number of times. If a project is in 3 cities I get each note 3 times.
I have a project entry form with a combobox subform for the city selection.The report is from a qry, by Proj_ID, I need to show the cities - the "key" city is the first alphabetically (also lowest via autonumber in City_ID). I would like to add that I do not know SQL. I have created this database using access commands.
I am thinking that I may be able to query the project ID for the Cities and somehow select the first alphabetically or the lowest in ID and store that somewhere? and then run a query that uses that and the notes to generate the report and then have a sub report for the other cities?
View 6 Replies
View Related
Nov 13, 2014
i removed the DIsTINCT in my query to move some field to be updateable on the form. Once I did that my detail section of my form was empty ..why and how do I fix this problem.
View 1 Replies
View Related
Feb 12, 2008
Hi this is my first post... so hi all :)
ok what i have is a table with contact details 900k plus
there are about 90k of which are duplicates.
this is the basic feilds that are important in this case.
Id, data_source, data_recived, data_code,
what i want is to have a table with unique records (no dups in data_code)
this table will look like this...
Id, data_code, Num_dups, dup1_source, dup1_date, daysbtw_Dup1_dup2, dup2_source, dup2_date, daysbtw_Dup2_dup3 ,dup3_source, dup3_date, daysbtw_Dup3_dup4 ,dup4_source, dup4_date,
I know there is no more than 4 dups of each record.
what i want from this is a table that will give me a record of how many dups for each record then all the dates that they were added and the date between each record entry.
if anyone can help it would be great .
thanks in advance.
View 6 Replies
View Related
Dec 8, 2005
Hey, all! Thanks for helping, here is my situation.
I have a table with about 70,000 records that have duplicate Address field values. The rest of the field values for those records are different. When I do a find duplicate querry I get the result that 17,000 records have the same address. However, when I do the append qurrey as instructed here: http://support.microsoft.com/?kbid=209183 I get a total of only 600 records in the new table. I have tried deleting all of the indexes for both the new and old table, with no luck.
I'm using Access 2000 on XP Pro.
If anyone could help with this I would greatly appreciate it!
Thanks
Will
View 14 Replies
View Related
Aug 23, 2005
I have a table in a healthcare patient database that stores Admitting Number, Scan Number, Last Name, and some other fields detailing the scan. Each patient can have multiple scans and I'm trying to design a query that lists how many scans each patient has had by finding all the duplicate admitting numbers. I'm using this SQL statement:
SELECT MultScans.Admitting_Number, Count(MultScans.Admitting_Number) AS Num_Of_Scans
FROM MultScans
GROUP BY MultScans.Admitting_Number
HAVING (((Count(MultScans.Admitting_Number))>1));
which works fine but I can't figure out how to also print out the last name, scan number and the other fields in this query. If I add the field to the select part it says I can't because the expression is not part of the aggregate function and if I add the expression to the 'group by' the query runs but displays all the duplicates. Thanks for any help.
View 3 Replies
View Related
Apr 4, 2006
Please help. I am having difficulty understanding why a subform is showing duplicates despite using SELECT DISTINCT for its query. I have looked all over the forum but am still quite confused.
I think my database is normalised but this may be the problem and can attach it if necessary.
My query is as follows
SELECT DISTINCT StudentAndCourse.ClassID, StudentAndCourse.LevelID, StudentAndCourse.StudentID
FROM [Level] INNER JOIN (Class INNER JOIN StudentAndCourse ON Class.ClassID=StudentAndCourse.ClassID) ON Level.LevelID=StudentAndCourse.LevelID;
Any help would be most appreciated as I cannot understand what is going wrong.
View 1 Replies
View Related
May 1, 2007
Hello,
I am just wondering whether there is any way I could run a simple query that would pull all the data I need and inform me of the duplicates if there is any.
Thanks!
View 2 Replies
View Related
