• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 31
  • Last Modified:

Access 2016 - Delete Query will not work

Hi Experts,

I can't get a Delete Query to work.

See the attached .mdb file, LOADS7.mdb.

My query is "TLD Details - VIC".

Here is the SQL:

DELETE TrafficLoadDistributionDetails.*
FROM TrafficLoadDistributionMetaData INNER JOIN TrafficLoadDistributionDetails ON TrafficLoadDistributionMetaData.ID = TrafficLoadDistributionDetails.idTrafficLoadDistribution
WHERE (((TrafficLoadDistributionMetaData.State)="VIC"));

Open in new window


I am trying to delete 2750 records from table TrafficLoadDistributionDetails, where (TrafficLoadDistributionMetaData.State)="VIC"

When I run the query from the Design menu I get this error:

Could not delete from specified tables.

Regards,
Leigh
0
LeighWardle
Asked:
LeighWardle
  • 3
  • 3
1 Solution
 
Ares KurkluSoftware EngineerCommented:
Hi Leigh,
Are you trying to do this ?

delete *
from
TrafficLoadDistributionMetaData
WHERE TrafficLoadDistributionMetaData.State="VIC"
AND
TrafficLoadDistributionMetaData.ID
IN (select TrafficLoadDistributionDetails.idTrafficLoadDistribution from TrafficLoadDistributionDetails)
0
 
LeighWardleAuthor Commented:
Hi Ares,

No, your query only returns 55 records, it should be 2750 records.
0
 
Ares KurkluSoftware EngineerCommented:
What is your delete criteria exactly? looks like you are trying to delete from  TrafficLoadDistributionMetaData and i don't even think there is that many rows unless if you are trying to delete from TrafficLoadDistributionDetails ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
LeighWardleAuthor Commented:
Hi Ares,

I am trying to delete from TrafficLoadDistributionDetails.
0
 
Ares KurkluSoftware EngineerCommented:
The other way around would the one below, which i don't think this will delete 2750 rows, I am not sure how you came up with that number you need to explain that

delete *
from
TrafficLoadDistributionDetails
WHERE
TrafficLoadDistributionDetails.idTrafficLoadDistribution
IN (select TrafficLoadDistributionMetaData.id  from TrafficLoadDistributionMetaData
WHERE TrafficLoadDistributionMetaData.State="VIC" )
0
 
LeighWardleAuthor Commented:
Thanks, Ares, I am sure your query deletes the intended records.

Thanks for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now