Filtering records in large tables

[timbar]

I have a large table of x,y,z coordinate values. The table contains over a million records.

Is there a convenient way to select and retain only a certain, lesser number of records using a query?

I'd like to select, say, every fourth record and retain that record, and discard the others, creating a new table in the process. In general, I'd like to be able specify the frequency of the records retained (say every tenth, instead of every fourth record). Do I need to write code to allow this generalization, or can I control the record-retention frequency in a query?

Thanks for your feedback.

Tim Barbour
timbar@att.net

 

[jsteph]

If it's a one-time shot, and you want to 'thin the herd' you could do some thing like:

Say you have a sequetial ID, such as autonumber, we'll call it RecID:

Delete From TheTable Where (RecID Mod 4) = 0;


Change the 4 to different number based on how thin you want to go.
--jsteph

 

[timbar]

Thanks for your input.

I understand the logic of your recommendation, but am not sure about how to implement it.

I have a table (named Thin) with an autonumber field named ID. The other three fields are x, y, and z coordinate value numbers. There are over a million records in the table.

Should I create a select query which operates on the table (Thin) and enter the "Delete from Thin where (ID mod 4) = 0" syntax? If so, am I using the right syntax, and in what field do I enter this criterion?

Or, am I approaching this incorrectly. I'm sure the answer is simple. I just haven't used this type of criterion before.

Thanks for you feedback.

Tim Barbour
timbar@att.net

 

[timbar]

I've figured out that I should be using a &quot;delete query&quot; and I see how the from and where grid cells works. I can delete records, for example, with an ID number of less than 10, for example by using a criterion <10 under the where field.

I can't get the Mod function to work in criterion field (under where), however. I'm using (id mod 4) = 0 and getting a data mismatch error.

I have the from (table) where (ID) working OK. But the criterion under the where field (id mod 4) = 0 isn't right.

Thanks for your advise on this.

Tim Barbour
timbar@att.net

 

[jsteph]

timbar,
You need to put the 'mod' thing into a 'field' row of a blank column:
Expr1: [ID] mod 4

...then in the criteria of that new column, put 0
--jsteph

 

[timbar]

That did the trick.

Thanks for your help. It's just what I needed.

Tim Barbour