Queries :: Find Duplicates - Resultant Datasheet Won't Close
Apr 24, 2013
I have a Find Duplicates query that I created with the Query Wizard.
IF I run the query and find duplicates, I can close the resultant datasheet with the close button.
IF I run the query and find duplicates and delete them, I can close the resultant datasheet with the close button.
IF I run the query after I have found and deleted duplicates and closed the window, I can close the resultant datasheet (with no records) with the close button.
BUT IF I run the query when there are no duplicates, it opens the datasheet with no records, but you can't close it with the close button. You have to exit the database completely.
I don't know SQL or VB. Have to do this in Access.
I thought about creating an If statement in a macro, but am not sure of the syntax, but this would be the basic statement I would make:
IF Find Duplicates query returns no records,
THEN Cancel Event.
End IF
View Replies
ADVERTISEMENT
Oct 23, 2014
I have a find duplicates query with the following SQL:
Code:
SELECT tblData.Vendor, tblData.[Loccurramount EUE], tblData.Last4, tblData.ID, tblData.Line, tblData.CoCd, tblData.[Document record number], tblData.PurchDoc, tblData.Reference, tblData.Curr, tblData.[Entry dte], tblData.Status, tblData.Version, tblData.Outcome
FROM tblData
WHERE (((tblData.Vendor) In (SELECT [Vendor] FROM [tblData] As Tmp GROUP BY [Vendor],[Loccurramount EUE],[Last4] HAVING Count(*)>1 And [Loccurramount EUE] = [tblData].[Loccurramount EUE] And [Last4] = [tblData].[Last4])))
ORDER BY tblData.Vendor, tblData.[Loccurramount EUE], tblData.Last4;
This works fine however I want to add another clause to the WHERE and I'm not sure how. At the moment the query highlights duplicates where the Vendor, Loccurramount EUE and Last4 match. I want to further restrict it so that it only finds duplicates where the Vendor, Loccurramount EUE and Last4 match BUT the number shown in version Does Not Match
So if two records have the same details for Vendor, Loccurramount EUE and Last4 and also have the same Version number then they don't show in the result.
View 3 Replies
View Related
May 29, 2014
I used the wizard to create a find duplicates query. I know for a fact that 15 records are duplicates. I'm getting over 300 records. When I dumped the data into Excel and did a pivot table to count the number of records by account number only 15 had a total count of 2. All others only had 1. Why are the ones with no duplicates showing? I just want to see the 15.
View 2 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
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
Mar 4, 2008
Hi!
I used the find duplicates from the query wizard and it is not finding all of the duplicates. How can I fix this?
I have only a few fields
Last name, First name, address, parent name, email.
But when I ran the query the first time it found some.
Then I went into the table and I saw several that it did not find.
I don't get it.
View 5 Replies
View Related
Nov 9, 2006
say i have a table called tblEmployees and i want to find people who have the same name but different employee numbers, can that be done with a query or will that require VBA code? i won't know the names ahead of time, i don't know who has the same name.
for example, columns:
first_name, last_name, employee_num, phone_num, office_num
and i want it to return James Smith with employee_num = 1234 and James Smith with employee_num = 9876.
View 5 Replies
View Related
Jan 29, 2007
Hi everyone,
I have a query as below:
Row1 Row2 Row3 Row 4 Row 5
1, 2, 2 , 0 , 15
1 , 2 , 2 , 1 , 7
0 , 1 , 1 , 4 , 2
1 , 2 , 2 , 0 , 29
I need to pull our records which have the same entries in Rows 1-4 and then add up Row 5. For example The above query would make three more queries i.e
One
1,2,2,0, 44
Two
1,2,2,1, 7
Three
0,1,1,4, 2
I would then wish to recombine them into one table
Final
1,2,2,0, 44
1,2,2,1 , 7
0,1,1,4, 2.
Is this possible and how would I go about doing this!?
Been scratching my head for awhile now!
thanks eveyrone!
Sue
View 2 Replies
View Related
Jun 6, 2007
hi all, im trying to make a query so that when someone updates a field in a form it searches a table (Companytable) which is a list of company names. If it exists i want it to then display a msgbox " already exists" sort of thing! trying to do this in the criteria field on the query. anyone have any ideas or useful resources, ive not done this for a while and cant remember much of IF THEN sort of stuff. mainly not sure if im barking up the right tree, i can search dependent on a set value but not sure how to do it dependant on whats been typed into a form..any pointers would be appreciated! thanks! James
View 2 Replies
View Related
Jun 15, 2007
Hi,
Our Contacts database has around 4000 individual contacts (and growing), which have been pulled together from four or five seperately maintained databases. As such there are bound to be duplicate records.
What would be a good way of querying the database to find duplicate entries (i.e. entries where the first name and surname are the same)? Short of putting everything in alphabetical order and manually scanning them to see if there are two names the same next to each other I don't know of a way to do it. Also, the manually-scanning method won't work if the names are spelled a little differently (Davies and Davis).
Any ideas?
View 1 Replies
View Related
Jan 4, 2008
I know I have duplicate records in a table and therefore I used the "Find duplicates Query Wizard" to identify the duplicates.
I am positive - there are duplicates and the query does not perform the way it should be.
Any suggestion will be appreciated.
TKS
JLA
View 9 Replies
View Related
Aug 15, 2014
I am using the following code to check for duplicate tickets when importing multiple records into a datasheet view form by using the paste append function.
Code:
Private Sub Ticket_Number_BeforeUpdate(Cancel As Integer)
DoCmd.SetWarnings False
If DLookup("Ticket_Number", "Record_Store", "Ticket_Number= '" & Me.Ticket_Number.Value & "'") > 0 Then
Cancel = True
MsgBox "There were import errors, please open View Import Errors above."
End If
End Sub
The form is used to insert multiple records into the database at a single time.
That codes works to check for duplicates. And if there are none there are no popup messages.
If there are duplicates though it gives a popup for every single Ticket_Number that is a duplicate.
I am wondering if there is a way for it to give only a single popup once it completes checking all the records to be imported for duplicates.
View 14 Replies
View Related
Sep 29, 2005
Greetings all,
I have the code below which is giving me grief as I have copied it from a previous usage and it worked fine. I have tried it in a beforeupdate and now as a seperate button on the form and it gives me the same error message; I have tried to put a save command in to save the record without any luck
the code is
If DCount("[Batch]", "tbl_Data_Wine_Batch", "[Batch]=" & Me.Batch) > 0 Then
Cancel = True
End If
The error message is
Run time error 2001
you cancelled the previous operation.
The problem comes up in the dcount expression.
Any help would be great as it is an important part of the project and we can move on once we have overcome this
rbinder
View 1 Replies
View Related
Jul 5, 2005
I've almost completed the DB I'm working on, but still have a few loose ends to tie up that I can't seem to figure out. I've spent many hours already on this forum searching for solutions, and have tried a few different things, but I still can't work out these bugs.
First, I need my form to open with the latest records filtered first, so that the most recently added records will be easiest to find and edit. I'm not sure how exactly to do this. I've tried a run query when the form is opened, but I still need all the records displayed or accessible. The form is called frmDenial, and I would like it to be looking in the DateLogged field of the form. Any suggestions?
Second, I have command buttons that will export data in the fields to MSWord Templates via bookmarks. The button runs well by opening the document, inserting the data, and printing. I have also tried several different code syntax to close word once it is done, but it is still staying open after printing. Here is a sample of the code:
________________________________________
Private Sub Print_Letter_Click()
Dim objWord As Word.Application
'Start Microsoft Word 2000.
Set objWord = CreateObject("Word.Application")
With objWord
'Make the application visible.
.Visible = False
'Open the document.
.Documents.Open ("G:PharmacyPrior Auth Docs and DataRevised Pharmacy Denial ProcessesKAN Not Nec or Benefit2.dot")
'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("bmkFirstName").Select
.Selection.Text = (CStr(Forms!frmDenial!MBRFirst))
.ActiveDocument.Bookmarks("bmkLastName").Select
.Selection.Text = (CStr(Forms!frmDenial!MBRLast))
.ActiveDocument.Bookmarks("bmkHRN").Select
.Selection.Text = (CStr(Forms!frmDenial!MemberNumber))
.ActiveDocument.Bookmarks("bmkAddress1").Select
.Selection.Text = (CStr(Forms!frmDenial!MBRAddress1))
End With
Print_Letter_Click_Err:
'If a field on the form is empty, remove the bookmark text, and
'continue.
If Err.Number = 94 Then
objWord.Selection.Text = ""
Resume Next
End If
objWord.Application.Options.PrintBackground = False
objWord.Application.ActiveDocument.PrintOut
objWord.Application.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
'Quit Microsoft Word and release the object variable.
objWord.Quit
Set objWord = Nothing
Exit Sub
End Sub
_______________________________________
Finally, I have two cascading combo boxes set up that will auto pop related fields based on the selection made. But, if an item is not in the list, I would like the user to add it to the linked table to appear in the list. I also have this working well, with a pop up asking the user if they want to make the addition, type in the new item, and add to the list without requiring the user to refresh or exit then re-enter the form. But it will only add the item name, and not the item description (another field in the form and another column in the table). How can I modify the code to prompt the user to enter these other details? I can link it to a pop up sub form to enter the data, but if possible, would rather the boxes pop up to have the user type in the data. Here is the code I have so far in the NotInList Event...
__________________________________________
Private Sub DrugName_NotInList(NewData As String, Response As Integer)
Dim DB As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not an available Drug" & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add the new Drug to the current Database?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new model?") = vbNo Then
Response = acDataErrContinue
Else
Set DB = CurrentDb
Set rs = DB.OpenRecordset("tblDrug", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!Drug = NewData
rs.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
rs.Close
Set rs = Nothing
Set DB = Nothing
End If
End Sub
______________________________________________
The two other colums in the tblDrug that I need the user to be prompted to fill are Denial Reason (column 3) and Alternative (column 4). What would be the best way to accomplish this?
I would really appreciate any help or suggestions with any of these problems. Thank you so much! :o
View 7 Replies
View Related
Jun 5, 2007
Hello all,
I am sure this must be simple, but I can't get my head around it.
I have a query that looks at client bank account numbers, and finds duplicates.
There are a LOT of duplicates, because we have several accounts for the same person.
What I would like to do is find all the records where the account number and sort code match, but where the account NAME is different.
I am trying to find any records where the same account number is being used on more than one account.
All the fields are in a single table.
This is what I have so far, which just finds duplicate sort codes and account numbers;
SELECT CPL_ACCOUNT.ACCOUNT_NUMBER, CPL_ACCOUNT.SORT_CODE, CPL_ACCOUNT.BANK_NAME
FROM CPL_ACCOUNT
WHERE (((CPL_ACCOUNT.ACCOUNT_NUMBER) In (SELECT [ACCOUNT_NUMBER] FROM [CPL_ACCOUNT] As Tmp GROUP BY [ACCOUNT_NUMBER],[SORT_CODE] HAVING Count(*)>1 And [SORT_CODE] = [CPL_ACCOUNT].[SORT_CODE])))
ORDER BY CPL_ACCOUNT.ACCOUNT_NUMBER, CPL_ACCOUNT.SORT_CODE;
Thank you!
View 1 Replies
View Related
Aug 15, 2013
Access 2007 - I am using the oft used, frequently posted and shard code from srfreemen - modified as below. now I know I sort of broke commandment # 8 - Thou shalt not copy and paste other people's code without at least attempting to understand what it does. but I honestly did try to understand what it does.
I keep getting runtime error 3078 - cannot find the input table or query 'tblEnrolment_Committee_Master'. It is the name of a table in my database but I am missing the very basic element of how to get my form (which draws all of it's fields from that table) to look it up or identify it.
Likely because of a silly basic error. Normally - I wouldn't attempt such things but my work needs me to ensure no duplicates exist in this case AND it's already a primary key - but I don't want to wait until the whole form is filled out for it to identify the duplicate!
How to get the tblEnrolment_Committee_Master to be included in the search and erase this error ...
Private Sub Entitlement_File_Number_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
[Code] .....
View 3 Replies
View Related
Oct 4, 2014
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?
View 14 Replies
View Related
Apr 24, 2013
Employees submit information into a form which translates the information into a table. The table has been in use for years. By accident some employees were writing to an archived table while others were writing the the active table. This resulted in a field called "WorkID" being duplicated across the two tables.
Bottom line I am trying to write a query finding duplicates across tables but basing the duplicate only on certain columns.
View 5 Replies
View Related
May 1, 2008
I get a very strange error when trying to create a find duplicates query. When i try to select a table that has duplicate values it brings up a message box and it says that i must pick a table that has at least one field that's not a memo or OLE data type. I look at my table and I have niether of those in it...pretty much just text and number fields. Does anyone know what's going on?
View 3 Replies
View Related
Nov 9, 2013
I'm using a split form with the datasheet on the left.
When I use the 'Find' in the Navigation buttons at the bottom of the form, it refuse to find text in a particular text box.
The text box is a memo field and is enabled.
Example:
I have text in the memo field which is a title of an episode - "The Outcast."
When I start typing "the" it finds this text in the memo field. But when I type the entire title it the finds the text in another field. ie "out."
I've tried the 3 different Search setting in options. When I use the Find command from the ribbon it work ok as long as I select the text box to search in first.
View 1 Replies
View Related
Oct 25, 2006
Hi,
I want to have some queries run during the database is closed, like the functiona with "Repair on Close". But I don't want to use a form with a VBA code doing it. Is there any other possibility to do it? :confused:
Thanks in advance for answers.:)
View 14 Replies
View Related
Aug 1, 2014
I have been working on a simple data base for some time now (beginner level) and am still trying to improve it. I would like to do something but before that I would like to have your opinion to know if it is even possible?I have a query QryMainReport:
Start Date/Time
End Date/Time
Employee
At the moment this is what the format of my report looks like (I removed other unnecessary fields):
StartTime----------EndTime---------------Employee
12/06/2014 01:00--12/06/2014 03:00------John Smith
12/06/2014 04:00--12/06/2014 06:00------Jane Doe
13/06/2014 02:00--13/06/2014 05:00------John Smith
13/06/2014 08:00--13/06/2014 08:00------Jane Doe
I would like to do as a report. (Dates would always be from Sunday to Saturday). I am not sure it is possible to do that. I suppose first it would mean:I would have to do a query to separate the times from the dates?I would have to find a way for Access to find the unique dates and unique names?Does it mean I have to use cross tab queries?
View 2 Replies
View Related
Jun 5, 2013
I have a form with a child that holds a query and for 99.9% of the time remains locked as it's for viewing data only. However, if the user wishes to make a change then they need to press an unlock button first then make the changes.
The problem I have is when closing the form; if this child's query has been unlocked I'm getting a message asking do I wish to save the changes to the layout of the child's query. The answer will always be "No".
I've added the following line of code to all sorts of procedures with no success.
DoCmd.Close acQuery, "LinkTableManager_Frmqry", acSaveNo
how to stop the message appearing when the form is closed.
View 5 Replies
View Related
Feb 10, 2014
I need to delete duplicate rows (or create new table without them) but the duplicate is not the entire row of data.
Date Time LeagueMatchingId League HomeMatchingId HomeName AwayMatchingId AwayName
HomeScore AwayScore HomeRedCardCount AwayRedCardCount FTOU FTOverOdds FTUnderOdds
20121202 10/02/2014 20:34:02 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.88 -0.98
20121202 10/02/2014 20:34:02 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.88 -0.98
[Code] .....
In this example I am only interested in the first and last rows, since the ones in between have the same data in the last few columns. They are not true duplicates since the time stamp for each is different.
Currently thinking I need to compare each and every row to the prior row in VBA and delete if criteria match.
View 4 Replies
View Related
Apr 2, 2014
I need to create a query that will pull duplicate names out of my db.
I would like it to pull all names that have:
duplicate LastName and duplicates of the first 3 letters of the FirstName.
For example, if I had the names:
Bland, Abe
Brown, Abe
Brown, Bill
Buster, Jon
Buster, Jonathon
I would like my query to return only Buster, Jon and Buster, Jonathon.
View 4 Replies
View Related
