Select query, conversion to delete query

[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 &quot;cant have aggregate function in WHERE clause&quot;

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? I have been working on this for days now and I don't seem to be moving..

Luke-

 

[KenReay]

Hi

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

I think should do it



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[tekquest]

Hi Ken!

Thanks for the ultrafast response!!!

I have changed the SQL statement to yours, but when I run the delete query, I get an error:

Missing ), ], or Item in query expression 'contractnumber IN ((Select RECTRANS.ContractNumber
FROM RECTRANS
GROUP BY RECTRANS.ContractNumber
HAVING (((DateAdd('yyyy',7,Max([RECTRANS].[TransactionDate])))<Now()));

Any ideas as to why? I changed the )'s around, but didn't seem to get anywhere!! :-(

Thanks heaps for the reply, much appreciated

 

[KenReay]

Hi

Oops, thing I missed a ) off the end, should be

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


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[tekquest]

Hi Ken,

Thanks so much, that worked perfectly!!! it deleted all the records that were 7+ years old from the last date, as it did in the select query.

I have one more question, which is:

If the Table &quot;Rectrans&quot; holds the Contract number and a transaction date, could I possibly link the other tables to it (e.g. ADV) so if there is an item in Rectrans that is 7 years old to be deleted, it will also kill the same contract number from ADV? (contract records are kept in two seperate places, depending on the type of contract no)

Thanks again so much.

 

[KenReay]

Hi

If you declared a relationship between teh tables, and set Cascade delete in the properties of the relationship, then it will delete the records in the 'child' table (ADV), but I am not sure that is what you are getting at

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[tekquest]

Unfortunately I can't do that, due to violations of referential integrity! But yes, I do want it to delete the related (by contract no) records in the child tables like ADV

Is there any other way around this problem?

Thanks

 

[KenReay]

Hi

Only thing I can think of is to write yourself another SQL statement which deletes all from ADV where the ContractId is not present in RecTrans, something like

DELETE FRON ADV WHERE ContractNumber NOT IN (SELECT ContractNumber FROM RecTrans);

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

Sorry must improve my typing!!

should be

DELETE FROM ADV WHERE ContractNumber NOT IN (SELECT ContractNumber FROM RecTrans);

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[tekquest]

Hi Ken,

I just tried that, it deletes the items from the child tables that are not in the RECTRANS, but when I try to add the ref integrity, it errors with the same message.

Can I please email you a picture of the relationships? i would post the picture but i cant here.

Thanks

 

[KenReay]

Hi

OK kenneth.reayREMOVETHIS@talk21.com

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[tekquest]

mate, couldnt get the message through, said that your a/c is temporarily over quota! let me know when I can send through..

 

[KenReay]

Hi

Just keep trying, it is all of the SPAM !

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK