Query not updateable

[Guest]

I want to delete records in a table if the

Sum of GroupOnField1, GroupOnField2 , Sum(FieldAmount) = 0

I tried using queries to get the items that were zero and then another query
relating it back to the table and when I tried to delete it said not an
Updateable Query.

How would I accomplish this.

Thank you for your help.

Steven

 

[Douglas J. Steele]

As soon as you have an aggregate function such as Sum, the query cannot be
updated. I don't believe there's any way around this: since a single row in
the query can represent details from multiple rows, how would Access know
which row to update?

 

[David F Cox]

Do you have records with a field called something like [fieldamount] which
you want to delete if they are zero? Thsi is simply done with a delete query
with the criteria WHERE [fieldamount] =0, or just =0 in the criteria grid.
No grouping or summing is necessary.

or do you have records where if the amount in one is 10 and the amount in
the other is -10 then you want to delete both of them because their sum is
zero?

 

[Guest]

Yes, The second one... If I have one that is 10 and the other -10 then
delete both.

Do you have records with a field called something like [fieldamount] which
you want to delete if they are zero? Thsi is simply done with a delete query
with the criteria WHERE [fieldamount] =0, or just =0 in the criteria grid.
No grouping or summing is necessary.

or do you have records where if the amount in one is 10 and the amount in
the other is -10 then you want to delete both of them because their sum is
zero?

I want to delete records in a table if the

Sum of GroupOnField1, GroupOnField2 , Sum(FieldAmount) = 0

I tried using queries to get the items that were zero and then another
query
relating it back to the table and when I tried to delete it said not an
Updateable Query.

How would I accomplish this.

Thank you for your help.

Steven

 

[David F Cox]

Sorry, I do not have time now to test this.

Even if I have the syntax right I worry that it might delete the first
record and fail on the second because the total is no longer zero. (There
are still so many places I have not been.) Another worry is that it will
delete records with Nulls in the values, which may not be the intended
behaviour.

DELETE ....
WHERE (Select SUM([fields] FROM MyTable WHERE ..... ) = 0

Yes, The second one... If I have one that is 10 and the other -10 then
delete both.

Do you have records with a field called something like [fieldamount]
which
you want to delete if they are zero? Thsi is simply done with a delete
query
with the criteria WHERE [fieldamount] =0, or just =0 in the criteria
grid.
No grouping or summing is necessary.

or do you have records where if the amount in one is 10 and the amount in
the other is -10 then you want to delete both of them because their sum
is
zero?

I want to delete records in a table if the

Sum of GroupOnField1, GroupOnField2 , Sum(FieldAmount) = 0

I tried using queries to get the items that were zero and then another
query
relating it back to the table and when I tried to delete it said not an
Updateable Query.

How would I accomplish this.

Thank you for your help.

Steven

 

[Guest]

I'll give it a go. Thanks.

Sorry, I do not have time now to test this.

Even if I have the syntax right I worry that it might delete the first
record and fail on the second because the total is no longer zero. (There
are still so many places I have not been.) Another worry is that it will
delete records with Nulls in the values, which may not be the intended
behaviour.

DELETE ....
WHERE (Select SUM([fields] FROM MyTable WHERE ..... ) = 0

Yes, The second one... If I have one that is 10 and the other -10 then
delete both.

Do you have records with a field called something like [fieldamount]
which
you want to delete if they are zero? Thsi is simply done with a delete
query
with the criteria WHERE [fieldamount] =0, or just =0 in the criteria
grid.
No grouping or summing is necessary.

or do you have records where if the amount in one is 10 and the amount in
the other is -10 then you want to delete both of them because their sum
is
zero?


I want to delete records in a table if the

Sum of GroupOnField1, GroupOnField2 , Sum(FieldAmount) = 0

I tried using queries to get the items that were zero and then another
query
relating it back to the table and when I tried to delete it said not an
Updateable Query.

How would I accomplish this.

Thank you for your help.

Steven

 

[David F Cox]

I realised on my way out that the fear about Nulls was not justified. I ran
a little test and this query deleted the two records that summed to 0 :->

DELETE Table1.name1
FROM Table1
WHERE (((Table1.name1)=(SELECT name1
FROM Table1
GROUP BY name1
HAVING (Sum(Table1.qty))=0)));

I believe that a query along those lines will work for you.

I'll give it a go. Thanks.

Sorry, I do not have time now to test this.

Even if I have the syntax right I worry that it might delete the first
record and fail on the second because the total is no longer zero.
(There
are still so many places I have not been.) Another worry is that it will
delete records with Nulls in the values, which may not be the intended
behaviour.

DELETE ....
WHERE (Select SUM([fields] FROM MyTable WHERE ..... ) = 0

Yes, The second one... If I have one that is 10 and the other -10
then
delete both.

:

Do you have records with a field called something like [fieldamount]
which
you want to delete if they are zero? Thsi is simply done with a delete
query
with the criteria WHERE [fieldamount] =0, or just =0 in the criteria
grid.
No grouping or summing is necessary.

or do you have records where if the amount in one is 10 and the amount
in
the other is -10 then you want to delete both of them because their
sum
is
zero?


I want to delete records in a table if the

Sum of GroupOnField1, GroupOnField2 , Sum(FieldAmount) = 0

I tried using queries to get the items that were zero and then
another
query
relating it back to the table and when I tried to delete it said not
an
Updateable Query.

How would I accomplish this.

Thank you for your help.

Steven

 

[Guest]

David,

That worked great. Just one thing. I tried to Group on more than one
field. I tried many ways but could not make it work. How would you code it
to group on 2 fields and get the sum and if -0- then Delete.

Thank you for your help.

Steven.

 

[David F Cox]

Sorry to be so long in coming back.

My brain hurts. This is new territory for me.

One situation I had not covered was where more than two records summed to 0.
It is possible to test for this condition if required ... HAVING
(((Sum(Table1.Field1))=0) AND ((Sum(1))=2))

In order for Access to be able to delete a record Access must be able to
identify the record.

We cannot identify the records to be deleted by the key because we cannot
group by the key. We are deleting all of the records in a group. I should
have used IN (SELECT ...) as the test.

I believe that If we want to use more than one field to identify the
grouping we must generate a composite "group key"

If I am right we group on [field1] & [field2] & ....
and test on:
[field1] & [field2] & .... IN (SELECT [field1] & [field2] & .... FROM ....
GROUP BY Field1, Field2 ....

 

[Guest]

David,

Sorry your head started hurting. That was incredible. It worked perfect.
Thank you for checking back and helping me through this. I wish I had
something in return to reimburse you for all your effort and knowledge.

Thanks again.

Steven