Delete Query

[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!!!!!

 

[Ken Snell]

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.

 

[JeffH]

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]

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!!!!!

.

 

[JeffH]

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!!!!!




.

--
Joan Wild
Microsoft Access MVP



.

 

[Ken Snell]

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>

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!!!!!




.

--
Joan Wild
Microsoft Access MVP



.

 

[JeffH]

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>

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



.

.

 

[Ken Snell]

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

--

Ken Snell
<MS ACCESS MVP>

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>

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



.

.

 

[JeffH]

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>

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>


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



.



.

.

 

[???]

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!!!!!

.