By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,363 Members | 618 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,363 IT Pros & Developers. It's quick & easy.

how to compare a value to all other values in the same field

P: 3
I am an access newbie but i have been searching and can't seem to find a good way to identify values in a single field that are repeated.

my field is [VIN] and i tried using the expression iif([VIN] = [VIN],"Duplicate", "Unique")

however this returns every record as a duplicate...

if i can pull this off i then wanted to add in the field [Program] to find records in which the [VIN] and [Program] value are the same....

any help would be appreciated:

example

VIN Program
123456 100
123456 100
122222 200
122222 300
133333 400
144444 400

so
my expression in the querry would result in only the first two records being displayed
VIN Program
123456 100
Jul 10 '07 #1
Share this Question
Share on Google+
4 Replies

nico5038
Expert 2.5K+
P: 3,072
Not sure what you want to achieve.
When you just want to identify the duplicates you should create a groupby query with the ID and Value field. Just place them in the graphical query editor and press the "E" looking button. An additional row will appear underneath the fields with "GroupBy". Now add the ID again and change the "GroupBy" into "Count".
This field will tell how many rows with the same two values appear and by giving the criteria: >1 you'll find the dupes.

Does that help, or do you need something else?

Nic;o)
Jul 10 '07 #2

P: 3
see belllow for the answer
Jul 10 '07 #3

P: 3
YOU ARE THE MAN!....and i feel like an idiot.....i used the wizard and pulled it off perfectly just as you said (but it wasn't so clear to me)

i looked at the expression the wizard created to accomplish this and it follows:



In (SELECT [VIN] FROM [clms_mpbtt_d0701] As Tmp GROUP BY [VIN],[Program Number] HAVING Count(*)>1 And [Program Number] = [clms_mpbtt_d0701].[Program Number])
Jul 10 '07 #4

nico5038
Expert 2.5K+
P: 3,072
Glad I could help, have felt myself "stupid" many times in the past, but it did teach me a lot of valuable lessons <LOL>

Success with your application !

Nic;o)
Jul 10 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.