HELP! Delete query with multiple criteria

  • Thread starter Thread starter Chris
  • Start date Start date
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?
 
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.
 
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...
 
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?
 
I have some entries in a table and some exits (with quantity in 'kg' and
length in 'm') . I want to delete from the table, for archiving, all the
records entered before a chosen date, if the remaining quantity in 'kg'
or lenght in 'm' is 0 (the sum of the entries is equal with the sum of
the exits).
Chris
 
Chris said:
I have some entries in a table and some exits (with quantity in 'kg' and
length in 'm') . I want to delete from the table, for archiving, all the
records entered before a chosen date, if the remaining quantity in 'kg'
or lenght in 'm' is 0 (the sum of the entries is equal with the sum of
the exits).
Chris
Click to expand...
 
I have some entries in a table and some exits (with quantity in 'kg' and
length in 'm') . I want to delete from the table, for archiving, all the
records entered before a chosen date, if the remaining quantity in 'kg'
or lenght in 'm' is 0 (the sum of the entries is equal with the sum of
the exits).
Click to expand...

I'm trying to understand what YOU mean by "SUM". You know the
structure of your table; you know what the "entries" and "exits" are.
I do not.

Are you adding values within a record?

Or are you adding up values stored in different records?

Please (as requested) post *AN EXAMPLE* of one or more records
describing which record should be deleted.
 
-----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.
 
Back
Top