Changing query result with code

[Guest_imported]

I am hoping someone can help me with a problem that has stumped me.
Basically i have run a query but some of the records need to be deleted ( given a set criteria ) but i can't seem to do it using a normal query criteria. Is there a way of using recordsets to edit the query result and will this preserve the underlying table/query data?

To give you an idea of what happening ..

-monthly info in tables1,2,3....

-record X in each table which leads to the query finding the difference

Table1 Table2 Query (t1-t2)

t1_X t2_X t1_X - t2_X

this is easy.....but some times record X needs to be "reset" so we end up with ...


Table1 Table2 Query (t1-t2)

t1_X1 t2_X1 t1_X1 - t2_X1
t1_X2 t2_X2 t1_X2 - t2_X2

the query will also give t1_X1 - t2_X2
t1_X2 - t2_X1

I know that an obvious answer would be to split them into two records but because this application is referring to a metering system, the "two" records should really be assigned to one meter. In addition, the recordX cannot be altered because in the next month there will probably be no "splitting" and must be able to find the original meter number.
A proposal in VBA would be to examine each record and if there are multiple reads per recordX then use some date criteria to delete unwanted rows...is this possible


thanks for reading this


Josh

Ps i've looked at logic functions but i dont think it can be done that way....

 

[RickSpr]

I can't quite make out what you're saying. You said sometimes Record X needs to be "reset", and your example looks like two records, yet you say you can't make two records out of it.

So what does "splitting" mean? Do you add another column somehow? (Bad idea from the standpoint of relational database design.) Does the user do it or is it done in code?

You also seem to be saying that the "extra" differences from the query are not desired--is that right? If you want the query to pair only t1_X1 with t2_X2 and not t1_X1 with t2_X2, you'll either have to delete some (what? records?) with extraneous values or you'll have to add a column to the join that makes the combination of join fields unique.

Could you describe the table keys and the join fields, please? Rick Sprague