Delete records from Access Table based on concatenated field query criteria

[sfgiantsdude]

Hi!

I would like to delete specific records from an Access table based on the results from a select query based on that table.

Specifically, referencing the PNG file I attached below, I have created a select query based on the main data table and pulling in another field from a separate lookup reference table. The select query pulls in and concatenates three of the fields from the main Access table that I want to delete records from: "CODE", "DATA", and "IND". The "LOC" field is also concatenated to the other 3 fields and is pulled from a separate reference table that is linked to the main data table.

I want to figure out a way to delete one of the two records from the main data table where the first three fields are identical (i.e. Code, Date, and LOC) and where the 4th one is different (i.e. 0 and 1). In the example below I want to delete one of the records labeled 5 or 6. The first three values are identical but the last one is different. So I want to delete one of those records from the main data table.



Hope that makes sense!

[Micron]

Cannot fathom why you'd concatenate multiple values together only to have to parse portions of them to figure out what to delete. Maybe could be as simple as including the first 3 fields in a find duplicates query (giving you a set of records where the first 3 values are the same) and include the 4th field, but not in the set that's being looked at for duplication. Then have a delete query based on that query and delete based on the Max (or Min) of the 4th field. If you feel more comfortable, or if it were necessary, you could create a select query and get the max/min and use that as the query that selects records for deletion. You didn't say which record needs to be kept, so I'm just guessing at that part. As always, I say when experimenting with deletion queries, always play with a copy of your table.

[isladogs]

Agree completely with Micron.
Suggest you do something like the screenshot below which would have your 3 fields (Code/Date/Loc) used in the outer join links



I have an extended article on ways of Synchronising Data on my website that you may find useful: http://www.mendipdatasystems.co.uk/s...a-1/4594514001

[sfgiantsdude]

Thank you both! I'll take a shot

[sfgiantsdude]

For clarification, I want to identify duplicates where the first 3 fields are identical but where the 0,1 value differs and then delete all of those that 0 at the end. Does that make sense?

[isladogs]

Yes it does. Add filter criteria for IND field =0 in the table where records will be deleted from and <>0 in the reference table.
One complication you may find is getting a message that you cannot delete from specified tables.
If so you'll need an extra step involving a make table query.
My Web article covers that as well.

Signing off now. Good luck

[Micron]

Does that make sense?

Not if you put the data together first. That's the part I'm not understanding the need for. To do what you want with what you're showing requires the concatenated string to be parsed (separated into component parts) so that individual parts can be compared. Why do that if they're not already together??

It's like asking for a teaspoon of salt from a mix that was once separate measures of flour, baking soda and salt. First put them all together. Then separate the components so you can remove the salt. You wouldn't do that.

If you're stuck with them being concatenated (e.g. that's how you get the data) then by all means enlighten us and we can move on to a solution. Either way, why not address the concern that two responders have raised? AFAIC, it makes no sense to dive right in and solve an issue in some convoluted fashion if it can be done by preventing the very thing that makes the solution convoluted.

[Micron]

I want to identify duplicates where the first 3 fields are identical but where the 0,1 value differs

Actually, not making sense for another reason.
Issue number 3 and 4 are exactly the same, even to the point that they both end in zero. It's arbitrary? Meaning it matters not which issue number record is deleted? The concatenated strings may or may not be the same, while the first field is not. Looks like the issue values are of no real value if they can be arbitrarily deleted. I'm also now thinking that's a spreadsheet, not a table, because tables don't have row numbers.

If what you've posted is pseudo data and isn't really what you're dealing with, then you need to let us know.

[sfgiantsdude]

In retrospect the only reason I concatenated it was to visualize what fields I needed to consider. I could have just created a query to pull the fields I wanted to demonstrate here.

Thanks for all the advice. However, I still am not sure how to go about this step by step. I'm not super comfortable with delete queries and outer joins, etc. I did design a find duplicate query for the first 3 fields at least.

[Micron]

I suspect you could have visualized by just placing the query fields close together or concatenating them in a field for viewing, but intending to deal with them as separate fields. I believe I tried isladog's suggestion and did get the message that I could not delete from the specified tables.

I've commented recently in another post about how tough I find subqueries, especially since people seem to want some sort of difficult grouping or other such things (I was successful there). In your case, this seemed to be the only solution and the solution is even more complicated than the other one without being concatenated. My table is tblConcatSplitDel (even though the values ended up not being concatenated). Using the query wizard to find duplicate records, you get a query with a subquery anyway. This would give records with duplicates, but since you have 2 records exactly the same (end in zero) using that query in a delete query would delete both records. You did not address this from post 8.

Here is the sql to cover that off. It uses a second subquery to get the MIN of ISSUE. You can reverse that to MAX if it suits you better. If you have situations where neither is acceptable, then I wish you luck with that. NOTE: I had to change CODE in brackets to [CDE] in this post, otherwise the forum code tags get screwed up.

DELETE tblConcatSplitDel.CODE, tblConcatSplitDel.DTE, tblConcatSplitDel.Loc, tblConcatSplitDel.IND, tblConcatSplitDel.ISSUE
FROM tblConcatSplitDel
WHERE (((tblConcatSplitDel.CODE) In (SELECT [CDE] FROM [tblConcatSplitDel] As Tmp GROUP BY [CDE],[DTE],[LOC] 
HAVING Count(*)>1  And [DTE] = [tblConcatSplitDel].[DTE] And [LOC] = [tblConcatSplitDel].[LOC])) 
AND ((tblConcatSplitDel.ISSUE) In (SELECT Min([ISSUE]) FROM [tblConcatSplitDel] AS T WHERE T.CODE = tblConcatSplitDel.CODE 
ORDER BY MIN(ISSUE) DESC)));

Hope that is what you need.

[isladogs]

Normally you will get the message Cannot delete from specified tables when you have a query based on the table to be pruned in the WHERE part of the DELETE query.
In such cases Access won't let you delete because the action would affect the query it was based on.
The solution is to turn the query into a MAKE TABLE so you have a static reference source for your DELETE query.

For an example, see item 6 on this page http://www.mendipdatasystems.co.uk/s...a-2/4594514002
Item 7 using NOT EXISTS may be worth reading as well

[isladogs]

In case it helps, have a read of this post where I've done each step in the process of identifying duplicates and removing all except the first of each dupe
https://www.access-programmers.co.uk...16&postcount=9