I need to assign a number count to duplicate records. For instance, if a Catalog Number is equal to another Catalog Number, I need to assign them the same number.

So,
Catalog Number: Number:
12345 1
12345 1
22222 2
22222 2
33333 3
33333 3
44444 0
55555 4
55555 4

I thought that I could use a simple iif statement but don't know how to get it to assign the count of records that are duplicates.

Any suggestions?

Thanks!

Before we look at figuring this one out, is there a reason to have duplicate catalog numbers?

If not, it may be worth pulling a list of distinct catalog numbers and then using an autonumber field to assign each a unigue number.

Let us know,

Mike

Mike
EchoVue.com

Yes, unfortunatly there are duplicate catalog numbers that I must review and suggest witch one is to be deleted. They want to be able to group them with the duplicate value underneath according to date.

so:
1 12345 4/2/2005
1 12345 3/1/2003

2 22222 3/1/2003
2 22222 3/1/2002

I would have to rack my brain to remember how to do that, but for now, you could do a simple query, and sort by catalog number and then by date. That will order give you the order that you want. Let me know if you need more than that, and I will see what I can figure out tomorrow.

Hope that helps

Mike

Mike
EchoVue.com

Yes, that is what I ended up doing to show them what they wanted. Will need to do the above at some point, but for now, they are ok!
Thanks for your help!

Kim