Access 2003 - Key Violation.... help?

[elleg]

Hi All,

This isn't a VB question, but I was hoping someone familiar with Access databases might be able to answer this anyway...

A co-worker of mine usually calls me when she needs a query added to her Access database, and until today, I haven't had any problems, though I'm used to dealing with Oracle, not Access.

Today, I came across a problem I couldn't explain. Here's the relevant information for this problem:

There are two tables, let's call them tblProcs and tblJobs.

tblJobs has 3 columns, one is JobID (a number, and the primary key of the table), and the other 2 don't matter for this problem. This table has a complete (unique) list of all job titles.

tblProcs has 2 columns, which jointly make up the primary key. These two columns are JobID and ProcID, both numbers. What this table contains is a list of all the procedures (procs) that are assigned to different job titles. A single ProcID can occur multiple times in the table, as can a single JobID. The only limitation (given the joint primary key) is that you cannot have a specific ProcID assigned to a specific JobID more than once.

Here's a very simplified example of the data in these tables:

tblJobs
-------
JobID
-----
1
2
3

tblProcs
-------------
ProcID	JobID
------	-----
100	1
100	2
200	3
300	1

What my co-worker wanted was a way to assign a specific procedure to all JobIDs at once, rather than having to enter that procedure repeatedly for every single job title. This translates to adding more rows to tblProcs, and the only complication is that if a JobID already has the procedure in question assigned to it, we don't want to try to add that row again or we'll violate the primary key of that table.

Given the sample above, let's say my co-worker wants to assign procedure 300 to all job titles. Currently, it's only assigned to JobID = 1. So, we want to add 2 more rows, (300,2) and (300,3).

This is the query I came up with to do this...and I verified that the SELECT part of the query only "creates" a record where one doesn't already exist.

INSERT INTO	tblProcs (JobID,ProcID)
SELECT		DISTINCT j.JobID
,		[Procedure ID]
FROM		tblJobs j
LEFT JOIN	tblProcs p
ON		j.JobID	= p.JobID
WHERE		NOT EXISTS	(
				SELECT	p1.JobID
				,	p1.ProcID
				FROM	tblProcs p1
				WHERE	p1.JobID	= j.JobID
				AND	p1.ProcID	= [Procedure ID]
				)

When we try to run this query, we get a key violation and no records are added to tblProcs. Access also does not create an error table with the records that did not append.

Now, I just took off the INSERT line of the query, and left it as a SELECT query only. This pulls up a list of ProcID, JobID that she is able to copy and then paste append directly into tblProcs. She gets no errors when copying and pasting the results of the SELECT query straight into the table.

So, why would we get a key violation when trying to run an INSERT query with that same SELECT query???

This 2-step process is still much better/faster/easier than what she was doing before, but I guess I'm just a perfectionist, and I want it to just be the 1-step process of an INSERT statement, and it really bugs me that I can't get it to work.

Additionally, all the ProcIDs we've tested the INSERT query on exist in the master table for ProcIDs (in case any foreign keys exist, which I'm not sure of), and clearly all the JobIDs that are generated are in the master table of JobIDs (tblJobs), so there's no problem there either.

Any help on this issue would be greatly appreciated! Thanks!

[RunsWithScissors]

Try THIS, using SELECT DISTINCTROW instead of DISTINCT:

INSERT INTO tblProcs (JobID,ProcID)
SELECT DISTINCTROW j.JobID,[Procedure ID] AS ProcID
FROM tblJobs j
INNER JOIN tblProcs p
ON j.JobID = p.JobID
WHERE NOT EXISTS
(
SELECT	p1.JobID, p1.ProcID
    FROM tblProcs p1 
    WHERE p1.JobID = j.JobID
    AND p1.ProcID = [Procedure ID]
)

Worked for me in my mock-up.
Isn't it annoying that Access/JET won't do SQL Batches?