Delete Duplicate Records Based On Multiple Criteria

Sep 21, 2015

I need developing a new delete query criteria to add onto existing delete duplicate queries for deleting duplicate values. I need the new delete duplicate query to take precedent over the other two so that those values retained in the first query are kept retained after going through the second and third delete queries. Attached is a document laying out the request.

View Replies


ADVERTISEMENT

Cannot Allow Duplicate Records In 2 Tables Based On 2 Criteria

Sep 26, 2005

Dear all,

I would like to seek your help on this problem.

I would like to do a search for my records based on 2 different criterias. If found, the function should just return a boolean value so that I can act further based on the boolean value.

The reason I'm doing this is that I have 2 similar tables that contains EventID and AttendeeID and these 2 tables cannot have the same EventID and AttendeeID. So before I can allow a record to be inserted in the 1st table, it must check that there is no record in the 2nd table that has the same EventID and AttendeID. If there is, then i cannot allow the record to be inserted in the 1st table.

The same thing applies when I want to insert a record in the 2nd table. It must now check the first table.

Recordset.find seems to be only catering to 1 criteria, not 2.

Pls help. Codes are most welcome too.

View 3 Replies View Related

Delete Records Based On Date Criteria

Aug 16, 2005

I have a table that stores records by "return date". I would like to create a delete query that removes all records that fall outside of the date range entered on a specific form (frmMainMenu).

I can deleted the records that match the form criteria, however that is not what I need. The criteria used to delete the records was:

Between [Forms]![frmMainMenu]![from date] And [Forms]![frmMainMenu]![to date]

Let's say that the frmMainMenu contain the date range of 8/1/05 to 8/10/05, however the table contained data from 6/1/05-8/14/05, I would like for the query to delete all records that have a date 6/1/05-07/31/05 and 08/11/05-08/14/05.

Any assistance would be greatly appreciated. Thanks in advance for your help![/SIZE]

View 1 Replies View Related

Queries :: Delete Records Based On Criteria In Another Table

Jun 3, 2013

I am trying to create a delete query that, for a given person, deletes records in Table B that do not have a corresponding record in Table A.

Here are the relevant tables:

tblStates holds StateID, StateName, and RegionID (RegionID is a FK to tblRegions).
tblPeopleStates is a junction table between tblPeople and tblStates.

It lists states assigned to people. It has 3 fields: PersonStateID, PersonID, StateID.

tblPeopleRegions is a junction table between tblPeople and tblRegions.

It lists regions assigned to people. It has 3 fields: PersonRegionID, PersonID, RegionID.

For a given PersonID, I need to delete records (i.e., states) in tblPeopleStates whose RegionID is *not* in tblPeopleRegions.

For example, pretend that tblStates shows that State IDs 1, 5, and 6 are all in Region ID (i.e., all have a RegionID = 10).

If Joe (PersonID = 200) has StateIDs 1, 5, and 6 in tblPeopleStates, but doesn't have a record for RegionID = 10 in tblPeopleRegions, I need to delete his three records in tblPeopleStates (i.e., the ones where StateID = 1, 5, and 6).

PersonID will be found on [Forms]![frmMain]![subform1].[Form]![subform2].Form]![PersonID]

View 8 Replies View Related

Delete Records That Match Multiple Criteria From Second Table

Jan 23, 2012

I would like to create a query that will delete records that match several fields from another table. This is complicated by the fact that one of the fields will be in one of 3 columns.I have attached a test database (no real details), all Sheet2 entries need to be deleted from Sheet1.

What I need to do is delete records that have the same 'Surname' and 'DPS' value but also the same 'Line5' value from Sheet2 in 'Line3' or 'Line4' or 'Line5' in Sheet1.The 'Surname' and 'DPS' are no problem, it's the variable position of the third field. I think I could do it in three separate queries but it would definitely be better in one.

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

Delete Certain Records Based On Selected Date - Data Type Mismatch In Criteria Expression

Aug 24, 2015

I want to delete certain records based on the selected date. However, I come across with this is error - Run time error '3464' (Data type mismatch in criteria expression).This part is highlighted in yellow. I even used the debug.print to test out if the sql statement is executed properly.

Code:
DoCmd.RunSQL DelSummarySQL

Here is my full code

Code:
Private Sub cmd_Delete_Click()
Dim DelSummarySQL As String
Dim StartRange As Date

[code]...

View 2 Replies View Related

Queries :: How To Count Records Based On Multiple Criteria From Multiple Tables

Jan 4, 2014

I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".

View 4 Replies View Related

Selecting Records Based On Multiple Criteria

Jul 10, 2007

hi,

i am querying a single table that contains card numbers and corresponding transaction codes (amongst other things). a single card number can have multiple transaction codes.

i want to select all card numbers that have both 0100 and 0802 transaction codes.

if i use a select query with ="0100" Or "0802"as the criteria, it returns all card numbers that have either 0100 or 0802 transaction types. if i then change the query to crosstab and group by card number, it is clear that some cards have both transaction types.

when i try to change the criteria to ="0100" And "0802" it returns nothing at all.

i'm sure the solution is something really simple - any idea what i'm doing wrong?

View 7 Replies View Related

How To Delete Duplicate Records

Apr 30, 2007

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

View 1 Replies View Related

Duplicate Records Delete

Jan 14, 2008

On the below data I need to delete records where dep is blank or the same for the same ID. So I need to find and del recNr. 1,4,5. Any help will be appreceated!Thanks

recNr IDDep
1 ABX
2 ABX 1234
3 ABX 1258
4 CDE
5 CDE 7895
6 CDE 7895

View 5 Replies View Related

Delete Duplicate Records

Feb 2, 2006

Help please!
I have a form (frmHalfTab) built on a table (tblHalfTab2) that has existing data in it. In the very near future, I will be uploading additional data to this table via excel cut and paste. What I want to do is search for and eliminate duplicate records that have duplicate data in TWO fields, (MemberNum) and (MBRDrug), so that if MemberNum is "10" and MBRDrug is "Zzz" for more than one record, the duplicated records need to be deleted, OR a check box needs to be checked automatically for all the identical records, the original and the duplicate(s). I wanted to put this behind a button, if possible.

Now, I have created a duplicate query that will find the duplicated records, but I'm not sure how to use it to get rid of the duplicated records or check the checkbox appropriately. Should I ditch this query and choose a different route, or am I on the right track? Thanks in advance for your help.:confused:

View 2 Replies View Related

Delete Duplicate Records In Another Table

Apr 12, 2012

I have two tables that have duplicate order numbers. I would like to compare the first table with the second and then delete the records from the second that match.Can I set this up in a macro and run it every day?

View 2 Replies View Related

SQL Code To Delete Duplicate Records

Feb 19, 2013

I have a table that has 1335 records in, and columns names, Title, First Name, Surname, Address, Address2, Town/City, County, Postcode.I want to know how to write a SQL code, which will locate all the duplicates that match the First Name, Surname and Postcode and delete them, but keeping 1 copy of the duplicate.I have created a query to locate the duplicate records, and I have put the SQL code there. I tried changing this, however it would delete all the records, so I am not to sure what to try...

SELECT SurnameSearch.Title, SurnameSearch.[First Name], SurnameSearch.Surname, SurnameSearch.Address, SurnameSearch.Address2, SurnameSearch.[Town/City], SurnameSearch.County, SurnameSearch.Postcode
FROM SurnameSearch
WHERE (((SurnameSearch.[First Name]) In (SELECT [First Name] FROM [SurnameSearch] As Tmp GROUP BY [First Name],[Surname],[Postcode] HAVING Count(*)>1 And [Surname] = [SurnameSearch].[Surname] And [Postcode] = [SurnameSearch].[Postcode])))
ORDER BY SurnameSearch.[First Name], SurnameSearch.Surname, SurnameSearch.Postcode;

View 1 Replies View Related

Delete Duplicate Records Or Blank Records

Mar 19, 2007

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

View 3 Replies View Related

Delete Duplicate Records And Unite Results

Sep 24, 2013

I know this probably has been asked a 1000 times but I don't seem to be able to edit the uncommented VBA-codes I found online to work on my database.

I already ran a query that shows me the duplicate records of my original table. Now I m looking for a way to

a) delete all duplicate records (not just specific ones by using a criteria) and
b) unite the unique records (result of a)) with the original ones that were not displayed with the "find-duplicates-query" ....

View 1 Replies View Related

Tables :: Detect / Delete Duplicate Records In A Table

Apr 22, 2014

I'm wondering if it's possible to create a Query / Macro / VBA or whatever works, to detect the duplicate records in a table and delete them with the push of a command button. Or, even to do this automatically when the database opens using an AutoExec macro or something ?. I'd like for one copy of each record to be left intact in the table.

View 8 Replies View Related

Modules & VBA :: Duplicate Checking - Multiple Criteria

Jun 15, 2013

I'm using Access 2013. I want to check duplicate data. I have a form where from insert data into table but before insertion I want check multiple criteria with form. How can I do this

Field are:
BatchID
BillNum
CIH
IH

back end form code is:

Private Sub cmdCheck_Click()
Dim criteria As String
criteria = "[BatchID]=" & Me.cboBatchID & " AND [BillNum]=" & Me.txtBillNum & " AND [CIH]=" & Me.txtCIH & " AND [IH]=" & Me.txtIH & ""

[Code] ....

Finally I'm getting this error: Run Time Error 3075 Syntax Error Missing Operator in qry Expr.

View 3 Replies View Related

Modules & VBA :: Delete Records From A Table Based On Records In Another

Feb 7, 2014

I have a table InvPrice and Updated Pricing

Need to delete all records from InvPrice that Match UpdatedPricing

InvPrice.StockCode = UpdatedPricing.StockCode
InvPrice.PriceCode = UpdatedPricing.StockCode

I have tried something like this...

Dim dbs As DAO.Database, sql As String, rCount As Integer
Set dbs = CurrentDb
sql = "DELETE * dbo_InvPrice Inner Join (dbo_InvPrice Inner Join UpdatedPricing on dbo_InvPrice.StockCode = UpdatedPricing.StockCode ) ON on dbo_INvPrice.PriceCode = UpdatedPricing.PriceCode "
dbs.Execute sql, dbFailOnError

View 14 Replies View Related

Delete Records Based On Query

Feb 2, 2006

Hi,

I'm getting really mixed up here, Im trying to delete all records in table products based on the following query;

SELECT products.*, tbl_stage_product_update.[Catalogue No]

FROM tbl_stage_product_update INNER JOIN products ON
tbl_stage_product_update.[Product Code] = products.pID

WHERE (((tbl_stage_product_update.[Catalogue No])="0"));

I've tried using the exists keyword with the previous query as a sub query, but it seems to delete all records form table products.

can anyone offer any advice.

TIA.

Mark

View 2 Replies View Related

Forms :: Delete Records Based On Inputs

Nov 23, 2013

I have a form Delete which contains four fields i.e. Date, City, Depots and Vendor, which has combobox.

I am trying to delete a record from table "Sheet1" based on the combobox, for which i have written the following code, but getting an error at the lines highlighted in red :

Private Sub Command30_Click()
On Error GoTo Err_delete_Click
Dim stDocName As String
Dim intResponse As Integer
intResponse = MsgBox("Are you sure you want to delete this record?", vbYesNo + vbExclamation, "Cash Management Team")
Select Case intResponse
Case vbYes
CurrentDb.Execute _

[Code]...

View 9 Replies View Related

Delete Records From One Table Based On Record From Another

Oct 10, 2014

I want to Delete all records from the Table Named "CustomerWiseRotaryTillerRates" (Given Below) if the State in the State Filed in found in the "Customer_Master" Table (attached Herewith). What will be the correct procedure ?

I followed the steps referred in page no. 67 & 68 in the Book "Microsoft Access 2007 Data Analysis" by Mr. Michel Alexender.

Customer Name
State
Product_Code
Product_Name
Price_perunit

TSI Engineering Indutries (P) Ltd.
Assam
P_05
1500_Rotary_Tiller_220_RPM
Rs. 63,722.00

Kishan Krishi Engineering Works
Jharkhand
P_05
1500_Rotary_Tiller_220_RPM
Rs. 62,997.00

TSI Engineering Indutries (P) Ltd.
Assam
P_06
1750_Rotary_Tiller_220_RPM
Rs. 66,663.00

Shri_Tata_Enterprises
Bihar
P_06
1750_Rotary_Tiller_220_RPM
Rs. 65,683.00

View 1 Replies View Related

Delete Multiple Records

May 9, 2007

how do i delete multiple records in a table, for example if i have a table that as serial_num 123 twice, how do i delete the two records, basically i don't want to delete one record, i want to delete the two records. Thanks.

View 5 Replies View Related

How To Delete Multiple Records

Aug 15, 2005

Hi,

I'm trying to delete say 4000 records, out of total 5000 records. I want to keep 1000 records based on the ID.

How can I write the SQL DELETE query, so that only 1000 records remain based on the query?

Thank you in advance for your great help.

regards,
Pranesh
Alberta, Canada

View 1 Replies View Related

Delete Records With Multiple Relationships

Sep 20, 2006

Hi

I am in the process of designing a sports database and have come across a problem that I can not seem to fix.

I have 3 tables that are linked, they are Customer, Program (this is the customers exercise program) and Health (the customers health records).

I have tried to set up a query that deletes a customer if a command button is clicked. This works find as long as the table I am deleting the data from is not linked to another table.

However when I try to delete a customer record it says that I can't as the data is linked and I have to delete the relationships first.

The only way I can think of doing this is to setup a query that deletes all the data in each table at the same time. I can not get this to work though.

Is this a problem that can be solved or will I have to change the way my relationships are set up?

I would appreciate any help or suggestions.

Thanks

View 4 Replies View Related

How Do I Delete Multiple Rows Or Records?

Apr 26, 2006

Is there a way to select several rows of records in a datasheet view of a table and delete all of the selected records?

Every time I try to delete more than one record at a time, I get the following message: "The Micorsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time."

Then it will let me delete one record out of the selection.

I am the only one in that particular table at the time I am trying to delete the record.

Thanks in advance for your help...

View 3 Replies View Related







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