custom product code numbering within a primary table

[Lesg]

Hi Guys, just looking for ideas on how I should approach the following client request.

We have a primary table tblproducts and all products have been traditionally identified as via the primary key productID

Overtime this table has become full of products that the client is now working to slim down to a 'product range' they want to be there listed products as part of this process clients is wanting to create a new numbering method to identify product by code range i.e. frozen products range 20,000-29,999 then fresh products in range 40,000 - 49,999 etc. the idea being a new column 'code' added the tblproducts. Products are already assigned to these categories by link table, but they now want to have this numbering method.

So I am looking for a way client will be able to add products into these ranges, ensuring all numbers are sequential and unique so any thoughts or suggestions on the best way to do this would be appreciated.

[CJ_London]

seems a waste of time to me - what is the benefit?

but if you want to do it perhaps use an update query along the lines of

Update tblProducts SET Code=20000+dcount("*","tblProducts","productPK<" & productPK) WHERE CategoryFK=1

run for each category, changing the 20000 and modify the where clause to however your are assigning them at the moment

and pray the client never has more than 10000 products for any given category

moving forward, to populate the code field for new products, google "dmax+1"