Delete query not working, but why?? 2

[ttellis]

Let me start by saying that I am new to Access and prior to this current project, have only developed database for school. My knowledge of coding is basic. Here's the problem:

I've been handed to task of creating a database that holds security badge info. BadgeData is the active badge data, but once an employee is terminated, I need to have the data moved to Termed table and deleted from BadgeData. The delete query selects the CardNum from the form IndivData and previews the correct record, but does not delete it. The message I received is: "Could not delete from specified tables. (Error 3086)". HELP says the error means the database was opened in Read-only or that I don't have permission to modify the table. Neither of these are true.

Here is the SQL for the query:

DELETE BadgeData.*, TempTermed.CardNum
FROM TempTermed INNER JOIN BadgeData ON TempTermed.CardNum = BadgeData.CardNum;

Any help that can be provided would be great!I'm all ears...

Tiffany

 

[JonFer]

Add a unique index or primary key to CardNum in each table and try it again.


John

http://www.interautomateconsulting.com

 

[ttellis]

JonFer,
The PK in BadgeData is CardNum, but no PK exists in Termed. Since the badges are recycled, the Termed table will have to hold duplicate records with the same CardNum value.

Tiffany

 

[JonFer]

Add a unique index then that includes CardNum with some other field. If that won't work, add an autonumber field and have both that and CardNum in the unique index.

John

http://www.interautomateconsulting.com

 

[ttellis]

Thank you John!!! I've taken your suggestions and wnet back in and made some modifications. It's now working like a charm!!

 

[KornGeek]

John,

I was having a similar problem with one of my delete queries, and you post helped me find the error (I had accidentally removed my unique index).

Thank you.