HELP! Delete query with multiple criteria

C

Chris

I have to make a delete query with multiple criteria (3). Somethhing like
this:

DELETE table.*, table.Data, Sum([dim1])-Sum([dim2]) AS Dif1,
Sum([quantity])-Sum([quantity]-[quantity2]) AS Dif2
FROM table
WHERE (((table.Data)<=[FORMS]![Archiving]![DataArh]) AND
((Sum([dim1])-Sum([dim2]))=0)) OR
(((Sum([quantity])-Sum([quantity-quantity2]))=0));

but access does not accept agregate funtions in a delete query

Can you please HELP me?
 
J

John Vinson

I have to make a delete query with multiple criteria (3). Somethhing like
this:

DELETE table.*, table.Data, Sum([dim1])-Sum([dim2]) AS Dif1,
Sum([quantity])-Sum([quantity]-[quantity2]) AS Dif2
FROM table
WHERE (((table.Data)<=[FORMS]![Archiving]![DataArh]) AND
((Sum([dim1])-Sum([dim2]))=0)) OR
(((Sum([quantity])-Sum([quantity-quantity2]))=0));

but access does not accept agregate funtions in a delete query

Can you please HELP me?
Click to expand...

Any query containing a Sum operation will not be updateable, since the
value in the sum does not come from a single record. What are you
trying to accomplish here? Do you really want to Sum these values
across all records in the table? If so, what record or records do you
want to delete - all the records in the table if the sum is zero? Or
are you just trying to compare the values of Dim1 and Dim2, Quantity
and Quantity2 within each record? If so you don't need the Sum, which
sums across multiple records.
 
C

Chris

I want to delete all the records in the table if any sum from the 2 (dim or
quantity) is 0.
Can you help me?

John Vinson said:
I have to make a delete query with multiple criteria (3). Somethhing like
this:

DELETE table.*, table.Data, Sum([dim1])-Sum([dim2]) AS Dif1,
Sum([quantity])-Sum([quantity]-[quantity2]) AS Dif2
FROM table
WHERE (((table.Data)<=[FORMS]![Archiving]![DataArh]) AND
((Sum([dim1])-Sum([dim2]))=0)) OR
(((Sum([quantity])-Sum([quantity-quantity2]))=0));

but access does not accept agregate funtions in a delete query

Can you please HELP me?
Click to expand...

Any query containing a Sum operation will not be updateable, since the
value in the sum does not come from a single record. What are you
trying to accomplish here? Do you really want to Sum these values
across all records in the table? If so, what record or records do you
want to delete - all the records in the table if the sum is zero? Or
are you just trying to compare the values of Dim1 and Dim2, Quantity
and Quantity2 within each record? If so you don't need the Sum, which
sums across multiple records.
Click to expand...
 
J

John Vinson

I want to delete all the records in the table if any sum from the 2 (dim or
quantity) is 0.
Click to expand...

You'll need to explain this. SUM() sums across multiple records. Is
that what you mean? Could you give an example of a few rows of the
table and indicate which records should be deleted?
 
G

Guest

-----Original Message-----
I have to make a delete query with multiple criteria (3). Somethhing like
this:

DELETE table.*, table.Data, Sum([dim1])-Sum([dim2]) AS Dif1,
Sum([quantity])-Sum([quantity]-[quantity2]) AS Dif2
FROM table
WHERE (((table.Data)<=[FORMS]![Archiving]![DataArh]) AND
((Sum([dim1])-Sum([dim2]))=0)) OR
(((Sum([quantity])-Sum([quantity-quantity2]))=0));

but access does not accept agregate funtions in a delete query

Can you please HELP me?

Hi There
Click to expand...

If you use ADO, you can use VBA code to create these
aggregates before you run the query! Check recordset and
parameters in ADO help files.
 
Top