Setting Primary Keys. 1

[lars7]

Hi,
I'm trying to make 2 fields in a table primary keys, "PayNumber" and "PostDescriptor". There should always be only one record that contains the same PayNumber and PostDescriptor in the table (I have checked many time this is the case) but it will not let me save the changes. The message box says that cannot save as it would be creating duplicates. Is there something else that i should be looking for.

 

[rjoubert]

You need to look at your data...there are duplicates. To find them, you can do a group by query, grouping by the "PayNumber" and "PostDescriptor" fields, and adding a Count of one of the fields to your query. Then look for the records where the Count is greater than 1.

SELECT PayNumber, PostDescriptor, Count(PayNumber) AS CountOfPayNumber
FROM yourTable
GROUP BY PayNumber, PostDescriptor
ORDER BY Count(PayNumber) DESC;

Good Luck!

 

[lars7]

Thanks rjoubert,
I had a find duplicates query but it did not show 2 duplicate and I don't know why.

 

[Golom]

Can you post your "Find duplicates" query? It should look something like

SELECT PayNumber, PostDescriptor, Count(*) AS NumberOfRecords
FROM yourTable
GROUP BY PayNumber, PostDescriptor
HAVING Count(*) > 1

You may also want to check for NULLs

SELECT *
FROM yourTable
WHERE PayNumber IS NULL or PostDescriptor IS NULL

because a Primary Key cannot contain nulls.

 

[lars7]

Hi Golom,

Here it is:

SELECT assimilation.PayNumber, assimilation.[Post Descriptor], assimilation.Name, assimilation.PayDivision, assimilation.GroupCode, assimilation.PayPoint, assimilation.[W/M], assimilation.[Old Scale], assimilation.JobDescription, assimilation.[Whitley salary], assimilation.[Spine/Band 1/10/04], assimilation.[AfC Salary Oct 04], assimilation.[Trans Pt?], assimilation.[Spine/Band Now], assimilation.[AfC Salary Now], assimilation.[Inc Date], assimilation.[Protection?], assimilation.ReviewRequested, assimilation.ReviewResolved, assimilation.Reviewdate, assimilation.AssKSF, assimilation.Asslettersigned, assimilation.[Assletterreceived/Posted], assimilation.Notsigned
FROM assimilation
WHERE (((assimilation.PayNumber) In (SELECT [PayNumber] FROM [assimilation] As Tmp GROUP BY [PayNumber],[Post Descriptor] HAVING Count(*)>1 And [Post Descriptor] = [assimilation].[Post Descriptor])))
ORDER BY assimilation.PayNumber, assimilation.[Post Descriptor];

I used the wizard to build the query so I have no idea why it didn't show 2 duplicates.

 

[PHV]

And what about this (typed, untested) ?
SELECT A.*
FROM assimilation AS A INNER JOIN (
SELECT PayNumber, [Post Descriptor] FROM assimilation GROUP BY PayNumber, [Post Descriptor] HAVING Count(*)>1
) AS D ON A.PayNumber=D.PayNumber AND A.[Post Descriptor]=D.[Post Descriptor]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Try PHV's code.

I suspect that yours isn't working because you are matching records in the sub-query only on "[Post Descriptor]" but not on "PayNumber".

 

[lars7]

Hi PHV,
I copied your code and created 1 duplicate but it did not show it and nor is the wizards query either

 

[jsteph]

How are you creating the Primary Key?
--Jim

 

[lars7]

Hi jsteph,

I just highlighted both rows in design view and then pressed the key on the tool bar. Is that what you ment?

 

[jsteph]

lars,
Yes. I asked because one of our coders here was trying to make a compound index by just seperately indexing both fields individually, but you're doing it correctly.

A FindDuplicates query, as created by the wizard should work if you select both fields in question *and* there are no null values in either field.
--Jim

 

[lars7]

Sorry everyone,
I'm a fool (but some of you already know that), I have 2 tables "assimilation" and "tblassimilation" you can guess the rest. the duplicate query works fine.