Select Qry into a Delete Qry

[tekquest]

All,

I have a select query:

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()));

It will select and display the contacts with a certain criteria, being the last date of each contract + 7 years, as you can see above.

When this query gets run, it displays all the contracts in a table. I tried changing this to a Delete query, but when I try to run it, I get a "cant have aggregate function in WHERE clause"

Would it be possible to use another type of query that would move the data into another table? cause then it would remove it from the users view, and each time the query got run, it would delete the previous table anyway.

Any ideas? is it better to change the way my query is run? even turn it into a module? I have been working on this for days now and I don't seem to be moving..

Cheers

 

[namliam]

You cant Group records that you want to delete.... You will have to select individual records...

Hope that helps

Regards

 

[tekquest]

so what is my best option about getting around it and deleting the data?

 

[namliam]

SELECT RECTRANS.ContractNumber, RECTRANS.TransactionDate, DateAdd('yyyy',7,[RECTRANS].[TransactionDate]) FROM RECTRANS
where DateAdd('yyyy',7,[RECTRANS].[TransactionDate])<Now();

Try that...

(i simply took away the group by bit...)

What is your situation?? Is a ContractNumber Unique?

Regards