I came across a query problem that seems simple but apparently is not. I have 100000 or so sequentially numbered records. I want to query for about 200 of them which are randomly located.
How can I do this?
How can I do this?
-
-
RE: Searching for lots of matches
What do the desired records have in common?
Alternatively, how will Alpha Five identify the desired records? -
RE: Searching for lots of matches
A query, by nature, requires that you be able to define a "filter", meaning that the records you want have something in common. You "filter out" all the records that do not satisfy the filter. Location of the records is unimportant (unless you wanted the first x number of records).
What do your "200 of them" have in common?There can be only one.Comment
-
RE: Searching for lots of matches
If the desired records have nothing in common, and cannot be conveniently aggregated, you may need to work your way through the table and 'mark' the desired records. Then run a query to extract only the marked records. There are several ways to do this, but the pattern is usually:
1) unmark all records
2) find and mark all desired records
3) extract the desired records by running a query
4) release the query and 'show all' when your processing is finishedComment
-
RE: Searching for lots of matches
That's an interesting point, Tom.
I didn't consider that some one would want to select 200 records that didn't have a common factor. Yet I can see that one might scroll through a table and, looking at each record individually, might decide that "some", on the basis of varying combinations of factors, should be included in the query.
I still think the filter "should be" writable, it just might be more trouble than it was worth.There can be only one.Comment
-
RE: Searching for lots of matches
Alpha will recognize them by the user asking for them using a specific number--like a customer number. The records all have various entry problems and need to be changed. But first they are identified by number.
Now, the normal way you can do this is QBF in the NUMBER field. Entry would be "1234,4334,5665,8976", etc. then doing the search. However, there is a limit in Alpha as to how big an expression can be. And there's the rub.Comment
-
RE: Searching for lots of matches
This would undoubtedly work but is key and search intensive--i.e., you have to search for each individual record.
Keying in a series such as 103,656,878,577,etc would be much faster assuming you could do the search at the end of CSV entry.Comment
-
RE: Searching for lots of matches
You could use the INLIST() function. It's limited to 25 expressions but still better than anything else right there.
The other thing you could do is make a table of problem numbers (data entry would be very easy) and then use that table as a source for some query work, or possibly some marking/query work.
- Steve-Steve
sigpic
Comment
-
RE: Searching for lots of matches
The process of finding and marking the records should be automated in a script, of course.
Store the list in a delimited file, and then import it to a temp table.
Even better, load the values into a temp table directly.
Then arrange for your script to:
Step through the temp table one item at a time.
For each item do a find by key into your big table, and mark each record that's found.
Then discard the temp table and have the script run the query against all marked records in the big table.
Once the script is finished all you have to do is enter the values to search for, and push a button. Isn't that what you are looking for?Comment
-
RE: Searching for lots of matches
Try this:
You said sequentially numbered records. I assume that is a field on the record call it sequence. If that is the case try this:
Create a dummy table with one field sequence (same size as your table)
Create a set:
Parent is your table
Child is dummy table
Link one to one on the sequence
Enter the desired sequence numbers into the dummy table
Do a query on the new set where alltrim(childsequence) > 0
Zap the dummy file when you want to do another search and enter more numbersComment
Comment