Delete query

[Matizo]

Hi,

Why is this delete query not working?

DELETE tblAllocations.*, ([tblAllocations_1].[EmployeeNo]=[tblAllocations].[EmployeeNo])
FROM tblAllocations, tblAllocations AS tblAllocations_1
WHERE (((([tblAllocations_1].[EmployeeNo]=[tblAllocations].[EmployeeNo]))=True));

Thanks guys!

 

[LPurvis]

Wow.
Query death by QBE. ;-)
The column expression and brackets are completely typical of a QBE query with an accidental column - but the question is: What are you actually wanting to delete?

You've the same table added twice, aliased once and the comarison is going to find nothing but records where EmployeeNo is not Null.

In general, your delete query would look like:

DELETE *
FROM tblAllocations
WHERE [EmployeeNo] Is Not Null

Or whatever criteria you're wanting to apply.
(Which would be..?)

Cheers

 

[Matizo]

Basically I'm trying to delete double allocations. for example employee A cannot be allocated twice to the same job. Hence, I don't know how to prevent double allocations I'm trying to run the query to delete them when the occur...


Cheers!

 

[LPurvis]

Even with the right query - deleting them requires a UI event.
You're much better off enforcing this at the database level.

Creating a unique index on the job and employee FKs in your allocations table will prevent duplicate assignments.
For example, remove all the duplicates manually and then run a query like the following to establish the unique index.

CREATE UNIQUE INDEX idxNoDups ON tblAllocations (EmployeeNo, JobNo)

(It will fail until there are no dupes).
As far as finding the duplicates - you can use the query wizard (can't believe I just said that lol).

Delete all but one of the duplicates - you don't want to delete all duplicates remember.
For example, to remove the "last" duplicate, something like:

DELETE * FROM tblAllocations A
WHERE EXISTS (SELECT Null FROM tblAllocations B GROUP BY B.EmployeeNo, B.JobNo HAVING Max(B.PKID) = A.PKID AND COUNT(*) > 1)

Where PKID is your primary key field of the tblAllocations table.

Cheers.

 

[Matizo]

Hi

I've got tblServices, tblEmployees and because it is many-to-many I use tblAllocations to assign employees to services (in tblAllocations I've only got EmployeeNo and ClientNo)
I used primary key for EmployeeNo and ClientNo so it works now... I didn't know that it possible to have one primary key for two fields..

Cheers!

 

[LPurvis]

It's certainly possible to have a PK consisting of more than one field.
(It's very much not my preference - due to the requirements of then referencing that PK from other tables - but possible).
However I was simply refering to adding a unique index to the table across those fields. (That's not the same as assigning those as the PK).

That way you can keep whatever PK you had before (an autonumber for example) which is easily referenced externally.

The delete query as offered should still help get you to a non-duplicate place as required.

Cheers.