I currentlyt have a search form that searches criteria based on a textbox. After entering data a macro runs, and opens up a query with the results. After that query opens i have macro that runs a duplicate query off of the query initally opened based on the search results. What i would like to have is a macro that removes the duplicate records from the duplicate query and display the remaining records.
View RepliesOk well I thought I was doing this right but it appears I have not. I am drawing data from two tables. Both of them related by the person's name. One table I am pulling the unique ID number, the other table contains the name. I have a PO (Purchase Order) field being pulled from the table that holds unique ID. Or lack there of, so I am pulling all the records that contain no PO number. However duplicate unique ID's are being pulled. The way my query is set up now is:
Unique ID Number
Name
Count (Number of Procedures)
Charges
PO Number - Criteria: "Is Null"
I have also right click in the table view area, in the properties I selected Unique values. I have to assume that this would have held back any Dupes from the output. However this is not the case. When I looked in the help files the only thing I could find was:
http://office.microsoft.com/en-us/a...0840151033.aspx
Quote: "If you want to see only unique values in a result set, you can specify that you want to exclude duplicates from the result set.
In the Database window, click Queries under Objects, click the query you want to open, and then click Design on the database window toolbar.
Right-click the background of the Diagram pane, then choose Properties from the shortcut menu.
Select DISTINCT values.
The Query Designer inserts the keyword DISTINCT in front of the list of display columns in the SQL statement.
Note If you use the DISTINCT keyword in Microsoft SQL Server, you cannot modify the data in datasheet view."
The problem with this is my properties box does not offer a Distinct Values option. So my question is. Is there an expression I can place in the Unique ID field that will exclude any duplicate records.
Sorry for the book
Sean
I'm having a problem removing duplicate records from a table. It should be easy but I can't suss it.This is an example of the data in my table:
Code:
RefDateStatus
113007111/06/2015Do Not Pay
114454306/07/2015Do Not Pay
115760714/05/2015Do Not Pay
116520705/05/2015Do Not Pay
117670108/05/2015Do Not Pay
118036218/05/2015Do Not Pay
118517015/05/2015Do Not Pay
178734020/07/2015Do Not Pay
182809915/07/2015Do Not Pay
184226010/07/2015Payment Due
184226022/07/2015Payment Query
As you can see, there are 11 records here but the last two records have the same reference number. I need my query to show the first 9 records + the record from the bottom two with the latest date (22/07/2015).
Im trying to write a query that shows all the container movements. Yet when I run the query qryFullHistory I get a duplicate value for container Off Island. Ive tried adding some criteria that says that the DateRequested has to be between the ImportDate and ExportDate but that doesnt seem to work. There are duplicate entries for container Off Island in tblContainerDetails as the same container has arrived and left and then returned on another voyage. Yet there is no entry for the second voyage in the tblMEMRContainer.
A brief description of the tables is:
tblMEMR Movement requests details
tblMEMRContainers the containers that were moved on the movement request. There can be more than 1 container for each request.
tblContainerDetails details and dates for the container when it arrived and when it left
There are other tables but these are the 3 that are used in the query.
I have a table with at least 13.000.000 records. There are many duplicates records... For example
ID Name Family mobile car number chassis Register_Year
1 Roy Jalbout 9999 123456/G ASF4546 2005
2 Roy Jalbout 9999 854658/G GRK554JFJD 2009
3 Tony Elishah 1234 854658/G GRK554JFJD 2012
4 Sam Markos 5478 854658/G GRK554JFJD 2014
5 Roy Jalbout 9999 123456/G ASF4546 2005
As you can see ID Number 2,3,4 have the same car but every one bought from another so it's not duplicates
The duplicates here is the ID's number 1,5. So how to remove the duplicates. I remember you i have at least 13.000.000 records. I try to make a query to find duplicates then i make a copy of the original table and than i should make a primary key then append the data from the original table to the copy table, but here i have more than one criteria
When I made a query to find duplicates the result was 680.000 records and every one have a minimum 2 duplicates an maximum 4 duplicates so it's about 2.500.000 duplicates records at least....
I have a database with an import process which normalises incoming data and appends to various tables. No issues with that. I also have a function within that process which counts the number of new entries for a summary popup when the process has completed.This works by querying the staging table, prior to the append, into a recordset and using the .RecordCount to increment the count (multiple files can be imported at once so this effectively provides a running count, per file, to give a total for the whole import)
I thought it was working fine but this morning I noticed that the count which appeared on the popup was 1 greater than the number of actual new records. I checked the source files and noticed that, for whatever reason, there was a duplicate entry in there. So I presume that's why the count was out by 1.
There's no integrity issue in the main tables as the composite primary keys ensure that duplication shouldn't be a problem. Indeed, the record in question, duplicated in the source, appears only once in the main table post-import. So not too worried about that.
However, I need the count in the popup to be accurate (it tells the users how many new entries require further investugation). And what's puzzling me is that I use DISTINCT in the query, which I would have thought should eliminate any potential dupes in the recordset and thus provide the correct count. It seems it doesn't?
Code:
Public lngNewBalancesTBI As Long ' Defined in a separate module...
-------
Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String
[code]....
Why the dupe, which is still present in the staging table, also makes it over to the recordset, even though I'm using DISTINCT?
I have a table that has mutliple records for the same person. I know how to create a query to remove duplicates but i don't know how to create a table to remove duplicates but keep the most recent record. Is this possible?
ie
client status date
123 A 1/1/07
123 C 2/5/06
123 A 9/3/07
results
client status date
123 A 9/3/07
Hello all,
I have made a crosstab query that is sporatically making duplicate counts. I'd like to know how to fix the problem, but more importantly - I'd like to know why and how my query is giving me these results so I can avoid making this same mistake again.
The relationship is set up so that all records from one table are returned, with only matching records from the other - so no problems that way as far as I can see. This is my crosstab query that is giving some counts of the same record as being matched sometimes two, three, or even four times:
TRANSFORM
Count([Testing DB].[DB_ID]) AS [CountOfDB_ID]
SELECT
[Testing DB].State, [Location].[North], Count([Testing DB].[DB_ID])
AS [Total Of DB_ID]
FROM [Testing DB] LEFT JOIN [Location] ON [Testing DB].[Address] = [Location].Address
WHERE (([Testing DB].[Window Length]) Is Not Null))
GROUP BY [Testing DB].State, [Location].[Size]
ORDER BY [Testing DB].State, [Location].[Size]
PIVOT Format([Date],"mmm-yyyy");
When I do a simple query for a list of the records being counted by this query, I can see the duplicate entries and can eliminate them simply by adding "Distinct" to the select statement - but I haven't had any luck adding any kind of distinction with this crosstab that will do the same.
How can I eliminate duplicate counts of records in my crosstab? If anyone has a suggestion, it would be much appreciated.
hi
i have a form with various fields, on this form i have an archive button that places certain fields in an archive form. however this does not remove the information from the main form. pls see attached
any ideas, as some will know i'm a newbie
I have:
Database A with fax numbers:
Example:
- 1. John. W
- 2. Mark. Y
- 3. Sean .S
I exported to a dbf file to do some mass faxing and realised that Mark's fax number is no long in use. (error report by the fax software)
I can export the error report and save as dbf.
Is there a way to make use of this dbf to update and remove Mark's record from Database A?
I have:
Database A with fax numbers:
Example:
- 1. John. W
- 2. Mark. Y
- 3. Sean .S
I exported to a dbf file to do some mass faxing and realised that Mark's fax number is no long in use. (error report by the fax software)
I can export the error report and save as dbf.
Is there a way to make use of this dbf to update and remove Mark's record from Database A?
I have a dataset of about 70 columns but would like to remove any two rows that sum to zero (based on two columns) if the column "Units" are the same for the same "Bar code" thus to say "for the same 'Units' (for any two lines), check to see if their respective 'Bar Codes’ are the same and if so remove if their amounts sums to zero".
I have the following code which seems to be working but for relatively large data ( about 5k), it does not select all the data that meets the criteria:
Code:
SELECT *
FROM datatable
WHERE ((([datatable].[Unit] & [datatable].[Bar Code] & Abs([datatable].[Amount]))
In (SELECT datatable.Unit&datatable.[Bar Code]&abs(datatable.Amount)
FROM datatable
GROUP BY datatable.Unit&datatable.[Bar Code]&abs(datatable.Amount)
HAVING count(*)>= 2 and sum(datatable.Amount)=0)))
ORDER BY [datatable].Unit & [datatable].[Bar Code] & Abs([datatable].Amount);
identifying the bug in this code that makes it not pull all the data meeting the criteria.
See sample data below:
# Unit bar Code Amount
1. AAB Mac1 2.75
2. AAB Mac1 -2.75
3. AAB Mac1 1.24
4. AAC Mac3 35.00
5. AAC Mac3 -20.00
6. AAC Mac3 20.00
7. AAD Mac3 16.11
8. AAC Mac2 11.00
9. AAC Mac2 -11.00
10 AAC Mac3 12.05
11 AAF Mac3 -12.05
12 AAD Mac3 -16.11
13 AAC Mac4 35.00
I want to select records # 1, 2, 5, 6,7,8,9 and 12 since they are same units having the same bar code and their sums total zero.
I'm creating a job/timesheet database and have a problem...
I have a main timesheets table that includes every job/piece of work thats been done (sometimes multiples for each employee each day). Each record contains a ref to the employee, ref to the contract, ref to the type of work, time taken and date.
There are then various tables that support the main table, which provide actual employee names etc.
I want to produce a query that provides the number of hours undertaken on each day of a particular week for each employee. So I'd have one row for each employee from the employees table, then a field for the sum hours for Monday through Friday.
The way I was going about this was
- to create five query's for the main timesheets table that would limit the entries to the five days in question - Monday-Friday. That worked fine.
- to then create a query that takes the employee name and using each of the five day based queries perform a Sum Total on the time field to give me the total hours worked for each day for each employee. That also worked fine, but the trouble is it removes any null values. So, if I only include the first day there might be 80 employees listed, but when I include the second day as well that goes down to 72 - presumably as 8 employees who entered timesheets for day 1 didn't for day 2. By the time I add all five days, I have almost no employees. I assumed that there would be a query level property to set, but i can't see one.
Also, is there a simpler way to do what I want? If I can get this working I'd like to replicate to breakdown by contract, job type etc in the same way as I have with employees.
i have a form porblem. I have a search form embedded onto my main form its really a listbox that when i click the records in it they display the record.
prob;em is that when i delete the record it still shows in the listbox
In my simple database (attached), I need to mass duplicate Tasks and their Notes.
I have three tables: tbTasks (PK: Task_ID), tbNotes (PK: Note_ID), jtbTaskNotes (FKs: Task_ID and Note_ID). jtbTaskNotes is my many-to-many junction table that ties Tasks to Notes.
The main form (fmTasks), bound to tbTasks, has a subform (sbfm_TaskNotes) that displays notes associated with each Task. On themain form,you select which Tasks you want duplicated via a checkbox. The append query (quCopyTasks) will duplicate all tasks that have the checkbox checked. All good there. However, I can't figure out how to also duplicate each task's Notes.
I found Allen Browne's solution [URL] ....., but that only handles duplication of one record at a time, whereas I need to duplicate many records at a time (sometimes 10+ records). How do I go about duplicating multiple Tasks and their associated Notes?
Before you ask "why are you duplicating records?": There are times when tasks need to be re-accomplished and therefore need to have a new record. It's easier to duplicate records than it is to hand-jam everything again.
I have built a qry that initially shows the correct information. For example.
tblContent has 289 records with a Type = Class.
I built a Query to select from tblContent Type = Class and I get 289 records. I add additional criteria of Progress <>"Not Scheduled", I then get 206 records. I then add additional criteria Last Name <>"Demo" And <>"Care" And <>"Support". This brings up 200 records, but the query appears to duplicate each record 3 times. I do not have 3 of the same types of records.
The SQL Statement is below
SELECT tblProfile.LoginName, tblProfile.FirstName, tblProfile.LastName, tblProfile.Organization, tblProfile.CostCenter, tblContent.Title, tblContent.Type, tblContent.Code, tblContent.[Date Assigned], tblContent.[Date Started], tblContent.[Last Accessed], tblContent.Progress, tblContent.[Date Completed]
FROM tblProfile INNER JOIN tblContent ON tblProfile.LoginName = tblContent.LoginName
WHERE (((tblProfile.LastName)<>"Demo" And (tblProfile.LastName)<>"Care" And (tblProfile.LastName)<>"Support") AND ((tblContent.Type)="Class") AND ((tblContent.Progress)<>"Not Scheduled"));
The qry is named qryPhysical Class. I have provided the link to view the database. Can you help me?
http://briefcase.yahoo.com/turnerbkgabrobins
Thank you in advance for your assistance.
I want to find duplicate records based on FirstName and LastName and delete the duplicate. Also, I want to delete any records which have a blank FirstName and LastName.
How can I do this?
Thanks,
Dave
pls can someone point me in the right direction
have searched through the forums.
i have two fields Forename & surename i need to prevent duplicate records if the forename and surename match another record.
Hi
I have a table (which is populated by people filling in a form) which contains two fields:
Section
WeekCom (e.g. week commencing)
I want to make sure that the form will not allow someone to make a double entry. For example, if Sally goes in and says enters "Benefits" as the section and "WeekCom" as 15/10/06, and then Ben tries to do the same - the form will not allow Ben to enter it.
How do you do this please? The table does not have a primary key at the moment....is there any way of doing it without setting one of the fields as a primary key?
Thanks
Maria
I Need to run a query that will not show duplicate records.
For example if joe smith is listed five times i only want the most recent date of membership.
Does anyone have any ideas?
I have a form in Access which has event date and then also an event time slot such as "Morning, Afternoon, Evening" . I need to make it so that you cannot have a duplicate time booking, i assume i will need to code some VBA but if anyone could tell me what it would be much appreciated. Thanks
View 2 Replies View Relatedi m new user to access i just wanna know how can i count duplicate recodes for example how many time
record repeat in one column
waiting for ur quick reply thanks
Hello,
I have a table that has many duplicate records. I tried importing the table and setting the field to no duplicates but did not work. How can I delete duplicate records. The primary Id id fine I have another field that I can use. it is a BAC number that is repeated many times. Here is the table I need fixed.
Thanks
Hi Guys,
I hope someone can help with this. I have a table, "Blasthole Submission" which is populated by input in a form, using the code below:
Const MyTable As String = "Blasthole Submission"
Const MyField As String = "Sample Name"
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intCounter As Double
Set db = CurrentDb
Set rs = db.OpenRecordset(MyTable)
For intCounter = Me.txtStartValue To Me.txtEndValue
rs.AddNew
rs.Fields(MyField) = "TP" & intCounter
rs.Fields("Submission #") = Me.SubNum
rs.Fields("Sample Type") = "Blasthole"
rs.Fields("XRF") = "True"
rs.Fields("LOI") = "True"
rs.Update
Next intCounter
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
What I am hoping to do is to place a random duplicate in the table, called, for example TP111152 DUP, approximately every 50th record. Is there any easy way of doing this?
Thanks in advance for your help!
i am using a textbox in the main form to transfer the Yr/Mth into the table. (refer to qp.zip)
now my problem is, how do i prevent duplicate records into the table?
e.g: currently whenever i scroll ard the months control in the form, i will get duplicate Mth/Yr into the table. how do i prevent that from happening?
i only want to have updated data inside the Mth/Yr table instead of duplicated Mth/Yr
Public Sub PutInMonthlyRecords()
Dim sql As String
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim f As Form
Dim MthYr As String
Set f = Forms!frmQpi
MthYr = f("txtMthYr")
sql = "SELECT * FROM [tblQpiMonthly] WHERE (([tblQpiMonthly].[Input MthYr] = 'MthYr' ));"
Set Db = CurrentDb()
Set rs = Db.OpenRecordset(sql)
If rs.RecordCount = 0 Then
If (f!txtTotalPF) = 0 Then
Exit Sub
End If
rs.AddNew
rs![Input MthYr] = f("txtMthYr")
rs![Monthly TotalPF] = f("txtTotalPF")
rs![Monthly Rejection] = f("txtTotalAvoid")
rs![Monthly TotalAvoid] = f("txtRejection")
rs.Update
Else
If (f!txtTotalPF) = 0 Then
rs.Delete
Exit Sub
End If
rs.Delete
rs.AddNew
rs![Input MthYr] = f("txtMthYr")
rs![Monthly TotalPF] = f("txtTotalPF")
rs![Monthly Rejection] = f("txtTotalAvoid")
rs![Monthly TotalAvoid] = f("txtRejection")
rs.Update
End If
Db.Close
End Sub
Be advised that I'm a complete novice with Access, and have used it only to view Excel files with more than 64,000 records. Any help would be appreciated.
I have an Excel file of 10,000 records entered by salesmen of possible prospects, with salesmen, prospect name, address fields. Some salespeople have entered the same prospect and address information; the only thing different in these records are the salesman name.
I know how to import the file into Access. But from this point, how do I construct a query and generate a report that shows me ONLY THE DUPLICATE RECORDS? We would like to use this report to identify the double entries and the salespeople so they can clean up the database. I know Access (I have Access 2003) can create this query, I don't have a clue on how to do it.
One hint: A post on another forum suggested " Make a group-by query (grouped on all fields except salesperson). Add a count field. Select if count >1. This should do the trick."
I'm sure this is the answer, but I don't know how to "make a group-by query", how to "add a field", etc. Like I said, I know how to import the data. That's it.
This is extremely frustrating to be this close to the answer and not know how to finish it. Any assistance would be appreciated. Thank you.