[RESOLVED] query help

[wengang]

Hey all.
Access database, working in a VBA macro.

I'm trying to simplify a task by keeping as much as possible in database query and commands rather than doing this all in VBA.

Background:
I've built a term search and replace macro driven by an ever-growing data table of terms to search and terms to replace them with in a Word doc.

I usually just query the whole thing:
select * from tblTerms order by ID asc
and perform recordset count iterations of search and replace.
The long-term problem is that some search terms precede other terms that include the former term in the latter.
Ex:
tblTerms
ID Search Replace
1 dog canine
2 doghouse canine residence

just made up examples of course, but you can see that if dog is replaced by canine, then doghouse will never be found. The list is too long for me to reason out the terms and change them manually, so I need to automate finding any term such that a term with a higher ID number fully incorporates it, then ideally I can just switch their IDs.
Don't worry about full word match and those problems (the actual data is in Chinese!).

Something like:
select * from tblTerms where (any other row like '% given row's term %' and ID > given row's id)
then ultimately
update tblTerms set Search = smaller search term, Replace = smaller replace term
where ID = larger term's ID

and of course

update tblTerms set Search = larger search term, Replace = larger replace term
where ID = smaller term's ID
*******

The easiest way I can think of to code it is just to pull the whole data table into an array in VBA, then go through the entire array for each term and switch as necessary, and keep recursing until no changes are made, but that would not be a very efficient code.

Can any of this be moved into the query language?
(As for stored procedures, I'm pretty good with them in SQL Server, but haven't used Access's internal tools very much at all).
Thanks.

[si_the_geek]

Is there a particular reason that you want to actually change the ID numbers around?

The simplest solution would be to change the way you order the data when you use it, to do it based on the length of the item rather than the ID number:

select * from tblTerms order by Len(Search) desc

This will take slightly longer to run than ordering by ID, but it will always return items in a way that ensures the longest match first (which isn't guaranteed with the "ID swap" method, as it needs to be re-run whenever something is added).

[wengang]

I don't know si.
I had that thought early on and somehow convinced myself it wouldn't solve the problem.
Can't remember what I was thinking now.
I guess you're right.
I already wrote the clunky vba code to do the job, but I guess I can delete that now.
Thanks.