Delete Query, was select query and was working!

[tekquest]

I have a issue in Access with my query, it used to be a "Select" query, that would grab all data that was 7+ years old.

Heres the code:

SELECT RECTRANS.ContractNumber, Max(RECTRANS.TransactionDate) AS MaxOfTransactionDate, DateAdd('yyyy',7,Max([RECTRANS].[TransactionDate])) AS Expr1
FROM RECTRANS
GROUP BY RECTRANS.ContractNumber
HAVING (((DateAdd('yyyy',7,Max([RECTRANS].[TransactionDate])))<Now()));

---- I want this to delete these records it displays, so I changed it to &quot;DELETE&quot;

DELETE RECTRANS.ContractNumber, Max(RECTRANS.TransactionDate) AS MaxOfTransactionDate, DateAdd('yyyy',7,Max([RECTRANS].[TransactionDate])) AS Expr1
FROM RECTRANS
GROUP BY RECTRANS.ContractNumber
HAVING (((DateAdd('yyyy',7,Max([RECTRANS].[TransactionDate])))<Now()));

Now, when I run the query, it gives an error &quot;Can't group fields selected with '*'. I cannot seem to find this '*', I must be missing something, or is the line &quot;FROM RECTRANS&quot; being seen as the item with the '*' on the end of it???

Thanks for reading

 

[Transcend]

Well first of all with a delete query you don't need the select list. I don't know what your table structure is so I can't test this but you should just have something like

DELETE FROM RECTRANS
Where ... etc etc

Transcend

 

[tekquest]

I have gone into design view of the query, and changed it from a &quot;select query&quot; into a &quot;delete query&quot;, I noticed it has changed the SQL statement to:

DELETE RECTRANS.ContractNumber, RECTRANS.TransactionDate, DateAdd('yyyy',7,Max([RECTRANS].[TransactionDate])) AS Expr1
FROM RECTRANS
WHERE (((DateAdd('yyyy',7,Max([RECTRANS].[TransactionDate])))<Now()));

Now when I run it, I get the error message: Can't have an aggregate function in the WHERE clause (DateAdd('yyyy',7,Max,([RECTRANS].[TransactionDate]))<Now()).

The delete query is supposed to delete the LAST contract in my table, based on its date.

E.g. If John smith has 2000 entries in the table, his last entry was 1.1.2000, then on the 1.1.2007, all his records can be deleted, based on the &quot;MaxOfTransactionDate&quot; in my select query.

Heres a thought, maybe I need a seperate delete query!!! at present, when its configured for &quot;Select&quot; it will display all the relivant records in the table (e.g. ones that are +7 years from the last date entered)

What is the best approach for this second query? e.g. I know I need to create a new query, that is a delete query, but where is it going to pull its data to delete from?

Thanks for the response

 

[Transcend]

Ok well this is a guess - and make sure you test this out on a copy if you want to keep you data but have you tried something like:

DELETE
FROM RECTRANS
WHERE RECTRANS.ContractNumber in
(SELECT RECTRANS.ContractNumber, Max(RECTRANS.TransactionDate) AS MaxOfTransactionDate, DateAdd('yyyy',7,Max([RECTRANS].[TransactionDate])) AS Expr1
FROM RECTRANS
GROUP BY RECTRANS.ContractNumber
HAVING (((DateAdd('yyyy',7,Max([RECTRANS].[TransactionDate])))<Now()));
)

Transcend

 

[Transcend]

Uh actually no that won't work I don't think ... I need to think about this some more

anyone else?

Trancend