Delete all after the first cancelled record.

[buzzbait9]

Hello World,

I seem to be confusing in my posts so I'm going to try this one in steps

I have a cancelled service that gives a -1 to an ISCANNED field for each EventDate after the date the services are cancelled.

Now I am permitted to Delete all of the cancelled records except the Min() date that =-1.

I built a query to find the value for the First Event date that =-1. I then built a delete query to delete all records after the First Event date that =-1 but it had to Join the "Find the value of the First query" to get the table records to show the records that were going to be deleted.

It cannot delete.

Is there a module or VB that can be used to make the delete query work without using the joined "Find the value of the First query"?

Something that will say "delete all the records after the first cancelled record"

Thanks

 

[jdraw]

I suggest you show the query SQL view.
It's often easier to communicate by telling readers WHAT you want to accomplish in plain English -- no Access jargon.

This is saying HOW you have done something, and the something is not crystal clear, yet.
eg -1 to an ISCANNED field for each EventDate

You'll get options for HOW, once readers understand WHAT.

Many developers will never DELETE a record physically. They will flag it along this line:
IsDeletedYN = Y
Quite often (maybe not in your case) auditors/others will want to see all the data. Deleting a record --removes it, forever.
Good luck.

 

[buzzbait9]

Query for First cancelled (ISCANNED=-1) event date.

SELECT MyTable.TagNo, First(MyTable.EventDate) AS FirstOfEventDate
FROM MyTable
WHERE (((MyTable.ISCANNED)=-1))
GROUP BY MyTable.TagNo;

Delete query using above query

DELETEMyTable.*, MyTable.ISCANNED, MyTable.EventDate, MyTable.WorkOrderNumber
FROM Mycancelquery LEFT JOIN MyTable
ON Mycancelquery.TagNo = MyTable.TagNo
WHERE (((MyTable.ISCANNED)=-1) AND ((MyTable.EventDate)>[FirstOfEventDate]) AND ((MyTable.WorkOrderNumber) Is Null));

Thank you

 

[jdraw]

Does First in your set up mean the lowest/oldest EventDate?

If so, you should use Min(EventDate). There have been articles on use of First and Last.
-1 in a Boolean field is equivalent to Yes.
What is the data type of ISCANNED?

What happens when you try to execute this

DELETEMyTable.*, MyTable.ISCANNED, MyTable.EventDate, MyTable.WorkOrderNumber
FROM Mycancelquery LEFT JOIN MyTable
ON Mycancelquery.TagNo = MyTable.TagNo
WHERE (((MyTable.ISCANNED)=-1) AND ((MyTable.EventDate)>[FirstOfEventDate]) AND ((MyTable.WorkOrderNumber) Is Null));

Can you please give a 3-4 line description of WHAT you are trying to do?
What is an Event....? What is a Service? How do Services and Events relate?

 

[buzzbait9]

The services(Event) are maintenence. they are all generated as soon as the equipment arrives. When they are no longer required, they are cancelled. The EventDate is the date generated for the service. When they are cancelled, they are ID'd by ISCANNED and the value becomes -1 which is only a number value. Sometimes they are casncelled after a Wworkorder is already finished so I can't delete that date.

What I am trying to do is keep the first service(Event) that was cancelled and delete all of the leftovers. As you can see I got my First() from the totals query. I can change thqt to Min() if need be.

Could not delete from the specified table is the error

Thank you

 

[jdraw]

??
Step back. Pretend you are talking to a child - we don't know you or your environment, and I certainly am not clairvoyant.

Where does this fit?

The services(Event) are maintenence. they are all generated as soon as the equipment arrives.

What equipment? Arrives from where????

Here's an example of a description to use to build a sample database.
From RogersAccessLibrary

[COLOR="Purple"][I]Narrative
ZYX Laboratories requires an employee tracking database. They want to track
 information about employees, the employee's job history, and their certifications.
 Employee information includes first name, middle initial, last name, social
 security number, address, city, state, zip, home phone, cell phone, email address.
 Job history would include job title, job description, pay grade, pay range,
 salary, and date of promotion. For certifications, they want certification type
 and date achieved. 
An employee can have multiple jobs over time, (ie, Analyst, Sr. Analyst,
 QA Administrator). Employees can also earn certifications necessary 
for their job. [/I][/COLOR]

 

[buzzbait9]

The data Base is designed to create Maintenence on equipment. When the equipment arrives it is added to the data base and assigned Maintenence services. Each service is assigned with a frequency. every week for 156 weeks, every 2 weeks for 78 weeks, etc. The Services, called events, are dated(EventDates). All Event dates are marked with a field called ISCANNED 0= do the service and -1= service has been cancelled.

When the Service is no longer required, the service is cancelled. This leaves all the remaining services in the Data base listed as cancelled. Since I only need to have the services that were completed and one cancelled eventdate for the auditors, I want to keep the services that were completed and the first service EventDate that was cancelled. The rest would be removed from the database.

 

[Galaxiom]

You need a subquery for the Where condition.

DELETE * FROM MyTable
WHERE TagNo IN ( query to select TagNo to delete)

The subquery must only return the TagNo field.