Delete Qry

[brucey54]

Hi folks, need some help here, I am trying to run a delete qry when patient treatment is complete.

I would like the qry to run if the Date Treatment Complete field is more than 28 days old, code below.


DELETE *
FROM tblPatient
WHERE EXISTS

(SELECT *

FROM tblReferral

WHERE tblPatient.PatientID = tblReferral.PatientFK AND ([Treatment Status]="Treatment Complete" AND [Date Treatment Complete]=Date()+28));

 

[jdraw]

Many developers would tell you to NOT physically delete a record. Instead, use a logically deleted field to signify the record is "deleted"/"no longer active" or whatever suits your set up.
Often a boolean (Yes/No) filed is used for this purpose.
eg
IsDeletedYN Boolean default is No
when a record is "deleted" from the application, the field is set to Yes.

Queries, forms and reports have to be aware of the status of this Boolean field.

Good luck.

 

[CJ_London]

the date function returns today

so this

[Date Treatment Complete]=Date()+28

is looking to delete records where the treatment is completed in 28 days time - which seems a bit prophetic...

suggest you need

[Date Treatment Complete]+28=Date()

or

[Date Treatment Complete]=Date()-28

I presume you are aware it is generally considered a bad idea to delete data - it is hard won. Better to have a Boolean flag called say 'markedDeleted' which defaults to false and set to true when you want to 'delete' it. Then modify your queries to exclude records where this flag is true

 

[brucey54]

Hi folks,

I am running append qry first then a delete qry, they want the data moved from one table to another table.

is possible to have the qry to run if [Date Treatment Complete] field is more than 28 day old.

The code below only runs if the [Date Treatment Complete] field is exactly 28 day old...

[Date Treatment Complete]+28=Date()

 

[plog]

Agree with jdraw, but I go further: Action queries (APPEND, DELETE, UPDATE) are huge red flags that you are doing something wrong. You've now stated you are using 2 different types of actions to maintain your data.

Why are you performing these action queries on your data? From what are you appending? And why not just flag your data instead of deleting it? I think these are symptoms of a larger issue.

 

[brucey54]

Why would you want to leave old data within your main table and not achieve to another table. My line of thought is keeping old data mixed in with current data would slow the database down. Why would you not want to append it to another table then delete it from the main table???

 

[plog]

A table is nothing special, just a space in memory to store data. You make sense of the data via queries not by making new tables for every "type". "Old" is a characteristic off the data, not a whole new set. And you don't make new tables just for every characteristic:

If you had a company directory you wouldn't have a table for Accounting people, Sales people and Tech people, etc.. They would all go together in the same Directory table and the department they worked for (characteristic) would be stored in a field to designate them as such.

Same thing with "Old" data. Its just a characteristic of the data so it goes in the same table.

 

[brucey54]

Thank Plog, I need to play devil’s advocate here, if your table had let’s say over 2 million records and you could achieve 40% of the records to another table.
This would optimise your database/main table i.e. the query would not need to run through 100% of the records to check if it is flag or not.
I have been taught that the main table that the queries work with should be optimised as much as possible i.e. remove old data into achieve table out of the way. Interested to hear you thoughts on this...

 

[plog]

I agree at some point, Twitter, Visa, IRS has to do optimization on their databases that might break normalization. But I have not encountered that in an Access database.

I have done archiving myself, but it was because I was coming up on the 2 gig limit, not because queries were running slow. If you properly assign indexes, choose the correct data types and normalize I don't think your scenario is likely to occur.

I'm not saying it takes the same time to run a query on 2 million records as it does 800k. I'm saying it will be either negligible or the scenario in which it occurs is an anomaly.

 

[CJ_London]

the query would not need to run through 100% of the records to check if it is flag or not

That is where indexing comes in. See these links

https://support.office.com/en-US/Article/Create-and-use-an-index-to-improve-performance-0a8e2aa6-735c-4c3a-9dda-38c6c4f1a0cehttps://support.office.com/en-US/Ar...formance-0a8e2aa6-735c-4c3a-9dda-38c6c4f1a0cehttps://support.office.com/en-US/Ar...formance-0a8e2aa6-735c-4c3a-9dda-38c6c4f1a0ce

https://technet.microsoft.com/en-us/library/ms191195(v=sql.105).aspx

 

[brucey54]

Thank you, interesting reading