Calculated field question (1 Viewer)

[Chris_A]

Hi all,

I have a table that contains a field "Category" and a calculated field "SKU". What I want to do is have the SKU field generate a value per row that is of the format INV/CATEGORY/NUMBER where number is the amount of times the category value has appeared in the table up to that point e.g. if that row is the 3rd time it's appeared it would be INV/CATEGORY/003.

Is this something that's possible ? I've looked around and can't find anything that's exactly what i'm trying to achieve.

Any help would be appreciated.

 

[theDBguy]

Hi. Welcome to AWF!

Not sure you can do this using a Calculated Field in a table, but you might be able to use a Calculated Column in a query.

 

[plog]

You shouldn't do this as you have stated. And possibly not at all.

What is this SKU for? Presumably you should have an autonumber primary key on that table and it should be the value you reference when you want to uniquely identify a record, not some concatenated frankenfield. Exactly how will people use this SKU?

If in fact this is warranted, then you would not merge those 3 values together in 1 field, you would keep them seperate in 3 fields [INV], [CATEGORY] and let's call the new one [SKU_NUMBER]. Then in a query you could merge them together to produce one string that displays as you want.

When you need to populate [SKU_NUMBER] for a new record you build a function to obtain the "next" number. You would pass that function the [INV] and [CATEGORY] value it would do a DMAX() to get the highest [SKU_NUMBER] for that [INV]/[CATEGORY] combination, add 1 to it and then that would be the value you put in the table for that new record.

 

[Pat Hartman]

I too question the way you are doing this but here's a sample of how to create a sequence number, which is what I think you are looking for.

Custom Sequence Numbers

Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below

www.access-programmers.co.uk