Delete Query Question

[cori25]

Is it possible to delete data from one select query from a table?

Example:

I want to delete Query_SignedUp from TableAvailabili ty. The fields are the same I just can not figure out if this is possible in a delete query.

Any help or suggestions greatly appreciated.

Thanks
-Cori

[Stewart Ross]

Hi Cori. If TableAvailabili ty has a single key field (replicated in Query_SignedUp) it can be done using Query_SignedUp as a subquery:
[code=sql]DELETE * from TableAvailabili ty
WHERE [TableAvailabili ty].[idfieldname] IN
(SELECT [idfieldname] FROM Query_Signedup) ;[/code]

-Stewart

[cori25]

Thanks for the response but this will not work in my case. It is a little more complicated then I explained. Basically, I have employee's who sign up for OT shifts(tbl_Sign edUp). The shifts are located in tblAvailability . Query_SignedUp has both of these tables and only includes data that has the same date and shift. Meaning, if it has been signed up for that it should no longer be present in tblAvailability . So I need to have the the fields from this query deleted from tblAvailability .

With your suggestion, I would need to have only one key field. I am still working on finding a solution so if you have any other thoughts on this, I appreciate it.

Thanks again,
Cori

[Stewart Ross]

...It is a little more complicated then I explained...
With your suggestion, I would need to have only one key field. I am still working on finding a solution so if you have any other thoughts on this, I appreciate it....

Hi Cori. Reason for the one field limitation is the use of the subquery, where one field value fom the main query is being tested against the field returned by the SELECT subquery.

One way to progress by using a subquery is to concatenate fields so that one, unique, value is being returned that can be matched to both the availability table and whatever tables you query to get the list of entries you wish to delete. It means adding a new calculated field to the subquery that concatenates the relevant fields that would identify an entry from your availability table. You would also have to create a new query based on your availability table that includes the same type of calculated field. You need this new query for the DELETE based on matching the concatenated field from your subquery to the calculated field based on the availability table. I do not know what these fields are or I would simply show the concatenation of them for you.

The general form of the SQL remains just what was shown before, but with the difference that instead of a single key field you refer to the concatenated field in each query:
[code=sql]DELETE * from [qryTableAvailab ility]
WHERE [qryTableAvailab ility].[concatenatedfie ld] IN
(Select [otherquery].[concatenatedfie ld] FROM otherquery);[/code]
-Stewart

[cori25]

Hi Stewart,

The fields are Date, Shift, Total Out, Month, Day. I understand where you are going with the concatenated field. Now the next problem I did not think of until actually attempting this was that the Total Out field represents if there is more then 1 shift available. Meaning that if I have 2 of the same date,shift, ect. available. So if I have 1 shift signed up for all I want is for the Total Out field to subtract 1. Therefore, I would want this:

Date Shift Total Out Month Day
4/26/1008 8-6 2 2008-04-Apr Sat

To delete out 1 of these shifts:

Date Shift Total Out Month Day
4/26/1008 8-6 1 2008-04-Apr Sat

Otherwise, if the Total out field equals 1 then the entire row would be deleted.

Does this make sense to you?

I really appreciate your help!

[Stewart Ross]

... Now the next problem I did not think of until actually attempting this was that the Total Out field represents if there is more then 1 shift available. Meaning that if I have 2 of the same date,shift, ect. available. So if I have 1 shift signed up for all I want is for the Total Out field to subtract 1. ...

Sorry, Cori, but this last change in the requirements complicates matters. It is not possible to have a delete query which just alters the value of a field instead.

In the circumstances either two sets of queries need to be developed - one which will delete where there is a match and total out = 1, and one which will update the total out values where there is a match and total out > 1 - or a programmed approach will be needed.

It would certainly be possible to develop an update query using the same approach as outlined for the delete query, using a subquery to select the rows for deletion. With the two-query approach you could simply create a macro to run them one after the other if you wanted to ensure they were always run together.

As an aside, it is not normal practice to store calculated values as fields in a table - you can see the problems that having the value physically stored instead of calculated causes in this case.

I wish you well with resolving these issues.

Regards

Stewart

[cori25]

Thanks so much for the great feedback! Too bad access can't do everything we want, haha :)

-Cori

[wireshark]

Thanks so much for the great feedback! Too bad access can't do everything we want, haha :)

-Cori

just a suggestion....
it would be better if you make one field automatic number. and u can delete by that automatic number. get the record(Select) and delete for AutomaticNumber ='99'.
just something like that.... :)