delete a pair of records with algebraic summ...

[2009luca]

I use ado and vb6 classic and access database...

in table TEST have:

Field1 Field2 Field3
AAA BBB -100
AAA CCC 100
AAA BBB 100
AAA BBB 100
.....

based the goup by Field1 and Field2 i need to delete a pair of recorset where the algebric summ of filed3 is =0

in this my case delete this two recordset:
AAA BBB -100
AAA BBB 100

to the and of query i need to have this result:

AAA CCC 100
AAA BBB 100

i hope understand me....

Tks.

 

[SkipVought]

Hi,

Wouldn't that be simply...

Select Distinct Field1, Field2, Field3
From TEST
Group By Field1, Field2, Field3

Use the resultset to create a new table that would contain you expected results.

 

[2009luca]

????

 

[Andrzejek]

First I would introduce a PK to this table:
[pre][blue]
UID[/blue] Field1 Field2 Field3 [blue]
1 [/blue]AAA BBB -100[blue]
2 [/blue]AAA CCC 100[blue]
3 [/blue]AAA BBB 100[blue]
4 [/blue]AAA BBB 100
[/pre]
Then I would create a record set with the data in an appropriate Order. Go down the record set and compare 2 records at the time to see if the pair needs to be deleted.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[2009luca]

Andrzejek
not clear for me:-(

tks in other case for suggestion

 

[Andrzejek]

How about this:

rstA.Open "SELECT * FROM TEST"
rstB.Open "SELECT * FROM TEST"

Do While Not rstA.EOF
    rstB.FIlter = "Field1 = '" & rstA.Field1 & "' And " & _
                  "Field2 = '" & rstA.Field2 & "' And " & _
                  "Field3 = " & (rstA.Field3 * -1)
    If rstB.RecordCount > 0 Then[green]
        'We have one or more records that match
        'Delete a record from rstA and
        'Delete first matching record from rstB
[/green]
        strSQL = "Delete from TEST " & _
                 " WHERE UID IN(" & rstA.UID & ", " & rstB.UID & ")"
        Execute strSQL
    End If

    rstA.MoveNext
Loop

rstA.Close
rstB.Close

Just an un-tested pseudo-code.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.