Identify Unique Records

[thingssocomplex]

I've data in table and I need to identify unique records (the reference number would not have another i.e duplicated) for example

012345
012345
012346
012347
012346

In this example the unique records is "01247" how can I identify these in Access 2007? Thank you in advance for your support

 

[Dennisk]

add an autonumber column and set it as the primary key.

 

[Brianwarnock]

add an autonumber column and set it as the primary key.

Whist I agree that all records should have a unique identifier I don't see how your suggestion helps here, and maybe they already have that.

Things


drag the field rwice into the design grid, select totals query, leave one as Group By and change the other to Count and a criteria =1.

If you require other fields from the record join this query back to the table on the field in a second query.

Brian

 

[MSAccessRookie]

I've data in table and I need to identify unique records (the reference number would not have another i.e duplicated) for example

012345
012345
012346
012347
012346

In this example the unique records is "01247" how can I identify these in Access 2007? Thank you in advance for your support

Try something like this:

Select ReferenceNumber, Count(*)
From YourTable
Group By Reference Number Having Count(*) = 1

Note: It looks like BrianWarnock said the same thing (Design View Approach). Now you have two choices.

 

[thingssocomplex]

Hi Brian,

Thanks for you help that did the trick, I admit I was somewhat confused with the autonumber because this was already present.

Once again thanks for your assistance

 

[Brianwarnock]

Glad to be of assistance.

Brian

 

[AtLarge]

That's actually a pretty good trick. I can see using that for other things. Brian is da man!