Delete query not working

S

Sewagerat

Registered User.
Local time
Today, 08:42
Joined
Nov 23, 2009
Messages
19
I have done delete queries before but this one is not working. Ill just paste the script and see if you guys can point out the mistake, btw I did it in design view but seeing as though you all use script more. What it is, is all my 4 tables. I need to have the records with 'None' for Customers.M, 'None' for Customers.GM and 'No' for Customers.Lessons deleted. The 'None' is chosen from a Look-up field which has 2 columns and the 'No' is chosen from a Look-up field which has 1 column. Im not sure if you need to have Cascade records etc in the relationships on because I dont want to use enforce referential intergrity. Please help me :D

Its a bit big :( (I split it into the 3 main parts to make it easier to read)

DELETE [Current Game].*, Customers.*, Golfers.*, Teachers.*, Customers.M, Customers.GM, Customers.Lessons, [Current Game].CID, [Current Game].[Date of game], [Current Game].[1st], [Current Game].[2nd], [Current Game].[3rd], [Current Game].[4th], [Current Game].[5th], [Current Game].[6th], [Current Game].[7th], [Current Game].[8th], [Current Game].[9th], [Current Game].[10th], [Current Game].[11th], [Current Game].[12th], [Current Game].[13th], [Current Game].[14th], [Current Game].[15th], [Current Game].[16th], [Current Game].[17th], [Current Game].[18th], [Current Game].Total, Customers.PID, Customers.Surname, Customers.[First Name], Customers.Title, Customers.DOB, Customers.Sex, Customers.GID, Customers.DSUFM, Customers.EDFM, Customers.DSUFGM, Customers.EDFGM, Customers.TID, Customers.[Phone #], Customers.Email, Customers.Photo, Golfers.GID, Golfers.CID, Golfers.Rank, Golfers.TAO8, Golfers.TW08, Golfers.TS09, Golfers.TSm09, Golfers.TA09, Golfers.TW09, Teachers.TID, Teachers.Title, Teachers.Surname, Teachers.[First Name], Teachers.[Phone #]

FROM Teachers INNER JOIN (([Current Game] INNER JOIN Golfers ON [Current Game].CID = Golfers.CID) INNER JOIN Customers ON Golfers.GID = Customers.GID) ON Teachers.TID = Customers.TID

WHERE (((Customers.M)='None') AND ((Customers.GM)='None') AND ((Customers.Lessons)='No'));
 
Sewagerat said:
I have done delete queries before but this one is not working. Ill just paste the script and see if you guys can point out the mistake, btw I did it in design view but seeing as though you all use script more. What it is, is all my 4 tables. I need to have the records with 'None' for Customers.M, 'None' for Customers.GM and 'No' for Customers.Lessons deleted. The 'None' is chosen from a Look-up field which has 2 columns and the 'No' is chosen from a Look-up field which has 1 column. Im not sure if you need to have Cascade records etc in the relationships on because I dont want to use enforce referential intergrity. Please help me :D

Its a bit big :( (I split it into the 3 main parts to make it easier to read)

DELETE [Current Game].*, Customers.*, Golfers.*, Teachers.*, Customers.M, Customers.GM, Customers.Lessons, [Current Game].CID, [Current Game].[Date of game], [Current Game].[1st], [Current Game].[2nd], [Current Game].[3rd], [Current Game].[4th], [Current Game].[5th], [Current Game].[6th], [Current Game].[7th], [Current Game].[8th], [Current Game].[9th], [Current Game].[10th], [Current Game].[11th], [Current Game].[12th], [Current Game].[13th], [Current Game].[14th], [Current Game].[15th], [Current Game].[16th], [Current Game].[17th], [Current Game].[18th], [Current Game].Total, Customers.PID, Customers.Surname, Customers.[First Name], Customers.Title, Customers.DOB, Customers.Sex, Customers.GID, Customers.DSUFM, Customers.EDFM, Customers.DSUFGM, Customers.EDFGM, Customers.TID, Customers.[Phone #], Customers.Email, Customers.Photo, Golfers.GID, Golfers.CID, Golfers.Rank, Golfers.TAO8, Golfers.TW08, Golfers.TS09, Golfers.TSm09, Golfers.TA09, Golfers.TW09, Teachers.TID, Teachers.Title, Teachers.Surname, Teachers.[First Name], Teachers.[Phone #]

FROM Teachers INNER JOIN (([Current Game] INNER JOIN Golfers ON [Current Game].CID = Golfers.CID) INNER JOIN Customers ON Golfers.GID = Customers.GID) ON Teachers.TID = Customers.TID

WHERE (((Customers.M)='None') AND ((Customers.GM)='None') AND ((Customers.Lessons)='No'));
Click to expand...

I think you are over thinking your problem.

You can only DELETE Records, not columns.

To effectively DELETE a column you must use an UPDATE query.

Here is the basic SQL Delete syntax (from BlueClaw http://www.blueclaw-db.com/accessquerysql/sql_delete.htm)

The basic syntax of the Access Delete query is:

Delete TableName.* From TableName Where somecriteria;

Note that using an Access delete query without the where clause will delete all rows from the specified table.

The asterisk in TableName.* is a wildcard character and refers to all fields within the table. You may only delete entire records rather than values in particular fields therefore the asterisk is mandatory.

If TableName is a table which is defined as the master of a master-detail relationship and the cascade delete property of the relationship is set to true then corresponding records in the detail table will also be deleted.

The delete method is often preferable to the drop table method, which complete removes the table from the database, because all of the characteristics of the table remain intact - such as the indexes, relationships and primary keys.

Now with real column and table names:

Delete * From M_Employees Where Emp_Name = "Joe";

The above query will delete all employees with an emp_name of Joe.
Click to expand...

Your Where clause

WHERE (((Customers.M)='None') AND ((Customers.GM)='None') AND ((Customers.Lessons)='No'))
Click to expand...
only refers to Table 'Customers'.


Let us know.
 
I dont plan to delete columns, only records. I have only mentioned columns because when you make a Look-up field, it makes a list of options and that list can have a number of columns.

P.S. The database is split and the query is in the front-end, not sure if that makes a difference.


I'd like to add that i want to delete all the fields of a record from 4 tables using criteria from only one table, i hope that the relationships will select the correct records to be deleted in the 3 tables without criteria.
 
Last edited:
You don't need to list all the fields. [TableName].* is enough. When building a delete query I usually start with a select query and then change it to a delete query when I know it is selecting the data correctly.

Two points unrelated to your question:
- Lookup fields are an absolute nightmare, best not to use them
- Your table [Current Game] is horribly un-normalised
 
When you have repeating columns like you have 1st, 2nd, etc this is a bad design. Repeating data like this should be stared in a second table as repeating records, not fields. This is a process known as normalisation (normalization in the US). Plenty of stuff on the internet about this but Wikipedia is a good place to start.
 
But is there a way to make a delete query for several tables based on criteria from one of them only?
 
You can't do a single delete query for multiple tables. You need one for each table.
 
:( Thats the problem cause the criteria is in only 1 table and is joined to other table by the relationships.
 
Sewagerat said:
:( Thats the problem cause the criteria is in only 1 table and is joined to other table by the relationships.
Click to expand...

You should be able to have that table in the query but you can only delete from one table at a time. So you can have one query with one table to delete from and that other table as criteria. Then as long as the deletions are ONLY from the one table it should work.
 
Is it possible for you to write me the script to do that boblarson?
Because I made a simply delete query but its not working:

DELETE Table2.*, Table1.gid
FROM Table2 INNER JOIN Table1 ON Table2.tid = Table1.tid
WHERE (((Table1.gid)=[enter]));

(it features 2 tables with the criteria from only one of them)

Any advice?
 
Sewagerat said:
Is it possible for you to write me the script to do that boblarson?
Because I made a simply delete query but its not working:

DELETE Table2.*, Table1.gid
FROM Table2 INNER JOIN Table1 ON Table2.tid = Table1.tid
WHERE (((Table1.gid)=[enter]));

(it features 2 tables with the criteria from only one of them)

Any advice?
Click to expand...

Again, you're trying to delete from two tables at once.

DELETE Table2.*, Table1.gid

Unfortunately, this generally can't be done unless, as you said, you enforce referential integrity. Also, I'm not sure why you are specifying a single column:

Table1.gid

You can't delete a column like that - you have to delete the whole row.

DELETE Table1.*

You're trying to do too much in one query. It can take several steps and queries to delete from two related tables. There's generally no easy way to do this. I suggest you work on putting together some VBA script.
 
For eample, one technique I used in the past was to add a boolean column called "Deletable" to both tables. This takes multiple queries:

- ALTER TABLE table1 ADD COLUMN Deletable YESNO
- ALTER TABLE table2 ADD COLUMN Deletable YESNO
- UPDATE table1 SET Deletable = False
- UPDATE table2 SET Deletable = False

- Update Table1
Inner Join Table2 on Table1.Id = Table2.ID
Set Table1.Deletable = True


- Update Table2
Inner Join Table2 on Table1.Id = Table2.ID
Set Table2.Deletable = True

- DELETE FROM Table1 WHERE Deletable = True
- DELETE FROM Table2 WHERE Deletable = True

- ALTER TABLE table1 DROP COLUMN Deletable
- ALTER TABLE table2 DROP COLUMN Deletable


I realize that's a lot of queries (there are shorter ways to do it) but it's a technique easy to understand and follow. In VBA, run a query like this:

CurrentDB.Execute "ALTER Table.....
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom