Delete All Save One Using Like

[TheAceMan1]

Howdy All!

Making a database to remove all duplicates save the one. Using faq701-5721 works just fine for this. However, due to the nature of the data (downloadable bank statements) I find I'm going to need another table to hold the full address of each record.Its how to get this other table involved in the FAQ. Here's an Idea of what the records look like:

[tt] Description
----------------------------------------------
ATM WITHDRAWAL 39109[red]1[/red] 199 SECOND ST MINEOLA NY
ATM WITHDRAWAL 39109[red]2[/red] 199 SECOND ST MINEOLA NY
ATM WITHDRAWAL 39109[red]3[/red] 199 SECOND ST MINEOLA NY[/tt]

I think you see the problem and it occurs frequent enough.

For a little background ... the DB is designed to track bank transactions [blue]leaving unknowns[/blue] for the user to resolve. Known transactions are remarked with something the user recognizes, then copied to a known table and deleted from the UnKnown table. The Idea is wind up with no unknowns (so you can sleep)

Tables are:

[tt] tblImport tblUnKnown
-------------------- ----------------------------
ID as AutoNumber(PK) UnKnownID as AutoNumber(PK)
xDate as Date/Time Description as Text
No as Long Integer Remark as Text
Description as text
Debit as Currency
Credit as Currency[/tt]

[tt] tblKnown tblMasks
------------------------- ------------------------
KnownID as AutoNumber(PK) MaskID as AutoNumber(PK)
Description as Text Address as text
Remark as Text[/tt]

Note: All tables are [blue]Independent! ... No Relationships![/blue]

Sequence of events are:
[ol][li]Download data to tblImport. This data is untouched for reference.[/li]
[li]Transfer data from tblImport to tblUnknown. All the work is done here. Queries format, correct spacing, remove [blue]ATM Fee records[/blue] and other records not needed. faq701-5721 is run.[/li]
[li][purple]It is here I need to run the faq pinging tblUnknown against tblMasks! Mask values are existing data within the description of each record, typically the full address.[/purple][/li]
[li]Finally records are removed from tblUnknown pinging against tblKnown.[/li][/ol]

[blue]Your Thoughts? . . .[/blue]


See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[lameid]

I think this ought to work. It uses a non-equijoin so it does not use join syntax because the join is in the where clause. The caveat here is you have to both identify the records that have "duplicates" and then the one to keep requiring 2 sub queries. A better method maybe to normalize the address on import so it is not an issue. A faster method would probably be to use a temp table. I expect this will be slow but funtional. You might find yourself needing to increase you max locks per file because you get an out of memory error... Registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0\MaxLocksPerFile

Delete tblUnknown.Description 
From tblUnknown
Where tblUnknown.UnKnownID 
     Not IN(
     Select Max(tblUnknown.UnKnownID)
     From tblKnown, tblMasks
     Where tblUnknown.Description like ("*" & tblMasks.Address & "*")
     Group by tblMasks.Address)
AND tblUnknown.UnKnownID 
     IN (Select tblUnknown.UnKnownID
     From tblKnown, tblMasks
     Where tblUnknown.Description like ("*" & tblMasks.Address & "*")
Group by tblMasks.Address
Having Count (tblUnknown.UnKnownID) > 1)

In short, I think it will work but may be so slow as to not be worthwhile.