delete query error

[Guest]

Good Morning. I am trying to perform a delete query to delete all records
that are in table "keyskillcomp" but aren't in table "keyskilllatest". I
have done the query and when it is viewed it shows the records that should be
deleted. However when I try and execute the query I get the error message;
"Could not delete from specified tables".
My tables aren't read only as I can update and append to them. The sql is
as follows;
DELETE keyskillcomp.*, [key skill latest].[Course Code], [key skill
latest].[Person Code]
FROM keyskillcomp
LEFT JOIN [key skill latest] ON (keyskillcomp.[Course Code] = [key skill
latest].[Course Code]) AND (keyskillcomp.[Person Code] = [key skill
latest].[Person Code])
WHERE ((([key skill latest].[Course Code]) Is Null) AND (([key skill
latest].[Person Code]) Is Null));

Any help would be great
Rhys

 

[Guest]

Hi Rhys,

Does the table "keyskillcomp" include any related child tables? If so, does
the relationship include referential integrity enforced? You might be running
into a situation where you need to delete child records before deleting
records from a parent table.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Hi Tom,
The table "keyskilllatest" gets records from other linked tables. The
records are then appended to table "keyskillcomp" if they are not already
there. "Keyskillcomp" started off as a blank table. It doesn't include any
related child tables.
I think it doesn't work because the first line seems asking to delete all
records from "keyskillcomp" and personcode and coursecode from
"keyskilllatest". I think I need to do a subquery but I'm not sure how to do
this.

Hi Rhys,

Does the table "keyskillcomp" include any related child tables? If so, does
the relationship include referential integrity enforced? You might be running
into a situation where you need to delete child records before deleting
records from a parent table.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Good Morning. I am trying to perform a delete query to delete all records
that are in table "keyskillcomp" but aren't in table "keyskilllatest". I
have done the query and when it is viewed it shows the records that should be
deleted. However when I try and execute the query I get the error message;
"Could not delete from specified tables".
My tables aren't read only as I can update and append to them. The sql is
as follows;
DELETE keyskillcomp.*, [key skill latest].[Course Code], [key skill
latest].[Person Code]
FROM keyskillcomp
LEFT JOIN [key skill latest] ON (keyskillcomp.[Course Code] = [key skill
latest].[Course Code]) AND (keyskillcomp.[Person Code] = [key skill
latest].[Person Code])
WHERE ((([key skill latest].[Course Code]) Is Null) AND (([key skill
latest].[Person Code]) Is Null));

Any help would be great
Rhys

 

[Guest]

Hi Rhys,

The following SQL seems to work for me. It assumes that Course Code is the
primary key in both tables:

DELETE keyskillcomp.*
FROM keyskillcomp LEFT JOIN keyskilllatest
ON keyskillcomp.[Course Code] = keyskilllatest.[Course Code]
WHERE (((keyskilllatest.[Course Code]) Is Null));


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi Tom,
The table "keyskilllatest" gets records from other linked tables. The
records are then appended to table "keyskillcomp" if they are not already
there. "Keyskillcomp" started off as a blank table. It doesn't include any
related child tables.
I think it doesn't work because the first line seems asking to delete all
records from "keyskillcomp" and personcode and coursecode from
"keyskilllatest". I think I need to do a subquery but I'm not sure how to do
this.

Hi Rhys,

Does the table "keyskillcomp" include any related child tables? If so, does
the relationship include referential integrity enforced? You might be running
into a situation where you need to delete child records before deleting
records from a parent table.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Good Morning. I am trying to perform a delete query to delete all records
that are in table "keyskillcomp" but aren't in table "keyskilllatest". I
have done the query and when it is viewed it shows the records that should be
deleted. However when I try and execute the query I get the error message;
"Could not delete from specified tables".
My tables aren't read only as I can update and append to them. The sql is
as follows;
DELETE keyskillcomp.*, [key skill latest].[Course Code], [key skill
latest].[Person Code]
FROM keyskillcomp
LEFT JOIN [key skill latest] ON (keyskillcomp.[Course Code] = [key skill
latest].[Course Code]) AND (keyskillcomp.[Person Code] = [key skill
latest].[Person Code])
WHERE ((([key skill latest].[Course Code]) Is Null) AND (([key skill
latest].[Person Code]) Is Null));

Any help would be great
Rhys

 

[Guest]

Based on the principle of whatworks for you, this should work for me but it
doesn't
DELETE keyskillcomp.*
FROM keyskillcomp
LEFT JOIN [key skill latest] ON (keyskillcomp.[Course Code] = [key skill
latest].[Course Code]) AND (keyskillcomp.[Person Code] = [key skill
latest].[Person Code])
WHERE ((([key skill latest].[Course Code]) Is Null) AND (([key skill
latest].[Person Code]) Is Null));

Hi Rhys,

The following SQL seems to work for me. It assumes that Course Code is the
primary key in both tables:

DELETE keyskillcomp.*
FROM keyskillcomp LEFT JOIN keyskilllatest
ON keyskillcomp.[Course Code] = keyskilllatest.[Course Code]
WHERE (((keyskilllatest.[Course Code]) Is Null));


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi Tom,
The table "keyskilllatest" gets records from other linked tables. The
records are then appended to table "keyskillcomp" if they are not already
there. "Keyskillcomp" started off as a blank table. It doesn't include any
related child tables.
I think it doesn't work because the first line seems asking to delete all
records from "keyskillcomp" and personcode and coursecode from
"keyskilllatest". I think I need to do a subquery but I'm not sure how to do
this.

Hi Rhys,

Does the table "keyskillcomp" include any related child tables? If so, does
the relationship include referential integrity enforced? You might be running
into a situation where you need to delete child records before deleting
records from a parent table.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Good Morning. I am trying to perform a delete query to delete all records
that are in table "keyskillcomp" but aren't in table "keyskilllatest". I
have done the query and when it is viewed it shows the records that should be
deleted. However when I try and execute the query I get the error message;
"Could not delete from specified tables".
My tables aren't read only as I can update and append to them. The sql is
as follows;
DELETE keyskillcomp.*, [key skill latest].[Course Code], [key skill
latest].[Person Code]
FROM keyskillcomp
LEFT JOIN [key skill latest] ON (keyskillcomp.[Course Code] = [key skill
latest].[Course Code]) AND (keyskillcomp.[Person Code] = [key skill
latest].[Person Code])
WHERE ((([key skill latest].[Course Code]) Is Null) AND (([key skill
latest].[Person Code]) Is Null));

Any help would be great
Rhys

 

[Guest]

I cannot get your query to work. When I paste it into the SQL window of a new
query, I get "Syntax error in JOIN operation" error.

Okay, can you lay out the design of your tables? Here is the design that I
used for my quickie test. I don't know if this is correctly mimicking your
design:

Table: keyskillcomp
Field1: Course Code, Text (50) Primary key
Field2: Person Code, Text (50)

Table: keyskilllatest
Field1: Course Code, Text (50) Primary key
Field2: Person Code, Text (50)


So, can you lay out a design something like the above, which allows me to
build the correct tables? Make sure to tell me which fields are primary
keys, and if you have a foreign key, tell me that too.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Based on the principle of whatworks for you, this should work for me but it
doesn't
DELETE keyskillcomp.*
FROM keyskillcomp
LEFT JOIN [key skill latest] ON (keyskillcomp.[Course Code] = [key skill
latest].[Course Code]) AND (keyskillcomp.[Person Code] = [key skill
latest].[Person Code])
WHERE ((([key skill latest].[Course Code]) Is Null) AND (([key skill
latest].[Person Code]) Is Null));

Hi Rhys,

The following SQL seems to work for me. It assumes that Course Code is the
primary key in both tables:

DELETE keyskillcomp.*
FROM keyskillcomp LEFT JOIN keyskilllatest
ON keyskillcomp.[Course Code] = keyskilllatest.[Course Code]
WHERE (((keyskilllatest.[Course Code]) Is Null));


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi Tom,
The table "keyskilllatest" gets records from other linked tables. The
records are then appended to table "keyskillcomp" if they are not already
there. "Keyskillcomp" started off as a blank table. It doesn't include any
related child tables.
I think it doesn't work because the first line seems asking to delete all
records from "keyskillcomp" and personcode and coursecode from
"keyskilllatest". I think I need to do a subquery but I'm not sure how to do
this.

:

Hi Rhys,

Does the table "keyskillcomp" include any related child tables? If so, does
the relationship include referential integrity enforced? You might be running
into a situation where you need to delete child records before deleting
records from a parent table.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Good Morning. I am trying to perform a delete query to delete all records
that are in table "keyskillcomp" but aren't in table "keyskilllatest". I
have done the query and when it is viewed it shows the records that should be
deleted. However when I try and execute the query I get the error message;
"Could not delete from specified tables".
My tables aren't read only as I can update and append to them. The sql is
as follows;
DELETE keyskillcomp.*, [key skill latest].[Course Code], [key skill
latest].[Person Code]
FROM keyskillcomp
LEFT JOIN [key skill latest] ON (keyskillcomp.[Course Code] = [key skill
latest].[Course Code]) AND (keyskillcomp.[Person Code] = [key skill
latest].[Person Code])
WHERE ((([key skill latest].[Course Code]) Is Null) AND (([key skill
latest].[Person Code]) Is Null));

Any help would be great
Rhys

 

[Guest]

Table 1 :Keyskilllatest
field1: person code, number
field 2: course code, text(10)

table2:keyskillcomp
field 1erson code, number
field2: course code, text(10)

no primary keys

rhys

I cannot get your query to work. When I paste it into the SQL window of a new
query, I get "Syntax error in JOIN operation" error.

Okay, can you lay out the design of your tables? Here is the design that I
used for my quickie test. I don't know if this is correctly mimicking your
design:

Table: keyskillcomp
Field1: Course Code, Text (50) Primary key
Field2: Person Code, Text (50)

Table: keyskilllatest
Field1: Course Code, Text (50) Primary key
Field2: Person Code, Text (50)


So, can you lay out a design something like the above, which allows me to
build the correct tables? Make sure to tell me which fields are primary
keys, and if you have a foreign key, tell me that too.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Based on the principle of whatworks for you, this should work for me but it
doesn't
DELETE keyskillcomp.*
FROM keyskillcomp
LEFT JOIN [key skill latest] ON (keyskillcomp.[Course Code] = [key skill
latest].[Course Code]) AND (keyskillcomp.[Person Code] = [key skill
latest].[Person Code])
WHERE ((([key skill latest].[Course Code]) Is Null) AND (([key skill
latest].[Person Code]) Is Null));

Hi Rhys,

The following SQL seems to work for me. It assumes that Course Code is the
primary key in both tables:

DELETE keyskillcomp.*
FROM keyskillcomp LEFT JOIN keyskilllatest
ON keyskillcomp.[Course Code] = keyskilllatest.[Course Code]
WHERE (((keyskilllatest.[Course Code]) Is Null));


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


:

Hi Tom,
The table "keyskilllatest" gets records from other linked tables. The
records are then appended to table "keyskillcomp" if they are not already
there. "Keyskillcomp" started off as a blank table. It doesn't include any
related child tables.
I think it doesn't work because the first line seems asking to delete all
records from "keyskillcomp" and personcode and coursecode from
"keyskilllatest". I think I need to do a subquery but I'm not sure how to do
this.

:

Hi Rhys,

Does the table "keyskillcomp" include any related child tables? If so, does
the relationship include referential integrity enforced? You might be running
into a situation where you need to delete child records before deleting
records from a parent table.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Good Morning. I am trying to perform a delete query to delete all records
that are in table "keyskillcomp" but aren't in table "keyskilllatest". I
have done the query and when it is viewed it shows the records that should be
deleted. However when I try and execute the query I get the error message;
"Could not delete from specified tables".
My tables aren't read only as I can update and append to them. The sql is
as follows;
DELETE keyskillcomp.*, [key skill latest].[Course Code], [key skill
latest].[Person Code]
FROM keyskillcomp
LEFT JOIN [key skill latest] ON (keyskillcomp.[Course Code] = [key skill
latest].[Course Code]) AND (keyskillcomp.[Person Code] = [key skill
latest].[Person Code])
WHERE ((([key skill latest].[Course Code]) Is Null) AND (([key skill
latest].[Person Code]) Is Null));

Any help would be great
Rhys

 

[Guest]

Okay, that explains it. When I remove the primary key from both tables, the
delete query I supplied does not work. It does work, however, if I set the
Course Code field in the skillkeylatest table as a primary key, without
specifying a PK in skillkeycode. Is there any possibility that you can set
this field either as a primary key, or indexed uniquely [Yes, (No
Duplicates)]?

Pretty much every table *should* have a primary key. You really shouldn't
have tables that have no primary key.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Table 1 :Keyskilllatest
field1: person code, number
field 2: course code, text(10)

table2:keyskillcomp
field 1erson code, number
field2: course code, text(10)

no primary keys

rhys

I cannot get your query to work. When I paste it into the SQL window of a new
query, I get "Syntax error in JOIN operation" error.

Okay, can you lay out the design of your tables? Here is the design that I
used for my quickie test. I don't know if this is correctly mimicking your
design:

Table: keyskillcomp
Field1: Course Code, Text (50) Primary key
Field2: Person Code, Text (50)

Table: keyskilllatest
Field1: Course Code, Text (50) Primary key
Field2: Person Code, Text (50)


So, can you lay out a design something like the above, which allows me to
build the correct tables? Make sure to tell me which fields are primary
keys, and if you have a foreign key, tell me that too.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Based on the principle of whatworks for you, this should work for me but it
doesn't
DELETE keyskillcomp.*
FROM keyskillcomp
LEFT JOIN [key skill latest] ON (keyskillcomp.[Course Code] = [key skill
latest].[Course Code]) AND (keyskillcomp.[Person Code] = [key skill
latest].[Person Code])
WHERE ((([key skill latest].[Course Code]) Is Null) AND (([key skill
latest].[Person Code]) Is Null));



:

Hi Rhys,

The following SQL seems to work for me. It assumes that Course Code is the
primary key in both tables:

DELETE keyskillcomp.*
FROM keyskillcomp LEFT JOIN keyskilllatest
ON keyskillcomp.[Course Code] = keyskilllatest.[Course Code]
WHERE (((keyskilllatest.[Course Code]) Is Null));


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


:

Hi Tom,
The table "keyskilllatest" gets records from other linked tables. The
records are then appended to table "keyskillcomp" if they are not already
there. "Keyskillcomp" started off as a blank table. It doesn't include any
related child tables.
I think it doesn't work because the first line seems asking to delete all
records from "keyskillcomp" and personcode and coursecode from
"keyskilllatest". I think I need to do a subquery but I'm not sure how to do
this.

:

Hi Rhys,

Does the table "keyskillcomp" include any related child tables? If so, does
the relationship include referential integrity enforced? You might be running
into a situation where you need to delete child records before deleting
records from a parent table.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Good Morning. I am trying to perform a delete query to delete all records
that are in table "keyskillcomp" but aren't in table "keyskilllatest". I
have done the query and when it is viewed it shows the records that should be
deleted. However when I try and execute the query I get the error message;
"Could not delete from specified tables".
My tables aren't read only as I can update and append to them. The sql is
as follows;
DELETE keyskillcomp.*, [key skill latest].[Course Code], [key skill
latest].[Person Code]
FROM keyskillcomp
LEFT JOIN [key skill latest] ON (keyskillcomp.[Course Code] = [key skill
latest].[Course Code]) AND (keyskillcomp.[Person Code] = [key skill
latest].[Person Code])
WHERE ((([key skill latest].[Course Code]) Is Null) AND (([key skill
latest].[Person Code]) Is Null));

Any help would be great
Rhys

 

[Guest]

I've now set person code and course code in "keyskilllatest" as primary keys.
It is working now. Thanks a lot for allyour time and help. Much appreciated

Rhys

Okay, that explains it. When I remove the primary key from both tables, the
delete query I supplied does not work. It does work, however, if I set the
Course Code field in the skillkeylatest table as a primary key, without
specifying a PK in skillkeycode. Is there any possibility that you can set
this field either as a primary key, or indexed uniquely [Yes, (No
Duplicates)]?

Pretty much every table *should* have a primary key. You really shouldn't
have tables that have no primary key.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Table 1 :Keyskilllatest
field1: person code, number
field 2: course code, text(10)

table2:keyskillcomp
field 1erson code, number
field2: course code, text(10)

no primary keys

rhys

I cannot get your query to work. When I paste it into the SQL window of a new
query, I get "Syntax error in JOIN operation" error.

Okay, can you lay out the design of your tables? Here is the design that I
used for my quickie test. I don't know if this is correctly mimicking your
design:

Table: keyskillcomp
Field1: Course Code, Text (50) Primary key
Field2: Person Code, Text (50)

Table: keyskilllatest
Field1: Course Code, Text (50) Primary key
Field2: Person Code, Text (50)


So, can you lay out a design something like the above, which allows me to
build the correct tables? Make sure to tell me which fields are primary
keys, and if you have a foreign key, tell me that too.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


:

Based on the principle of whatworks for you, this should work for me but it
doesn't
DELETE keyskillcomp.*
FROM keyskillcomp
LEFT JOIN [key skill latest] ON (keyskillcomp.[Course Code] = [key skill
latest].[Course Code]) AND (keyskillcomp.[Person Code] = [key skill
latest].[Person Code])
WHERE ((([key skill latest].[Course Code]) Is Null) AND (([key skill
latest].[Person Code]) Is Null));



:

Hi Rhys,

The following SQL seems to work for me. It assumes that Course Code is the
primary key in both tables:

DELETE keyskillcomp.*
FROM keyskillcomp LEFT JOIN keyskilllatest
ON keyskillcomp.[Course Code] = keyskilllatest.[Course Code]
WHERE (((keyskilllatest.[Course Code]) Is Null));


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


:

Hi Tom,
The table "keyskilllatest" gets records from other linked tables. The
records are then appended to table "keyskillcomp" if they are not already
there. "Keyskillcomp" started off as a blank table. It doesn't include any
related child tables.
I think it doesn't work because the first line seems asking to delete all
records from "keyskillcomp" and personcode and coursecode from
"keyskilllatest". I think I need to do a subquery but I'm not sure how to do
this.

:

Hi Rhys,

Does the table "keyskillcomp" include any related child tables? If so, does
the relationship include referential integrity enforced? You might be running
into a situation where you need to delete child records before deleting
records from a parent table.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Good Morning. I am trying to perform a delete query to delete all records
that are in table "keyskillcomp" but aren't in table "keyskilllatest". I
have done the query and when it is viewed it shows the records that should be
deleted. However when I try and execute the query I get the error message;
"Could not delete from specified tables".
My tables aren't read only as I can update and append to them. The sql is
as follows;
DELETE keyskillcomp.*, [key skill latest].[Course Code], [key skill
latest].[Person Code]
FROM keyskillcomp
LEFT JOIN [key skill latest] ON (keyskillcomp.[Course Code] = [key skill
latest].[Course Code]) AND (keyskillcomp.[Person Code] = [key skill
latest].[Person Code])
WHERE ((([key skill latest].[Course Code]) Is Null) AND (([key skill
latest].[Person Code]) Is Null));

Any help would be great
Rhys

 

[Guest]

haha I love access. Now that the delete works I have another problem.
Because keyskilllatest is a make table query it deletes the primary key each
time it is run. is there a simple way to set primary keys other than
manually. Maybe by coding?

rhys

Hi Rhys,

The following SQL seems to work for me. It assumes that Course Code is the
primary key in both tables:

DELETE keyskillcomp.*
FROM keyskillcomp LEFT JOIN keyskilllatest
ON keyskillcomp.[Course Code] = keyskilllatest.[Course Code]
WHERE (((keyskilllatest.[Course Code]) Is Null));


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi Tom,
The table "keyskilllatest" gets records from other linked tables. The
records are then appended to table "keyskillcomp" if they are not already
there. "Keyskillcomp" started off as a blank table. It doesn't include any
related child tables.
I think it doesn't work because the first line seems asking to delete all
records from "keyskillcomp" and personcode and coursecode from
"keyskilllatest". I think I need to do a subquery but I'm not sure how to do
this.

Hi Rhys,

Does the table "keyskillcomp" include any related child tables? If so, does
the relationship include referential integrity enforced? You might be running
into a situation where you need to delete child records before deleting
records from a parent table.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Good Morning. I am trying to perform a delete query to delete all records
that are in table "keyskillcomp" but aren't in table "keyskilllatest". I
have done the query and when it is viewed it shows the records that should be
deleted. However when I try and execute the query I get the error message;
"Could not delete from specified tables".
My tables aren't read only as I can update and append to them. The sql is
as follows;
DELETE keyskillcomp.*, [key skill latest].[Course Code], [key skill
latest].[Person Code]
FROM keyskillcomp
LEFT JOIN [key skill latest] ON (keyskillcomp.[Course Code] = [key skill
latest].[Course Code]) AND (keyskillcomp.[Person Code] = [key skill
latest].[Person Code])
WHERE ((([key skill latest].[Course Code]) Is Null) AND (([key skill
latest].[Person Code]) Is Null));

Any help would be great
Rhys

 

[Guest]

Yes, but that's a new question and should be posted as such.

Also, in your last post, you wrote:
"I've now set person code and course code in "keyskilllatest" as primary
keys."

Just to be clear, a table can only have one primary key. It can be a
combined field primary key (ie. key symbols are displayed next to two or more
fields in table design view), but nevertheless, it is only one primary key
per table.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

haha I love access. Now that the delete works I have another problem.
Because keyskilllatest is a make table query it deletes the primary key each
time it is run. is there a simple way to set primary keys other than
manually. Maybe by coding?

rhys

Hi Rhys,

The following SQL seems to work for me. It assumes that Course Code is the
primary key in both tables:

DELETE keyskillcomp.*
FROM keyskillcomp LEFT JOIN keyskilllatest
ON keyskillcomp.[Course Code] = keyskilllatest.[Course Code]
WHERE (((keyskilllatest.[Course Code]) Is Null));


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi Tom,
The table "keyskilllatest" gets records from other linked tables. The
records are then appended to table "keyskillcomp" if they are not already
there. "Keyskillcomp" started off as a blank table. It doesn't include any
related child tables.
I think it doesn't work because the first line seems asking to delete all
records from "keyskillcomp" and personcode and coursecode from
"keyskilllatest". I think I need to do a subquery but I'm not sure how to do
this.

:

Hi Rhys,

Does the table "keyskillcomp" include any related child tables? If so, does
the relationship include referential integrity enforced? You might be running
into a situation where you need to delete child records before deleting
records from a parent table.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Good Morning. I am trying to perform a delete query to delete all records
that are in table "keyskillcomp" but aren't in table "keyskilllatest". I
have done the query and when it is viewed it shows the records that should be
deleted. However when I try and execute the query I get the error message;
"Could not delete from specified tables".
My tables aren't read only as I can update and append to them. The sql is
as follows;
DELETE keyskillcomp.*, [key skill latest].[Course Code], [key skill
latest].[Person Code]
FROM keyskillcomp
LEFT JOIN [key skill latest] ON (keyskillcomp.[Course Code] = [key skill
latest].[Course Code]) AND (keyskillcomp.[Person Code] = [key skill
latest].[Person Code])
WHERE ((([key skill latest].[Course Code]) Is Null) AND (([key skill
latest].[Person Code]) Is Null));

Any help would be great
Rhys

 

[Guest]

Yes I meant as a combined PK. Thanks for your help mate

Rhys

Yes, but that's a new question and should be posted as such.

Also, in your last post, you wrote:
"I've now set person code and course code in "keyskilllatest" as primary
keys."

Just to be clear, a table can only have one primary key. It can be a
combined field primary key (ie. key symbols are displayed next to two or more
fields in table design view), but nevertheless, it is only one primary key
per table.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

haha I love access. Now that the delete works I have another problem.
Because keyskilllatest is a make table query it deletes the primary key each
time it is run. is there a simple way to set primary keys other than
manually. Maybe by coding?

rhys

Hi Rhys,

The following SQL seems to work for me. It assumes that Course Code is the
primary key in both tables:

DELETE keyskillcomp.*
FROM keyskillcomp LEFT JOIN keyskilllatest
ON keyskillcomp.[Course Code] = keyskilllatest.[Course Code]
WHERE (((keyskilllatest.[Course Code]) Is Null));


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


:

Hi Tom,
The table "keyskilllatest" gets records from other linked tables. The
records are then appended to table "keyskillcomp" if they are not already
there. "Keyskillcomp" started off as a blank table. It doesn't include any
related child tables.
I think it doesn't work because the first line seems asking to delete all
records from "keyskillcomp" and personcode and coursecode from
"keyskilllatest". I think I need to do a subquery but I'm not sure how to do
this.

:

Hi Rhys,

Does the table "keyskillcomp" include any related child tables? If so, does
the relationship include referential integrity enforced? You might be running
into a situation where you need to delete child records before deleting
records from a parent table.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Good Morning. I am trying to perform a delete query to delete all records
that are in table "keyskillcomp" but aren't in table "keyskilllatest". I
have done the query and when it is viewed it shows the records that should be
deleted. However when I try and execute the query I get the error message;
"Could not delete from specified tables".
My tables aren't read only as I can update and append to them. The sql is
as follows;
DELETE keyskillcomp.*, [key skill latest].[Course Code], [key skill
latest].[Person Code]
FROM keyskillcomp
LEFT JOIN [key skill latest] ON (keyskillcomp.[Course Code] = [key skill
latest].[Course Code]) AND (keyskillcomp.[Person Code] = [key skill
latest].[Person Code])
WHERE ((([key skill latest].[Course Code]) Is Null) AND (([key skill
latest].[Person Code]) Is Null));

Any help would be great
Rhys

 

[Guest]

You're welcome.

For your second question, I thought of a fairly easy solution, so I'll go
ahead and offer it now (however, I'm calling it an evening now, so I won't be
available for any follow-up). Anyway, instead of running a make table query,
just run VBA code that deletes all records from keyskilllatest followed by
VBA code that appends new records to the same table. Your primary key stays
in place that way. No need to try to use code to create a primary key.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Yes I meant as a combined PK. Thanks for your help mate

Rhys

Yes, but that's a new question and should be posted as such.

Also, in your last post, you wrote:
"I've now set person code and course code in "keyskilllatest" as primary
keys."

Just to be clear, a table can only have one primary key. It can be a
combined field primary key (ie. key symbols are displayed next to two or more
fields in table design view), but nevertheless, it is only one primary key
per table.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

haha I love access. Now that the delete works I have another problem.
Because keyskilllatest is a make table query it deletes the primary key each
time it is run. is there a simple way to set primary keys other than
manually. Maybe by coding?

rhys

:

Hi Rhys,

The following SQL seems to work for me. It assumes that Course Code is the
primary key in both tables:

DELETE keyskillcomp.*
FROM keyskillcomp LEFT JOIN keyskilllatest
ON keyskillcomp.[Course Code] = keyskilllatest.[Course Code]
WHERE (((keyskilllatest.[Course Code]) Is Null));


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


:

Hi Tom,
The table "keyskilllatest" gets records from other linked tables. The
records are then appended to table "keyskillcomp" if they are not already
there. "Keyskillcomp" started off as a blank table. It doesn't include any
related child tables.
I think it doesn't work because the first line seems asking to delete all
records from "keyskillcomp" and personcode and coursecode from
"keyskilllatest". I think I need to do a subquery but I'm not sure how to do
this.

:

Hi Rhys,

Does the table "keyskillcomp" include any related child tables? If so, does
the relationship include referential integrity enforced? You might be running
into a situation where you need to delete child records before deleting
records from a parent table.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Good Morning. I am trying to perform a delete query to delete all records
that are in table "keyskillcomp" but aren't in table "keyskilllatest". I
have done the query and when it is viewed it shows the records that should be
deleted. However when I try and execute the query I get the error message;
"Could not delete from specified tables".
My tables aren't read only as I can update and append to them. The sql is
as follows;
DELETE keyskillcomp.*, [key skill latest].[Course Code], [key skill
latest].[Person Code]
FROM keyskillcomp
LEFT JOIN [key skill latest] ON (keyskillcomp.[Course Code] = [key skill
latest].[Course Code]) AND (keyskillcomp.[Person Code] = [key skill
latest].[Person Code])
WHERE ((([key skill latest].[Course Code]) Is Null) AND (([key skill
latest].[Person Code]) Is Null));

Any help would be great
Rhys