How to creat a Delete query?

  • Thread starter Thread starter Tiff
  • Start date Start date
T

Tiff

Hi,
I have a query that I get all my information from. I created a query that
pulls all the info I need and did a Mk tbl with that query. I now have my
tbl. I Created another query that will append any new records that are
created in the main query. Now I need a way to delete any records that have
been removed from the main query. I tried to use a delete query, however I am
not very familiar with these. Can someone please help?
Thanks in advance!
 
Hi,
I have a query that I get all my information from. I created a query that
pulls all the info I need and did a Mk tbl with that query.
Click to expand...

That's very rarely either necessary nor appropriate. If you can create a
MakeTable query, then you have already created a SELECT query with all of the
same information. You can base a Form, or a Report, or an Export directly on
the select query; it's not necessary to take the expensive extra step of
creating a (typically non-normalized) new table.
I now have my
tbl. I Created another query that will append any new records that are
created in the main query. Now I need a way to delete any records that have
been removed from the main query. I tried to use a delete query, however I am
not very familiar with these. Can someone please help?
Click to expand...

What specific problem are you having? What is it that you want to delete?

The basic problem you're having is that you're storing data redundantly in
your main tables, *and* the same data again in your new table. Solution? Just
don't create the new table! Then you don't need to delete from it.
 
I have to have a tbl, b/c there is also some information that has to be
manually input. The main query that I pull my information from gets updated
daily. When Something drops off that query I need it to drop from my tbl.
 
Also, I created a delete query that with the criteria WHERE is null. When I
click the datasheet view it DOES show the record that needs to be deleted,
however when I try to run the query it gives me the error "Could not delete
from specified table" This table is a single table it is not linked to any
other object or table. I just can't figure out whats wrong
 
POST the SQL of the query that is not working.
(Menu: View: SQL)

Cut and paste.

We should be able to suggest a modification that will let the query
accomplish what you want it to accomplish.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
here is the SQL:
DELETE tblCSG.*, [DB Mach Stat Export].[Sls Ord]
FROM [DB Mach Stat Export] RIGHT JOIN tblCSG ON [DB Mach Stat Export].[Sls
Ord]=tblCSG.[Sls Ord]
WHERE ((([DB Mach Stat Export].[Sls Ord]) Is Null));

Thanks again for your help.
 
DELETE DISTINCTRow tblCSG.*
FROM [DB Mach Stat Export] RIGHT JOIN tblCSG
ON [DB Mach Stat Export].[Sls Ord]=tblCSG.[Sls Ord]
WHERE ((([DB Mach Stat Export].[Sls Ord]) Is Null));

If that doesn't work you will probably need to build a sub query in a where
clause

DELETE
FROM tblCSG
WHERE [Sis Ord] NOT IN
(SELECT [Sis ORD] FROM [DB Mach Stat Export])

That could be slow with a lot of records so you could try
DELETE
FROM tblCSG
WHERE [Sis Ord] In
( SELECT T.[Sis Ord]
FROM [DB Mach Stat Export] RIGHT JOIN tblCSG as T
ON [DB Mach Stat Export].[Sls Ord]=T.[Sls Ord]
WHERE [DB Mach Stat Export].[Sls Ord] Is Null)

As always, backup your table FIRST, just in case the delete kills the wrong
records.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Tiff said:
here is the SQL:
DELETE tblCSG.*, [DB Mach Stat Export].[Sls Ord]
FROM [DB Mach Stat Export] RIGHT JOIN tblCSG ON [DB Mach Stat Export].[Sls
Ord]=tblCSG.[Sls Ord]
WHERE ((([DB Mach Stat Export].[Sls Ord]) Is Null));

Thanks again for your help.
Click to expand...
 
THANK YOU so much... It worked

John Spencer said:
DELETE DISTINCTRow tblCSG.*
FROM [DB Mach Stat Export] RIGHT JOIN tblCSG
ON [DB Mach Stat Export].[Sls Ord]=tblCSG.[Sls Ord]
WHERE ((([DB Mach Stat Export].[Sls Ord]) Is Null));

If that doesn't work you will probably need to build a sub query in a where
clause

DELETE
FROM tblCSG
WHERE [Sis Ord] NOT IN
(SELECT [Sis ORD] FROM [DB Mach Stat Export])

That could be slow with a lot of records so you could try
DELETE
FROM tblCSG
WHERE [Sis Ord] In
( SELECT T.[Sis Ord]
FROM [DB Mach Stat Export] RIGHT JOIN tblCSG as T
ON [DB Mach Stat Export].[Sls Ord]=T.[Sls Ord]
WHERE [DB Mach Stat Export].[Sls Ord] Is Null)

As always, backup your table FIRST, just in case the delete kills the wrong
records.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Tiff said:
here is the SQL:
DELETE tblCSG.*, [DB Mach Stat Export].[Sls Ord]
FROM [DB Mach Stat Export] RIGHT JOIN tblCSG ON [DB Mach Stat Export].[Sls
Ord]=tblCSG.[Sls Ord]
WHERE ((([DB Mach Stat Export].[Sls Ord]) Is Null));

Thanks again for your help.

John Spencer said:
POST the SQL of the query that is not working.
(Menu: View: SQL)

Cut and paste.

We should be able to suggest a modification that will let the query
accomplish what you want it to accomplish.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Also, I created a delete query that with the criteria WHERE is null.
When
I
click the datasheet view it DOES show the record that needs to be
deleted,
however when I try to run the query it gives me the error "Could not
delete
from specified table" This table is a single table it is not linked to
any
other object or table. I just can't figure out whats wrong

:

On Tue, 4 Mar 2008 07:35:01 -0800, Tiff
<[email protected]>
wrote:

Hi,
I have a query that I get all my information from. I created a query
that
pulls all the info I need and did a Mk tbl with that query.

That's very rarely either necessary nor appropriate. If you can create
a
MakeTable query, then you have already created a SELECT query with all
of
the
same information. You can base a Form, or a Report, or an Export
directly
on
the select query; it's not necessary to take the expensive extra step
of
creating a (typically non-normalized) new table.

I now have my
tbl. I Created another query that will append any new records that
are
created in the main query. Now I need a way to delete any records
that
have
been removed from the main query. I tried to use a delete query,
however
I am
not very familiar with these. Can someone please help?

What specific problem are you having? What is it that you want to
delete?

The basic problem you're having is that you're storing data
redundantly
in
your main tables, *and* the same data again in your new table.
Solution?
Just
don't create the new table! Then you don't need to delete from it.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top