Deleting Duplicate Values Based On 2 Fields

Jan 3, 2006

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 Replies


ADVERTISEMENT

Deleting Duplicate Values

Feb 16, 2005

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 5 Replies View Related

Prevent Duplicate Records Based On Multiple Fields

Nov 15, 2005

how or what function (DLookup) should I use to prevent duplicate records based on multiple fields? I want to look at data in three fields that can't match existing data in those three fields. It's ok if one or two of the fields match but not all three.

View 2 Replies View Related

Tables :: More Than One Unique Values - Prevent Entry Of Duplicate Combination Of Fields

Nov 4, 2014

1. I have a database (see attached) with three tables all of them with the same fields. The first three are numbers (InCo_No, Proto_No, Year_No). Each of these fields (numbers) can be the same in the other table(eg. Year_No), but the combination of the three cannot be.

How can I prevent the entry of a duplicate combination of these three fields?

2. I want to have a form to fill the three tables separately, depending the values in the other fields.

How can I do this?

View 3 Replies View Related

Adding Fields Based On Values In Other Fields

Jun 6, 2007

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 3 Replies View Related

Automatic Values Based On Other Fields

May 18, 2007

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 8 Replies View Related

Adding Value In Table Based On Values In Two Other Fields

Dec 3, 2007

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 12 Replies View Related

Deleting Duplicate Records

Apr 30, 2008

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 1 Replies View Related

Forms :: Prevent Duplicate Values With Alert After User Enters Duplicate Value

Dec 30, 2013

We are working on an Access (2007) database that is on a SharePoint Site (2007).

Currently the form is operational, but there is one last thing that would be nice to have.

The table is "Updated Headcount" which contains "EMP_ID" which are unique numbers stored as text.

In the event a new employee is entered in the system by another user on this site we would like to prevent any duplicate "EMPID"s from being entered and saved on the SharePoint, we would also like to alert the user and prevent the data from being saved.

All data is currently bound, so once the user makes a change it is made, no submit button is required.

We are running into some difficulties in doing a dlookup from the value entered and comparing to a column in the table.

TABLE - UPDATED HEADCOUNT
COLUMN in UPDATED HEADCOUNT - EMPID
FORM CELL user will input an EMPID - newEMPID
FORM CELL used for a dlookup to compare what user has entered to what is already in the table - duplicateEMPID

So below is what we are trying to do, we are sure there are a few commands missing....

=IIF(newEMPID=dlookup([UPDATED HEADCOUNT]![EMPID]), newEMPID, "Error, EMPID already exists")

View 11 Replies View Related

Deleting Duplicate (but Not Similiar) Records

Jun 10, 2006

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 2 Replies View Related

Tables :: Deleting Duplicate Records

Sep 11, 2013

i have a data base with a primary key but some records have been added from a excel data base and now the data base has duplicate records with different primary keys. I do a Query looking for duplicate records based on a field not the the primary key. There are 315 out of 22000. I can sort the table and then manually delete the dups. The Query does not allow deleting records so finding the culprits in the full Data base is time consuming. How can i delete the duplicate records using a query? Is this function available hidden someplace in the guts of Access? Seems like this would be a frequent problem. The Microsoft forum addresses this problem and the only viable solution is the manual method, when they get to the Query method it looks like they just forgot what the subject was and tell you how to delete one record if you know what the criteria is. If two non Key fields are Identical then i want to delete one of the records.

View 3 Replies View Related

Merging And Deleting Duplicate Records

Jan 6, 2012

I have an Access table with a number of records which refer to the same [InvoiceNo] but with data in different fields. Normally, my database works like this:

1)Import data from Excel File to tblMain in Access
2)Import data from Excel File to tblVendorLine in Access
3)Import data from Excel File to tblDunning in Access
4)Query is sorting from two first tables only records that refer to the third one (by InvoiceNo)
5)We have only items from tblDunning with data from other two tables to be send (mail to the vendor).

The point is, that when I have relation between those three tables, I receive only few records, but sometimes - for example - tblDunning refers to 5 records in tblMain and to 7 records in tblVendorLine - but there is only 4 records related between tblMain and tblVendorLine, so query shows me only two records. Ive made a query for each table (tblMain & tblVendorLine - relation with tblDunning). Then make one query of those two queries. It append data to new table tblDunnAll

So for example the table would look like this after

Vendor | InvoiceNo | CoCode | Status | Clearing_Date | PBk
100000 23333 GB11 23/12/2011
233332 121212 GB18 16/12/2011
233332 121212 Complete K
134444 777777 GB12 Complete
100000 23333 GB11 Complete K

What I'd like to have is a table with:

Vendor | InvoiceNo | CoCode | Status | Clearing_Date | PBk
100000 23333 GB11 Complete 23/12/2011 K
233332 121212 GB18 Complete 16/12/2011 K
134444 777777 GB12 Complete

how to go about merging the data for each name into one record. There are lots of very useful posting about using SQL to delete duplicate but I can't find a posting with addresses this issue.

View 1 Replies View Related

How To Clear Values In Specific Fields Based On Updating Value Of Another Field

Feb 27, 2015

I am working on form where the user selects either "IN" or "OUT" from a dropdown of field name "CheckOut" in Frm1.

If they select "OUT" they will in turn need to fill in 2 additional fields. When they change the value back from "OUT" to "IN",

I want those other fields to be cleared of data for just this record so next time they change back to "OUT" from "IN" those 2 additional fields are already blank.

View 1 Replies View Related

Queries :: Query Records With Both Duplicate Values And Different Values?

Jun 18, 2013

I'm trying to determine the SQL to return only those records in a table which have duplicate values in each of two fields, but different values in a third field. Here's an example:

Code:

AcctNum FoodType FoodClass
------- -------- ---------
A123 Apple Fruit
A123 Apple Fruit
A123 Grape Fruit
A456 Potato Vegetable
A456 Potato Perishable
A789 Carrot Vegetable
A001 Banana Fruit

For the above table, I'm trying to return records which have multiple entries for AcctNum + FoodType, but DIFFERENT values for FoodClass. So for the above table, the query would return:

Code:

AcctNum FoodType FoodClass
------- -------- ---------
A456 Potato Vegetable
A456 Potato Perishable

It returns these two records because there is more than one record with for the AcctNum + FoodType (i.e. 'A456' + 'Potato'), but DIFFERENT values for FoodClass (i.e. one record has 'Vegetable' while the other has 'Perishable').

View 5 Replies View Related

Deleting The Null Values

Jul 20, 2005

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 1 Replies View Related

Deleting Values In Unbound Text Box

Aug 23, 2006

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 1 Replies View Related

Modules & VBA :: Deleting Values In Array?

Jul 1, 2013

How do I delete values in an array? I want to reuse that array but first I need to delete its values so I would start with Arr(0) rather than where I stopped off with the last loop at Arr(10) [assuming the last calculation inserted until Arr(9)]

Here is my code:

Set rst = CurrentDb.OpenRecordset( _
"Select * from dbo_ProductStructure where ChildProductNbr Like '*" & txtPartNumber & "*'") 'search associated fields with user input
While rst.EOF = False
ReDim Preserve Arr(i)
Arr(i) = rst.Fields("ParentProductNbr")
i = i + 1
rst.MoveNext

Wend 'end of while loop

x = Arr

View 9 Replies View Related

Modules & VBA :: Deleting Values From A Table Selected From A List Box

Mar 19, 2014

Im trying to delete values from a table based on the selected values of a listbox. The listbox values have a hidden column which related to the ID on the table to which I am trying to delete from (if that makes sense).

If I use the following code:

Code:
Set ctl = Me.Results_listbox
For Each varItem In ctl.ItemSelected
CurrentDb.Execute "Delete * FROM Table1 WHERE Table1.ID = " & ctl.ItemData(varItem)
Next varItem

I get an error stating Object doesn't support this property or method.

View 7 Replies View Related

Deleting Subform Records Based On Checkbox

Jun 21, 2005

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 1 Replies View Related

Deleting Records Based On A Field Content?

Nov 16, 2004

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 3 Replies View Related

Modules & VBA :: Deleting Records Based On Month

Jul 26, 2013

I want to write an sql or vba code to delete records based on the month...For example I have a field called date which contains a date and I want to delete all Dates that are In April

Ive tried
Delete from LinkTable Where month(Date) = 'April'
Still Nothing

View 3 Replies View Related

Modules & VBA :: Deleting File Based On Date

Feb 12, 2014

So I currently have a module which automatically backs up my database to a separate file everyday on open of my database.

If is it possible to create a function which will allow me to delete these backups after a certain amount of days (eg 30 days). They all have different file names based on the time that the backup was created but have the same extension of "*.accdb". I ask because it stores EVERY backup so the storage space required will soon build up unless manually deleting the files which is not what my client will want,

I have tried using the "Kill(pathname)" function but have had no luck yet!

If possible, I would like this to be done automatically either on open or on close of my database.

Would this be done with a module?

View 1 Replies View Related

Queries :: Deleting Records Based On Selections From Another Table

Apr 1, 2013

I am trying to create a Delete query.

I am trying to delete a specific part from multiple BOMs in my database.

I have a table of the BOMs that I want to look in. I called this table PartTable. I also linked my database table SYSADM.REQUIREMENT which contains all the requirement parts for all of our BOMs.

So I am wanting to delete only part number 123XX from each of the BOMs in my PartTable.

I am able to select the records with:

Code:
SELECT SYSADM_REQUIREMENT.*
FROM SYSADM_REQUIREMENT INNER JOIN PartTable ON SYSADM_REQUIREMENT.WORKORDER_BASE_ID = PartTable.PART_ID
WHERE (((SYSADM_REQUIREMENT.WORKORDER_TYPE)="M") AND ((SYSADM_REQUIREMENT.WORKORDER_BASE_ID)=[PartTable].[PART_ID]) AND ((SYSADM_REQUIREMENT.WORKORDER_LOT_ID)="0") AND ((SYSADM_REQUIREMENT.PART_ID)="123XX"));

Now how do I delete these same records.

I am getting error saying I have to select a table to delete from....

View 11 Replies View Related

General :: Deleting Record Based On Double Criteria

Nov 30, 2012

I am wanting to delete a record based on 2 criteria. ive had a look on the net and it looked so simple. I tried

PHP Code:

CurrentDb.Execute _    "DELETE FROM tblRecommendedProducts " & _    "WHERE ClientDetailsID = " & [Forms]![frmClientSale]![ClientDetailsID] And "WHERE ItemsID = " & [Forms]![frmClientSale-Retail]![ItemsID], dbFailOnError 

View 14 Replies View Related

Form Based On Queries - Deleting Field From Main Table

Mar 28, 2014

In my Access2000 db, I have a table, on which many different queries are based. Many forms are based on those queries. I want to delete some redundant fields from the table. I tried deleting one, but I found I could no longer open any of the associated queries and forms. Is there a simple way to delete table fields so that it doesn't stop me from opening associated queries and forms? (There are hundreds of them, and I have a lot of table fields that I want to delete).

View 1 Replies View Related

Queries :: Deleting Fields Within A Table

Sep 16, 2014

Is it possible to create a query that will delete the fields within a table?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved