Delete Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is posting I have posted in many new newsgroups. And after many
responses saying I can't do it. I would like a way around what I'm trying to
do this.

My posting:
I created many query to export out information to keep the working database
a little smaller. I would like to delete the information after I have expor
ted it out. I have be told many ways That I can't export from a joined tabl
e. But I'm hoping someone has an idea how I can delete this information.

I have created a query that show all the records form Material Estimation ta
ble that the job number matches in the Production Table, that the AssDate is
between the frm_Export_Data dates.

This the SQL view of the query.

DELETE [Material Estimation].*, Production.[AssDate]
FROM [Material Estimation] INNER JOIN Production ON [Material Estimation].[J
ob Number] = Production.[Job #]
WHERE (((Production.[AssDate]) Between [Forms]![frm_Export_Data]![FromDate]
And [Forms]![frm_Export_Data]![ToDate]));

I want to delete all the records in the Material Estimation table that show
up in this query.

Is this possible?

Thank you..

Please someone have an Idea!!!
 
Just change the DELETE clause to:

DELETE [Material Estimation].*
FROM [Material Estimation] INNER JOIN Production ... etc. ...

To test this query, substitute SELECT for DELETE: all the records you
want deleted should be in the returned dataset.

=====
Re: your private query - I don't answer unsolicited emails. It was just
chance I saw this posting. Here are some answers to your email.

BOL is the documentation for MS SQL Server. The MS SQL Server 2000
version can be found on this Microsoft web site (watch out for line
wrap):

http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-
445f-8a4b-bd77a7da194b&DisplayLang=en

The BOL will have info on SPs.
 
If I understand you correctly this is what you said to change.

Delete [Material Estimation].*
FROM [Material Estimation] INNER JOIN Production ON [Material
Estimation].[Job Number] = Production.[Job #]
WHERE (((Production.[Ass Date]) Between [Forms]![frm_Export_Data]![FromDate]
And [Forms]![frm_Export_Data]![ToDate]));

I changed it and I receive an error message of 'Could not delete from
specified table.' What did I do wrong or did I need to do something else.

Thanks for your help...

MGFoster said:
Just change the DELETE clause to:

DELETE [Material Estimation].*
FROM [Material Estimation] INNER JOIN Production ... etc. ...

To test this query, substitute SELECT for DELETE: all the records you
want deleted should be in the returned dataset.

=====
Re: your private query - I don't answer unsolicited emails. It was just
chance I saw this posting. Here are some answers to your email.

BOL is the documentation for MS SQL Server. The MS SQL Server 2000
version can be found on this Microsoft web site (watch out for line
wrap):

http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-
445f-8a4b-bd77a7da194b&DisplayLang=en

The BOL will have info on SPs.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

This is posting I have posted in many new newsgroups. And after many
responses saying I can't do it. I would like a way around what I'm trying to
do this.

My posting:
I created many query to export out information to keep the working database
a little smaller. I would like to delete the information after I have expor
ted it out. I have be told many ways That I can't export from a joined tabl
e. But I'm hoping someone has an idea how I can delete this information.

I have created a query that show all the records form Material Estimation ta
ble that the job number matches in the Production Table, that the AssDate is
between the frm_Export_Data dates.

This the SQL view of the query.

DELETE [Material Estimation].*, Production.[AssDate]
FROM [Material Estimation] INNER JOIN Production ON [Material Estimation].[J
ob Number] = Production.[Job #]
WHERE (((Production.[AssDate]) Between [Forms]![frm_Export_Data]![FromDate]
And [Forms]![frm_Export_Data]![ToDate]));

I want to delete all the records in the Material Estimation table that show
up in this query.

Is this possible?
Click to expand...
Click to expand...
 
I click on your link and it errored.

The download you requested is unavailable. If you continue to see this
message when trying to access this download, you might try the "Search for a
Download" area on the Download Center home page.

I'm going to try to see if I can find it out there.

Thanks for your help...

MGFoster said:
Just change the DELETE clause to:

DELETE [Material Estimation].*
FROM [Material Estimation] INNER JOIN Production ... etc. ...

To test this query, substitute SELECT for DELETE: all the records you
want deleted should be in the returned dataset.

=====
Re: your private query - I don't answer unsolicited emails. It was just
chance I saw this posting. Here are some answers to your email.

BOL is the documentation for MS SQL Server. The MS SQL Server 2000
version can be found on this Microsoft web site (watch out for line
wrap):

http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-
445f-8a4b-bd77a7da194b&DisplayLang=en

The BOL will have info on SPs.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

This is posting I have posted in many new newsgroups. And after many
responses saying I can't do it. I would like a way around what I'm trying to
do this.

My posting:
I created many query to export out information to keep the working database
a little smaller. I would like to delete the information after I have expor
ted it out. I have be told many ways That I can't export from a joined tabl
e. But I'm hoping someone has an idea how I can delete this information.

I have created a query that show all the records form Material Estimation ta
ble that the job number matches in the Production Table, that the AssDate is
between the frm_Export_Data dates.

This the SQL view of the query.

DELETE [Material Estimation].*, Production.[AssDate]
FROM [Material Estimation] INNER JOIN Production ON [Material Estimation].[J
ob Number] = Production.[Job #]
WHERE (((Production.[AssDate]) Between [Forms]![frm_Export_Data]![FromDate]
And [Forms]![frm_Export_Data]![ToDate]));

I want to delete all the records in the Material Estimation table that show
up in this query.

Is this possible?
Click to expand...
Click to expand...
 
Try putting DISTINCTROW after DELETE:

DELETE DISTINCTROW [Material Estimation].*
FROM ...

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

If I understand you correctly this is what you said to change.

Delete [Material Estimation].*
FROM [Material Estimation] INNER JOIN Production ON [Material
Estimation].[Job Number] = Production.[Job #]
WHERE (((Production.[Ass Date]) Between [Forms]![frm_Export_Data]![FromDate]
And [Forms]![frm_Export_Data]![ToDate]));

I changed it and I receive an error message of 'Could not delete from
specified table.' What did I do wrong or did I need to do something else.

Thanks for your help...

:

Just change the DELETE clause to:

DELETE [Material Estimation].*
FROM [Material Estimation] INNER JOIN Production ... etc. ...

To test this query, substitute SELECT for DELETE: all the records you
want deleted should be in the returned dataset.

=====
Re: your private query - I don't answer unsolicited emails. It was just
chance I saw this posting. Here are some answers to your email.

BOL is the documentation for MS SQL Server. The MS SQL Server 2000
version can be found on this Microsoft web site (watch out for line
wrap):

http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-
445f-8a4b-bd77a7da194b&DisplayLang=en

The BOL will have info on SPs.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)


JeffH13 wrote:

This is posting I have posted in many new newsgroups. And after many
responses saying I can't do it. I would like a way around what I'm trying to
do this.

My posting:
I created many query to export out information to keep the working database
a little smaller. I would like to delete the information after I have expor
ted it out. I have be told many ways That I can't export from a joined tabl
e. But I'm hoping someone has an idea how I can delete this information.

I have created a query that show all the records form Material Estimation ta
ble that the job number matches in the Production Table, that the AssDate is
between the frm_Export_Data dates.

This the SQL view of the query.

DELETE [Material Estimation].*, Production.[AssDate]
FROM [Material Estimation] INNER JOIN Production ON [Material Estimation].[J
ob Number] = Production.[Job #]
WHERE (((Production.[AssDate]) Between [Forms]![frm_Export_Data]![FromDate]
And [Forms]![frm_Export_Data]![ToDate]));

I want to delete all the records in the Material Estimation table that show
up in this query.

Is this possible?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top