Deleting Duplicate Values Based On 2 Fields
Hi guys,
I have a question I've been trying to figure out with no luck. I have 2 fields, companyname and zip, these 2 fields may have duplicate values or they may be uniquei.e.
companyname zip
billybobs 68135
dilberts 68137
billybobs 68135
what I need to do is delete all of the duplicate records where both companyname and zip match. Is there a way I can do that with a query?
Any help is greatly appreciated
Thanks
James
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Deleting Duplicate Values
I have a table which has duplicate values and i need to delete them, how do i do that?the table fields are field1 field2 field3 Field4 1 a aa bb 1 a aa bb 1 b bc ab 2 a aa bb 2 a aa bb I want to delete the ones which are bb in Field 4 and keep only bb value, how do i do this? Thanks
View Replies !
View Related
Adding Fields Based On Values In Other Fields
Hi, I am creating a small database to house results of certain tests. Is there a function in access that allows me to add fields if a result is out of the specification required. In other words I still need to record the out of spec results but I also need more fields to appear for the next lot of results to be entered, for example. When cooking a batch of product I need to test the pH at the 30 minute mark if the pH is too low I will continue cooking the product for another 30 minutes and then test the product again. The cook time is not a constant so I never know how many samples are required, therefore I don't know how many fields i would need in advanced.
View Replies !
View Related
Automatic Values Based On Other Fields
I've put together a database for my company to track our archived files. When the files are archived, there are three important values pertaining to their location: a list number, a box number, and a barcode number. Each box has a barcode on it, so what I'd like to do is make it so that when any record has both a list and box number entered (since it is dependent on the list number to know which, for example, "Box 1" I'm entering information for), it would automatically insert the appropriate barcode number in the field. It would obviously need a seed for each barcode number to be entered before it would know which one to use, but that could either be based on the first unique entry, or entered another way. If anyone can help me do this, I'd really appreciate it. Also, if my description was insufficient or confusing, just let me know and I'll try to clear it up. Thanks! Example: If I entered: List # Box # Barcode # 77 4 It might not know what the barcode is the first time. But once I had entered List # Box # Barcode # 77 4 366582 It would know what I was entering. Keeping in mind that I'm not showing the rest of the record, but just this end part, my next entry would look like: List # Box # Barcode # 77 4 366582 77 4 366582 ...the red representing that it would automatically fill that in, because it knows that if I'm talking about Box 4 in List 77, that must be the barcode.
View Replies !
View Related
Adding Value In Table Based On Values In Two Other Fields
Hello: Is there (simple) way that I can populate a field based on the values of two other fields? For example, in a list of new employees, if a new employee starts on Nov 30 and works in the Administration Department, his human resources contact is Mary. If a new employee starts on Nov 30 and works in Sales, his contact would be Fred. I hope this is clear! Many thanks!
View Replies !
View Related
Deleting Duplicate Records
I am having problems deleting various rows from an SQL table, the message coming up is "The row value(s) updated or deleted either do not make the row unique or they alter multiple rows (xxrows). Any Suggestions?
View Replies !
View Related
Deleting Duplicate (but Not Similiar) Records
Hi, I currently have a table in Access that contains over 2000 records, with a total of 20 fields (although some records do have null values for certain fields). Some of these records are nearly identical to one another and differ only in one or two fields. Others, however, are exact duplicates. Is there any way I can quickly go through the table and delete those records which are duplicates but, at the same time, ensure that those which are only similiar aren't deleted? Thanks for any help!
View Replies !
View Related
Deleting The Null Values
hello friends, I have written an insert query but sometime it does not work properly and leave some column blank. I want to write a query which should check for the blank columns and delete these records.thanks in advance
View Replies !
View Related
Deleting Values In Unbound Text Box
I have an unbound text box. I put a value, run an append query and append the value to the table. I want to know if I can delete the value in the text box automatically when the append query excutes. I am using a macro to run the append query. Thanks.
View Replies !
View Related
INNER JOIN Based On Equivalent Values, Instead Of Equal Values
I'd like to create a query which will consist of simple SELECT statements as follows: SELECT [table1].[field1], [table2].[field1], [table2].[field2] FROM table1 INNER JOIN table2 ON ([table1].[fieldX] = [table2].[fieldX]); The challenge arises b/c instead of joining on equal values, such as the following: [table1] INNER JOIN [table2] ON [table1].[field1] = [table2].[field1] I would like to join based on equivalencies, such as: [table1] INNER JOIN [table2] ON [table1].[field1] = 34 is equivalent to [table2].[field1] = 2; I do not know the proper syntax, so this is where I need help. I tried to search online without any success. I appreciate your help in advance.
View Replies !
View Related
Deleting Subform Records Based On Checkbox
I have a form with a subform inside of it. On the subform I have created a field for a check box. I wish for the user to be able to click 1 or more check boxes in the subform and then click a delete button that removes all selected entries. I have this code from a seperate post regarding this problem, but am not sure how to edit it to reflect what I need. Sub DeleteRecordButton_Click() 'Delete the selected record dim strSQL as String strSQL = "DELETE * FROM mytable WHERE mytblID = " & Me![mySubForm].Form![mytblID] DoCmd.RunSQL strSQL MsgBox("Confirmed") End Sub Thank you!
View Replies !
View Related
Deleting Records Based On A Field Content?
Hi all! I'm rapidly beginning to get a little confused... I'm trying to mass-delete records based on the content of a field. I want to run either a query or a button on a form (or anything, for that matter) that will delete any records when there is an X in the N_Disp field. Any ideas? Thanks, Barry
View Replies !
View Related
Duplicate Values
I have a many to many relationship, and one of my tables (the junction table) has only 2 fields (BookID & AuthorID). These fields together are set as primary key, in order to avoid duplicate records. Eventually, if i will try to put a duplicate record, i will get Access error's message "The changes you requested to the table were not successful because they would create duplicate values in the index, primarykey or relationship ..." What is the best approach in order to check whether a duplicate record is going to be created, and if yes, i will get a customised error message rather than the above mentioned default error message ? Thanks
View Replies !
View Related
Duplicate Values (Please Help)
I need help creating a report that summarizes duplicated values. I need to write a query that groups payments of equal amounts (duplicate values only) for each vendor together. I need to show a group header containing the vendor number and name. I was using the Having Count(*) > 1 function but that only tells me which values are duplicates It doesn't display them. I need to display the duplicates. -WM
View Replies !
View Related
Allow Duplicate Values!
i have a sub form which on the click of a button sets 4 fields Private Sub Command8_Click() LastPaymentDate.Value = Date NextPaymentDate.Value = Date + 7 AmountPaid.Value = [Form_Frm_Customer]![Payments] [Form_Frm_Customer]![Payments Due By].Value = Date + 7 End Sub It updates all the fields fine but i keep getting the error 'the changes you requested were not successful because they would create duplicate values' I have checked the table and all fields are set to allow duplicates. Cant figure it out.
View Replies !
View Related
Checking For Duplicate Values
Hi, I have a form in a database for our products (doors). I have a button that duplicates one of these records (all new products are based on standard template records). When this record is created a new product number is produced by the system which was the number of the original (template) record plus the customer enquiry number. I want to be able to warn the user if they are trying to save this newly created record with a product number that already exists so that they can alter the number to be unique. It will also warn them if they re-number an existing record to another already existing product number. Since the field is defined as unique, at present the system says absolutely nothing and just doesn't save the newly created record which seems is a touch annoying! I have tried several things with dlookup, dcount arguments but can't seem to get anything to work. Hopefully someone here can point me in the right direction? Thanks in advance John
View Replies !
View Related
Duplicate Values In Combo Box
hi again, I'm building a search function into my database and everything's fine apart from the fact that i have duplicate values in my combo boxes. For example in the combo box 'market' i have many fields named Europe. How do i replace these repeated values with one distinct value? Cheers
View Replies !
View Related
Help Excluding Duplicate Values
Is there a way that in a query from several tables you can choose to exclude duplicates of a specific field? In the query I'm trying to create I am going through and counting the number of customers in a certain time frame. I don't want to count a customer each time they buy something though, I only want to count them once. The dates of their purchases are held in a seperate table for purchases and the customer information is held in a customer table. The information I need is in both of these tables and each customer has an ID number which is how the two tables are joined. When I tried using the DISTINCT code it still brought in all the purchases because the customer bought things on different dates. The only way I have ever used this, however, is to write SELECT DISTINCT at the beginning of the SQL instead of just SELECT. If there's another way to use it to do what I need I will try it as long as you tell me what it is. How can I set up the query to bring in all the information I need but not duplicate the ID numbers? Thanks in advance for any help I get!!!
View Replies !
View Related
How To Delete Duplicate Values In A Table
Good morning all! I have a problem, I have a contact list table in my database such as name, addr, company, street, country etc. The problem is that I have multiple duplicates of the same data. How do i delete the duplicates? Is there an SQL query that I can run to delete? How do I do it. Please help. Thanks a bunch!
View Replies !
View Related
ComboBox Problem With Duplicate Values
I am using a combo box to select client records. The problem I am having is that there are several clients with the same last name. The query behind the combo box sorts on Last Name, First Name. When I (as an example) select the second listed duplicate name from the combo dropdown, The form is populated with the data from the first duplicate name. (example Jones, John or Jones, William). How can I make sure that the selected name will populate the form? Thanks
View Replies !
View Related
Duplicate Values In Dropdown:MOST URGENT
I am getting the error in a grid which has combobox. This combobox allows only to select unique values from the list. Whenever i selects the same value in another combo which is already selected in the grid, its shows the popup with the below message and makes us not to click anywhere else in the application. "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries, and try again". And when i delete the duplicate value selected in combobox it shows the popup with this message "Index or primary key cannot contain NULL value". I simply wants this : Whenever user selects duplicate value, message must be displayed and user comes out from that combo. Please help urgently.
View Replies !
View Related
Duplicate Values In Index, Primary Key Etc
Hi - it's been a while since I did much access and am trying to help someone out and have got into a pickle! A quick job has turned out to be a lot more complicated! Plus I'm having to use *spit* Acc 2k. When I go into frmHighCostDrug (from the "add new drug episode" button on the frmPatient) I often get the message "The changes you requested were not successful because they would create duplicate values in the index, primary key, relationship etc etc." Now, I didn't think I'd forgotten that much. I've got primary keys, values in the table set to Null, relationships set and ref integrity set and cannot for the life of me see why this is happening. I know though, that one of you will say "oh it's that" and point it out and I'm going to feel completely stupid, but at the moment, I don't mind! I really can't figure it out. I've attached db - it's in Acc2k and I tried to do a copy Acc'97 for those who don't have it but it caused no end of problems. Came up with messages that this form contains data that Access doesn't support!!!! ??? :confused: If I can sort it I will. If anyone's got any ideas, I'd be v grateful! Tnx Ally
View Replies !
View Related
Creating Duplicate Values Error
I have a table with one primary key (ClientID) which is an autonumber (cannot be duplicated), and another field which is a number field which is set to a random 6 digits (also cannot be duplicated) whenever a new client record is created. Now, the main database is on a desktop, and my partner and i are connected to this main database through our laptops whenever we're in the office. For the last 6 months, I had only the ClientID in the form (from the Client Table), and everything would work fine whenever we created a new record in the Form, even if we created a new record at the same time on our personal laptops, the numbers would automatically be increased by one w/ no error. I just added the other field now, the regular 6 digit random number field, and sometimes (only sometimes) when we both have a different client and start a new record, we get error messages that says we're creating duplicates. When that error message comes up, one of us has to close down the form completely, and reopen and start a new record again. Not sure why this is happening. Here is the error message. http://members.shaw.ca/hgkma/errormessage.jpg Any help appreciated.!!
View Replies !
View Related
|
TRACKER
