Delete Query

  • Thread starter Thread starter JeffH
  • Start date Start date
J

JeffH

I have create a delete query that is base on two tables.
When I run the query as a Select Query and show datasheet
it shows all the records I want to delete. Change it to
a delete query and look in datasheet it show all the
records I want to delete, But when I run it it comes up
with a error message of 'Could not delete from specified
tables.'

Field: Labor Per Job.* | Ass Date
Table: Labor Per Job | Production
Delete: From | Where
Criteria: | Between [Forms]!
[frm_Export_Data]![FromDate]and[Forms]![frm_Export_Data]!
[ToDate]

I have a frm_Export_Data open with FromDate field and the
ToDate field having information in them.

I have been using the form method to export out
information from many table in another database and its
been working very well.

Now I need to delete all the information that has been
exported out.

Does anyone have any Ideas????

Thanks!!!!!
 
For a delete query, you can have only one source table. So you must use a
subquery for the "joined" table. Post the SQL of the select query that
works, and what you want to delete, and we'll show you how to modify the
query.
 
OK...

How do I post the SQL of the select query?

Thanks.
-----Original Message-----
For a delete query, you can have only one source table. So you must use a
subquery for the "joined" table. Post the SQL of the select query that
works, and what you want to delete, and we'll show you how to modify the
query.

--

Ken Snell
<MS ACCESS MVP>

JeffH said:
I have create a delete query that is base on two tables.
When I run the query as a Select Query and show datasheet
it shows all the records I want to delete. Change it to
a delete query and look in datasheet it show all the
records I want to delete, But when I run it it comes up
with a error message of 'Could not delete from specified
tables.'

Field: Labor Per Job.* | Ass Date
Table: Labor Per Job | Production
Delete: From | Where
Criteria: | Between [Forms]!
[frm_Export_Data]![FromDate]and[Forms]! [frm_Export_Data]!
[ToDate]

I have a frm_Export_Data open with FromDate field and the
ToDate field having information in them.

I have been using the form method to export out
information from many table in another database and its
been working very well.

Now I need to delete all the information that has been
exported out.

Does anyone have any Ideas????

Thanks!!!!!
Click to expand...


.
Click to expand...
 
Open the query in design view; go to View SQL View, copy the text and paste.
OK...

How do I post the SQL of the select query?

Thanks.
-----Original Message-----
For a delete query, you can have only one source table. So you must
use a subquery for the "joined" table. Post the SQL of the select
query that works, and what you want to delete, and we'll show you
how to modify the query.

--

Ken Snell
<MS ACCESS MVP>

JeffH said:
I have create a delete query that is base on two tables.
When I run the query as a Select Query and show datasheet
it shows all the records I want to delete. Change it to
a delete query and look in datasheet it show all the
records I want to delete, But when I run it it comes up
with a error message of 'Could not delete from specified
tables.'

Field: Labor Per Job.* | Ass Date
Table: Labor Per Job | Production
Delete: From | Where
Criteria: | Between [Forms]!
[frm_Export_Data]![FromDate]and[Forms]! [frm_Export_Data]!
[ToDate]

I have a frm_Export_Data open with FromDate field and the
ToDate field having information in them.

I have been using the form method to export out
information from many table in another database and its
been working very well.

Now I need to delete all the information that has been
exported out.

Does anyone have any Ideas????

Thanks!!!!!
Click to expand...


.
Click to expand...
Click to expand...
 
Thanks.

DELETE [Material Estimation].*, Production.[Ass Date]
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]));

The query find all the data that is found in the Material
Estimation table that matches the job#'s that have an
Assemble Date from the Export_Data Form.

I'm trying to delete the data in the material Estimation
table.

Does this make sense?
-----Original Message-----
Open the query in design view; go to View SQL View, copy the text and paste.
OK...

How do I post the SQL of the select query?

Thanks.
-----Original Message-----
For a delete query, you can have only one source table. So you must
use a subquery for the "joined" table. Post the SQL of the select
query that works, and what you want to delete, and we'll show you
how to modify the query.

--

Ken Snell
<MS ACCESS MVP>

I have create a delete query that is base on two tables.
When I run the query as a Select Query and show datasheet
it shows all the records I want to delete. Change it to
a delete query and look in datasheet it show all the
records I want to delete, But when I run it it comes up
with a error message of 'Could not delete from specified
tables.'

Field: Labor Per Job.* | Ass Date
Table: Labor Per Job | Production
Delete: From | Where
Criteria: | Between [Forms]!
[frm_Export_Data]![FromDate]and[Forms]! [frm_Export_Data]!
[ToDate]

I have a frm_Export_Data open with FromDate field and the
ToDate field having information in them.

I have been using the form method to export out
information from many table in another database and its
been working very well.

Now I need to delete all the information that has been
exported out.

Does anyone have any Ideas????

Thanks!!!!!




.
Click to expand...
Click to expand...

--
Joan Wild
Microsoft Access MVP



.
Click to expand...
 
To do a deletion, you have to use a subquery instead of joined tables. Try
this (note: test on a copy of your data!!!!):

DELETE [Material Estimation].*
FROM [Material Estimation]
WHERE [Material Estimation].[Job Number] IN
(SELECT Production.[Job #]
FROM Production
WHERE Production.[Ass Date] Between [Forms]!
[frm_Export_Data]![FromDate] And [Forms]!
[frm_Export_Data]![ToDate]);

--

Ken Snell
<MS ACCESS MVP>


JeffH said:
Thanks.

DELETE [Material Estimation].*, Production.[Ass Date]
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]));

The query find all the data that is found in the Material
Estimation table that matches the job#'s that have an
Assemble Date from the Export_Data Form.

I'm trying to delete the data in the material Estimation
table.

Does this make sense?
-----Original Message-----
Open the query in design view; go to View SQL View, copy the text and paste.
OK...

How do I post the SQL of the select query?

Thanks.

-----Original Message-----
For a delete query, you can have only one source table. So you must
use a subquery for the "joined" table. Post the SQL of the select
query that works, and what you want to delete, and we'll show you
how to modify the query.

--

Ken Snell
<MS ACCESS MVP>

I have create a delete query that is base on two tables.
When I run the query as a Select Query and show datasheet
it shows all the records I want to delete. Change it to
a delete query and look in datasheet it show all the
records I want to delete, But when I run it it comes up
with a error message of 'Could not delete from specified
tables.'

Field: Labor Per Job.* | Ass Date
Table: Labor Per Job | Production
Delete: From | Where
Criteria: | Between [Forms]!
[frm_Export_Data]![FromDate]and[Forms]! [frm_Export_Data]!
[ToDate]

I have a frm_Export_Data open with FromDate field and the
ToDate field having information in them.

I have been using the form method to export out
information from many table in another database and its
been working very well.

Now I need to delete all the information that has been
exported out.

Does anyone have any Ideas????

Thanks!!!!!




.
Click to expand...

--
Joan Wild
Microsoft Access MVP



.
Click to expand...
Click to expand...
 
Ok. Looks good.

When I copied and ran it, it errored.

The message I got was:

Invalid use of '.','!',or'()'. in query
expression 'Production.[Ass.Date] Between [Forms]!
[frm_Export_Data]And[Forms]![frm_Export_Data]![ToDate]'.

So quess the expression isn't right? I have been trying
different bracketing I haven't got figured out yet.

Thanks for your help...
-----Original Message-----
To do a deletion, you have to use a subquery instead of joined tables. Try
this (note: test on a copy of your data!!!!):

DELETE [Material Estimation].*
FROM [Material Estimation]
WHERE [Material Estimation].[Job Number] IN
(SELECT Production.[Job #]
FROM Production
WHERE Production.[Ass Date] Between [Forms]!
[frm_Export_Data]![FromDate] And [Forms]!
[frm_Export_Data]![ToDate]);

--

Ken Snell
<MS ACCESS MVP>


JeffH said:
Thanks.

DELETE [Material Estimation].*, Production.[Ass Date]
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]));

The query find all the data that is found in the Material
Estimation table that matches the job#'s that have an
Assemble Date from the Export_Data Form.

I'm trying to delete the data in the material Estimation
table.

Does this make sense?
-----Original Message-----
Open the query in design view; go to View SQL View,
Click to expand...
copy
the text and paste.
JeffH wrote:
OK...

How do I post the SQL of the select query?

Thanks.

-----Original Message-----
For a delete query, you can have only one source table. So you must
use a subquery for the "joined" table. Post the SQL of the select
query that works, and what you want to delete, and we'll show you
how to modify the query.

--

Ken Snell
<MS ACCESS MVP>

I have create a delete query that is base on two tables.
When I run the query as a Select Query and show datasheet
it shows all the records I want to delete. Change it to
a delete query and look in datasheet it show all the
records I want to delete, But when I run it it
Click to expand...
comes
up
with a error message of 'Could not delete from specified
tables.'

Field: Labor Per Job.* | Ass Date
Table: Labor Per Job | Production
Delete: From | Where
Criteria: | Between [Forms]!
[frm_Export_Data]![FromDate]and[Forms]! [frm_Export_Data]!
[ToDate]

I have a frm_Export_Data open with FromDate field and the
ToDate field having information in them.

I have been using the form method to export out
information from many table in another database
Click to expand...
and
its
been working very well.

Now I need to delete all the information that has been
exported out.

Does anyone have any Ideas????

Thanks!!!!!




.

--
Joan Wild
Microsoft Access MVP



.
Click to expand...
Click to expand...


.
Click to expand...
 
You don't have spaces on either side of the And word. Add the spaces.

--

Ken Snell
<MS ACCESS MVP>

JeffH said:
Ok. Looks good.

When I copied and ran it, it errored.

The message I got was:

Invalid use of '.','!',or'()'. in query
expression 'Production.[Ass.Date] Between [Forms]!
[frm_Export_Data]And[Forms]![frm_Export_Data]![ToDate]'.

So quess the expression isn't right? I have been trying
different bracketing I haven't got figured out yet.

Thanks for your help...
-----Original Message-----
To do a deletion, you have to use a subquery instead of joined tables. Try
this (note: test on a copy of your data!!!!):

DELETE [Material Estimation].*
FROM [Material Estimation]
WHERE [Material Estimation].[Job Number] IN
(SELECT Production.[Job #]
FROM Production
WHERE Production.[Ass Date] Between [Forms]!
[frm_Export_Data]![FromDate] And [Forms]!
[frm_Export_Data]![ToDate]);

--

Ken Snell
<MS ACCESS MVP>


JeffH said:
Thanks.

DELETE [Material Estimation].*, Production.[Ass Date]
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]));

The query find all the data that is found in the Material
Estimation table that matches the job#'s that have an
Assemble Date from the Export_Data Form.

I'm trying to delete the data in the material Estimation
table.

Does this make sense?

-----Original Message-----
Open the query in design view; go to View SQL View, copy
the text and paste.

JeffH wrote:
OK...

How do I post the SQL of the select query?

Thanks.

-----Original Message-----
For a delete query, you can have only one source
table. So you must
use a subquery for the "joined" table. Post the SQL
of the select
query that works, and what you want to delete, and
we'll show you
how to modify the query.

--

Ken Snell
<MS ACCESS MVP>

I have create a delete query that is base on two
tables.
When I run the query as a Select Query and show
datasheet
it shows all the records I want to delete. Change
it to
a delete query and look in datasheet it show all the
records I want to delete, But when I run it it comes
up
with a error message of 'Could not delete from
specified
tables.'

Field: Labor Per Job.* | Ass Date
Table: Labor Per Job | Production
Delete: From | Where
Criteria: | Between [Forms]!
[frm_Export_Data]![FromDate]and[Forms]!
[frm_Export_Data]!
[ToDate]

I have a frm_Export_Data open with FromDate field
and the
ToDate field having information in them.

I have been using the form method to export out
information from many table in another database and
its
been working very well.

Now I need to delete all the information that has
been
exported out.

Does anyone have any Ideas????

Thanks!!!!!




.

--
Joan Wild
Microsoft Access MVP



.
Click to expand...


.
Click to expand...
Click to expand...
 
I checked and there spaces in there.
This is what I have.

DELETE [Material Estimation].*
FROM [Material Estimation]
WHERE [Material Estimation].[Job Number] IN
(SELECT Production.[Job #]
FROM Production
WHERE Production.[Ass Date] Between [Forms]!
[frm_Export_Data]![FromDate] And [Forms]!
[frm_Export_Data]![ToDate]);

I deleted them and put them back in. I get the same
message.

Thanks
-----Original Message-----
You don't have spaces on either side of the And word. Add the spaces.

--

Ken Snell
<MS ACCESS MVP>

JeffH said:
Ok. Looks good.

When I copied and ran it, it errored.

The message I got was:

Invalid use of '.','!',or'()'. in query
expression 'Production.[Ass.Date] Between [Forms]!
[frm_Export_Data]And[Forms]![frm_Export_Data]! [ToDate]'.

So quess the expression isn't right? I have been trying
different bracketing I haven't got figured out yet.

Thanks for your help...
-----Original Message-----
To do a deletion, you have to use a subquery instead
Click to expand...
of
joined tables. Try
this (note: test on a copy of your data!!!!):

DELETE [Material Estimation].*
FROM [Material Estimation]
WHERE [Material Estimation].[Job Number] IN
(SELECT Production.[Job #]
FROM Production
WHERE Production.[Ass Date] Between [Forms]!
[frm_Export_Data]![FromDate] And [Forms]!
[frm_Export_Data]![ToDate]);

--

Ken Snell
<MS ACCESS MVP>


Thanks.

DELETE [Material Estimation].*, Production.[Ass Date]
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]));

The query find all the data that is found in the Material
Estimation table that matches the job#'s that have an
Assemble Date from the Export_Data Form.

I'm trying to delete the data in the material Estimation
table.

Does this make sense?

-----Original Message-----
Open the query in design view; go to View SQL View, copy
the text and paste.

JeffH wrote:
OK...

How do I post the SQL of the select query?

Thanks.

-----Original Message-----
For a delete query, you can have only one source
table. So you must
use a subquery for the "joined" table. Post the SQL
of the select
query that works, and what you want to delete, and
we'll show you
how to modify the query.

--

Ken Snell
<MS ACCESS MVP>

I have create a delete query that is base on two
tables.
When I run the query as a Select Query and show
datasheet
it shows all the records I want to delete. Change
it to
a delete query and look in datasheet it show
Click to expand...
all
the
records I want to delete, But when I run it it comes
up
with a error message of 'Could not delete from
specified
tables.'

Field: Labor Per Job.* | Ass Date
Table: Labor Per Job | Production
Delete: From | Where
Criteria: | Between [Forms]!
[frm_Export_Data]![FromDate]and[Forms]!
[frm_Export_Data]!
[ToDate]

I have a frm_Export_Data open with FromDate field
and the
ToDate field having information in them.

I have been using the form method to export out
information from many table in another database and
its
been working very well.

Now I need to delete all the information that has
been
exported out.

Does anyone have any Ideas????

Thanks!!!!!




.

--
Joan Wild
Microsoft Access MVP



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
JeffH said:
OK...

How do I post the SQL of the select query?

Thanks.
-----Original Message-----
For a delete query, you can have only one source table. So you must use a
subquery for the "joined" table. Post the SQL of the select query that
works, and what you want to delete, and we'll show you how to modify the
query.

--

Ken Snell
<MS ACCESS MVP>

JeffH said:
I have create a delete query that is base on two tables.
When I run the query as a Select Query and show datasheet
it shows all the records I want to delete. Change it to
a delete query and look in datasheet it show all the
records I want to delete, But when I run it it comes up
with a error message of 'Could not delete from specified
tables.'

Field: Labor Per Job.* | Ass Date
Table: Labor Per Job | Production
Delete: From | Where
Criteria: | Between [Forms]!
[frm_Export_Data]![FromDate]and[Forms]! [frm_Export_Data]!
[ToDate]

I have a frm_Export_Data open with FromDate field and the
ToDate field having information in them.

I have been using the form method to export out
information from many table in another database and its
been working very well.

Now I need to delete all the information that has been
exported out.

Does anyone have any Ideas????

Thanks!!!!!
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top