Anyone have any suggestions on this? I am at a loss.

Actually, the current DB design allows for multiple sub cats, so you could have Category/sub/subsub/subsubsub and so on, but the data I am using only goes to a sub...

Ok here is some sample data - simplified of course.

use tempInstantStore

IF OBJECT_ID('tempDB..product','U') IS NOT NULL

DROP TABLE product

CREATE TABLE [dbo].[product](

...

The product table contains a lot of records roughly 2800 and the productCategory table has roughly 3850 - should I include all the date?

Sorry I did not see that page you linked, but very helpful. I will get that information out soon.

thank you Chris

Unfortunately I can not change the dthe datatype, the database is for an accounting, general ledger and inventory application(Macola) and changing the datatype will kill the application not to mention...

thank you sRGR'us. Though your statement would have produced a value of 12 due to the RIGHT() funtion I was able to build off of that.

If any one is...