How to remove certain records?

  • Thread starter Thread starter Pmxgs
  • Start date Start date
P

Pmxgs

Hi there!

Can someone please tell me how to remove certain records from a table?
I import several records to a table from a text file, but after i import
those records i need to remove some records that have negative values, which
is easy.

The problem is that i also need to remove the records that come immediately
before those that have negative values and the problem lies in the fact that
these records come immediately before the ones that have negative values is
the only thing that identifies them.

Example:
cod. desc. value
12 trg 10
35 erre 15 <== to be removed
89 ghfg -15 <== to be removed
56 dsfe 16

Thanks
 
Hi there!

Can someone please tell me how to remove certain records from a table?
I import several records to a table from a text file, but after i import
those records i need to remove some records that have negative values, which
is easy.

The problem is that i also need to remove the records that come immediately
before those that have negative values and the problem lies in the fact that
these records come immediately before the ones that have negative values is
the only thing that identifies them.
Click to expand...

You're in real trouble. There is no "before" in an Access table; a
table is an unordered "bag" of data. Unless, in the importing process,
you can incorporate some sort of sequential or at least monotonically
increasing sequence number, there is simply no way to find the
"before" record.

You can open a Recordset and step through it - and 98% of the time it
will work; but 2% of the time Access will have stored the records on
disk in an order different than in the text file and you'll delete the
wrong record.
 
first, add an autonumber field to your table, and call it Row_ID
next create a query called qryPrevious
Select Cod, desc, value, Row_ID + 1 as PrevRec, Row_ID from YourTable

now create a delete query
delete * from qryprevious inner join YourTable
on YourTable.Row_id = qryPrevious.PrevRec
where yourtable.value < 0

Preferably, add this to your join clause as well if it is appropriate
And YourTable.Value = qryPrevious.Value

This is not a foolproof method, but it should work in most cases as john
pointed out

HS
 
Back
Top