Calculated field - first free number

[PeHa]

Hi, I am totally new in Access, so please correct me if my ideas are nonsense



I have a simple table with just a few fields. It was designed to record the information about valves used in machine we are building up. There is a field for number of valve terminal in which is the valve located and some other fields. What I need is to generate a unique name of the valve. This name will be used by a programmer. This name has a fixed format: xYzz, where x is the number of valve terminal where is this valve, "Y" is constant letter, and zz is a order number of the valve in the valve terminal. For example 3Y6 is the sixth valve in terminal no.3. I thought I can use the calculated filed to populate the name, but I do not know how to get the first free number in the chosen terminal.
[Valve_terminal] & "Y" gifts me the first part of the name but how can I generate the order number?

Or am I wrong and this should not be done in a table?
Thanks for any help.

[CJ_London]

it can't be done in a table - the calculated field can only reference values in that record.

you need to clarify how you determine the order number - at the moment there is insufficient information to do this. what defines valve 6 compared with valve 5? is it counting 'round the clock' so valve 6 is in the 6 o'clock position? perhaps it doesn't matter, it is just the 6th record that happens to be entered?

[PeHa]

Let's say that it is simply the order number - for the specific terminal. For example, at first I will add three valves to terminal 1, their names will be 1Y1, 1Y2, 1Y3, than I will add two valves to terminal 2 (2Y1 and 2Y2), at the end I will add the last one, but I want to add it to terminal one - so its name will be 1Y4. Is it clear? I can attach some pictures if needed, but I don't think it is necessary.

[ArviLaanemets]

When you go this path, you hve to consider:

1. Such ID field is text field. When you continue as you described, then 3Y10 is less than 3Y6. I.e. when your table is sorted by valve terminal number, then valves aren't sorted in right order. A workaround is to have all valve termonal numbers of same length, like 3Y006 and 3Y010;
2. When you make valve terminal numbers fixed length, you have to determine how long the numeric part may be. When you after 10 year find out, that you need more numbers, and make valve terminal number simply longer for new entries, you are back at p.1. I have an IT Devices database, where this problem for DeviceID with similar structure is avoided using another workaround. The DeviceID is in format CCYYYYMMDDnnn, where CC is character string which determines device group (1st character determines main group, like computers, displays, network deviced, etc., 2nd character determines subgroup, like desktop, laptop, server, etc.). YYYYMMDD is the date of device registration. In my case, I can register up to 999 different devices of same device group at same day.

To get the next number you can use p.e. a query, where you select TOP 1 rows of ID ordered by ID Descending and having the filter set for preceeding part. You have to get the numeric part from returned ID [use Right()] and to convert it to number (when no records are returned, the result will be 0) Now you add 1 to gotten number, and compose new ID.

[orange]

PeHa,
Please describe your "business" in plain, simple English---no database jargon. What exactly is a terminal and valve? Once readers understand your needs and set up, they can offer more focused responses. We have to understand clearly the WHAT, before suggesting HOW "it" might be accomplished in MS Access.

[PeHa]

Hi orange,
thank you for advice. I thought that will be easier to think about specific things like valves and terminals than talk about "items" and "groups". Terminal is simply a group of valves assembled next to each other. But maybe you are right. Well, I do not know how to describe it better, which is probably my fault...
I have a table with records. Each record (valve) belongs to a group (terminal). What I need is to create automatically a unique code for each record. This code consists of (1) number of the group which the record belongs to, (2) letter "Y", and (3) sequence number in its group.
Is it better? I did my best

[PeHa]

Hi ArviLaanemets, thank you for the advice.
Yes, I consider the fixed length. It has been decided that the format will be xYzz, so the first valve will be 1Y01. I only forgot to write it.
There is no risk to reach 100 valves, because this will be specific for each machine. Every machine has its own database. There is also no risk that we have more than 9 valve terminals on one machine. So I find it ok.
Unfortunately I do not understand your last paragraph. As I said I am absolutely new in Access. I made the table "Valves". I add there fields like "terminal_ID" (defines in which terminal the record belongs to), "Valve_ID" (the code xYzz i want to generate) and some others. Than I made a new Query based on "valves" table, I add there a field "Valve_ID" and sort it. Here I get stucked... Maybe chosen the wrong type of query...

[PeHa]

orange, let me explain what I need regardless of access and database. We build up assembly machines. As the designer creates the machine, he must identify some of its parts. He mast give a name to every single cylinder, valve, sensor etc. so we can reference the name in other documents (user manual, program, risk analyse, pneumatic schema etc). Therefore we decided to make a database where we store all these components with some details (p.e. type of the valve). I want to start with the table of valves as I described above. Because the valves are grouped to valves units called terminals we use the terminal number in name of the valve. So, if i see the valve named 2Y06 I know it is the sixth valve in the second terminal.
What I want to do is to prevent that somebody will define a wrong number - for example he gives the same name to two valves. Or skip a number. That is the reason why I would prefer if the name is generated automatically.

[PeHa]

wouldn't be better to create table for each terminal = each group of valves? Than we can use simply automatic numbering...

[orange]

It seems to me that there may be confusion with the item (say Valve 26) and a location in some assembly (say 3rd from top in clockwise direction).
If a valve is identified with its usage location, then it seems you could not interchange one valve with another. And that seems very limiting (and may just be my misinterpretation of your post).

Consider this analogy:

You have a house with 6 rooms --kitchen, bathroom, living room, 2 bedrooms and a back sunroom. Let's also say you have a number of lightbulbs (60W, 100W...).

You could put a 100W bulb in the ceiling socket of Bed room #1, and a 100W bulb in the lamp in the living room.

The bulbs (valves) are still 100W. These bulbs could be interchanged, and they would still be 100W bulbs.

In my view, you may be confusing the geometry of the usage with the specific item (bulb, valve...) being used.

Can you use a particular valve in different locations in your assembly? If so, then I think item and where used are different concepts and must be handled separately.

[PeHa]

hi orange. The example with bulbs is very good. I need to generate the code that identifies the bulb in the house. Let's say that the first letter defines the room where is the bulb located and the end of the code is the sequence number in this room. It is necessary for the programmer. He needs to have a clear identification of the valve/bulb. If the garage gate is going to be open than program switches on the bulb 3B01 - the first (01) bulb in the garage (room no. 3). If a bulb is broken, the landlord gets the information from the house computer - please check the bulb 4B02 - the second bulb in kitchen...
The lightbulb power (60W, 100W...) or the type of the valve (5/2, 5/3NC ...) will be definitely another field of our database.

[orange]

I suggest you show us a picture/jpg of one of your assemblies that shows the various valves.

[PeHa]

why not. here are the pictures - 1.zip
1 - machine
2 - here you can see two valve terminals
3 - one terminal (the numbering was slightly different here)

Anyway, there is no discussion about how we will name the valves. We do it like this for many years and we are absolutely satisfied with it. If access can't create it automatically (I do not believe it, because Excel can), it will be done by user.

[CJ_London]

not sure if it will meet you needs but suggest using something commonly known as DMax +1

Because your naming code is alpha numeric, you need to strip off the 'given' part. Would be much easier if you just stored Terminal number in one field and valve number in another field and concatenate with a Y when required

If this was the case then from '[Valve_terminal] & "Y" gifts me the first part of the name but how can I generate the order number?'

newValveNumber = dmax("ValveNumber","tblValves","TerminalNumber=" & TerminalNumber)+1

newValveName=TerminalNumber & "Y" & format(newValveNumber,"00")

What I want to do is to prevent that somebody will define a wrong number - for example he gives the same name to two valves. Or skip a number.

This should be applied just before the record is created (typically the form before update event). If you were to do so earlier then in a multi user environment there is a risk that two users can create the same number - imaging the situation user A uses DMax to create a new number but before saving, goes to a meeting/lunch whatever. In the meantime, user B creates and saves the record.

Note this will still give gaps if a record is subsequently deleted, so numbers can be skipped in this way

[PeHa]

Ajax, it looks interesting.
It would be OK to have terminal number and valve number and merge them only when needed.
The user will define the terminal where is the valve located. I want that the number of valve is generated automatically to prevent the numbering mistakes.
As I am thinking of it, the DMax+1 calculation could be done somehow by the Form used for database input - a user want to add a new valve, starts a form, access checks the first free number and put is as default text in input field in the form. The user can simply accept it or change. Access would check the number once again when the record is entered and should be written in table.
Am I right?

When a record is deleted lately, there will be a gap in numbering. That's OK.
Only one user will enter the data to a database. We don't have to care about two or more users at the same time.