I've inherited a database at work and need to make some corrections...Here is my problem. I need to find related fields where no obvious relationship exists...Here's an example of a few rows delimeted by commas...
Anytime I see "door" AND it's followed immediatly by "knob" in the same field on the very next record (assume it's sorted correctly) then I need to add a new field to the row with "knob" and fill it with the ProductID of door. IF NOT, then fill the new field with ProductID of the original row...
1. 900k+ rows is WAY more than the JET engine can handle in any semi-efficient manner
2. The order of the rows is completely and totally arbitrary. You could pull the same query twice and, in theory, get the records back in a different order. You need something else to order them with.
I solved it. I decided to return to work after 2nd shift (when no one is in the DB) and open it exclusive, which means no locks. I then ran my nested loop and got the fields I needed. Hopefully I'm well on my way to getting this monstrosity of a DB into decent shape.
You should consider MSDE. It's very solid, and it supports stored procedures, triggers, and views. And, it's free.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert