How to create a unique composite value

[PackerIntl]

I have a database that consists of 5 tables:

1. Facility - Facility Name, Facility ID
2. Building - Bldg Name, Bldg ID
3. Floor - Floor Name, Floor ID
4. Department - Dept Name, Dept ID
5. Device Type - Device Type, Device ID

All equipment at the facility is defined by where and what it is so concatenating these items will identify a piece of equipment (almost). Besides the combining of the 5 ID fields I need a numeric field to uniquely identify which piece of equipment satisfies the 5. So I might have,
MGMN01CACLWS01, MGMN01CACLWS02, MGMN01CACLWS03, ... as workstations in one department
MGCTMFEMRGMP01, MGCTMFEMRGMP02, MGCTMFEMRGMP03, ... as multifunction printers in a different department

and so on, always starting at number 1 for each unique combination of the 5 concatenated fields.

What code or key could be used in Access to determine/create these last two characters of the device name after the other 5 fields are entered.

This Device name will go into a 6th table that contains other data also such as:

Inventory - Serial No, Device Name (as generated above), Owner, .....

Any help on this would be greatly appreciated.

[orange]

Please post a jpg of your tables and relationships. (printscreen of Database relationships window)
Show uses what your design is before getting too deep into concatenated fields.

[PackerIntl]

Attached is the requested .jpg of the database relationships. In table DeviceName, the field DevName = CSCode+BldgCode+FlrCode+DeptCode+DevCode+Unique. The Unique field would be a 2-digit string of the number assigned to this type of device in this area, 01 ... 99.

This is what it all looks like. It has been a long time since I did any Access development (probably 15 years at least). It is possible that the concatenation could be made from fields on the data entry form which uses Combo Boxes to fill in all of the related fields.

I just need to find a way to create/supply the data for Unique with it starting at 01 for each type of device in each area.

Again, Thanks.

[orange]

It would also be helpful if you gave a clear description of what business this database is to support.
Are you saying that if you added another building to your "business", that DeviceNames would/could change?

[ItsMe]

I guess there could be advantages to storing some sort of unique code that is comprised of portions of relative data from other tables. I am not sure I understand the question or problem either but, why not create a function that would create your value for "Unique" and hold it in a string variable. You could call the function when needed for, printing, form view, reports, etc. This way you are not storing calculated data and if someone takes equipment from one floor to another you do not have to maintain the table, just call the function when needed.

[PackerIntl]

The application is to support equipment supported by the IT department across an enterprise that consistes of multiple hospitals in multiple states. The powers that be decided to define a device naming scheme that would tell where any piece of equipment was and what type of equipment it was. This is not for record keeping or looking up by serial number would be just fine. This is so when a user calls in to support dispatch, they can give them a device name to pass along to the support staff and they can know where to go and what they will be servicing (kinda).

At any point in time, the device name is unique to it's location. But it could change if the device was moved to a different location. Also, if the device has to be replaced, the device name for the new device must (should) stay the same as for the device it is replacing so that no changes need to be made to the EMR software saying that a new device is now able to pull records for this department.

Just because a building is added does not mean that device names will change. But devices installed in that new building will reflect that in their device name.

I'm a level 2 support staff who used to do access and am trying to put together a small app to help the IT staff keep track of this miriad of device names. We used to have a system that was simple (Dept + ID). That was before the star ship enterprise landed and wanted consistency through-out. And since all hospitals have basically the same departments, more parts to the identification were needed. And this is what they gave us. Now we have to work with it.

Again, I hopes this sheds some light. And thanks for looking.

[orange]

I answered a poster who had a similar inventory -generally similar- he had equipment throughout buildings and rooms and was trying to swap "bad equipment and spares" sort of thing.

The posts are at https://www.accessforums.net/forms/h...orm-24504.html

and the sample data base I suggested to him is in post #15.

Take a look and see if it addresses any of the issues you are having "with the starship enterprise".

[PackerIntl]

Thanks again for the reply orange and the link to the past postings and your sample DB. But I don't think addresses the issue I have. Say I pull a new workstation from stock that is going to be installed in the Cardiac Cath Lab (my previous name example). Based on where it is going and the fact that it is a workstation, the first part of the device name is going to be MFMN01CACLWS and that is easy. But now I need the DB to tell me what the last two digits will be. If I don't have any workstations there yet it will be 01. But if I already have 4 workstations there (01 .. 04) it will have to be 05. How do I get Access to assign that 05. I don't think converting the value of an autoincrement variable to a string does it because each different combination of the 5 concantenated fields need to start at 1.

I have 2 ideas (guesses), but not sure I have the knowledge to pull them off. One of them would be to count the records of a query that satisfies the concatenation of the 5 fields and increment that value. The other would be to increment a counter in a while loop in VBA.

Does that make sense?????

[orange]

I think you may be focusing on a mis-guided concept. In database we try to design on atomic values (1 field 1 fact). As Ise what management is asking for is that the "visible name/identifier" on your equipment changes depending on its location at any given time.
Consider this analogy to see where I'm coming from. You have children Jim and Jane. Just because Jim goes to the ABC Public School ,you don't refer to him as JimABCPublicSchool. Jane goes to kinderGarten at the XYZ KidzAreFun PreSchool - I don't think you would refer to her as JaneXYZKidzAreFunPreschool. Neither Jim nor Jane's identity changed because they go to some Place. Another example, your car has a VIN number. It doesn't change when you go around the corner, or cross county lines.
Now don't get me wrong, if you wanted to stick a label on Jim or Jane or your car to show exactly where it is with some concocted code, that is definitely your prerogative, but it is not the unique identifier of the person or car. What I'm suggesting is that you follow proper database techniques and have a unique identifier that the "system" can refer to the "object/entity" regardless of where it is located. For Jim and Jane it would be some birth certificate/SSN assigned at birth --unique to the individual. For your car it would be the VIN---unique to your car.

So you let the database refer to your equipment by its uniquely assigned number. It never changes. All equipment moves --floor to floor , building to building are done by that unique number, AND you can put your "location label" on it physically to satisfy management and the tradition that is being followed/dictated by management.
The "location level" is changed on every movement. The unique equipment identifier NEVER changes.

In the previous post I suggested, you will see that poster wanted to continuously change the Primary Key of the record (unique identifier of each piece of equipment) every time it changed location.

In database, you NEVER change a Primary Key.

Good luck. Post back if you have further questions

[PackerIntl]

Orange,

I can understand what you are saying, but it really kind of clouds the issue for what I am trying to do. You make it sound as though the equipment is probably going to be moved around on a regular basis. That is not the case. Once it is installed, it will probably stay where it is "forever". The problem is, take our Emergency Department for example, it has a lot of workstations, say between 70 & 80. If they ask me to install another one, I would like to easily know what the next number should be. And I can determine that if I can count how many other workstations at the hospital have the combination of the first 5 fields that would identify it as being in "the emergency department on the main floor of the Centrury Tower of St. Mary's Hospital". Then this new one would be workstation number count+1.

While each piece of equipment has a serial number that is unique, we want a "unique" name that has some meaning.

What I am trying to do here is "use" Access to create a "tool" that we can use to make creating a new device name easy. Yes, the database will also function as more than just this as time goes on. But when we talk about "field 1, fact 1", the device name is a fact. It is not just a sticker that goes on the front of it. It is the Windows name of the computer if it is one that goes in to Active Directory and Altiris and Symantec management consoles. If it is a printer it is set in to the printer for monitoring by the HP Managed Print Service program that monitors toner needs and servicing needs for printers throughout the enterprise.

So the name will go into the database irregardless of whether it can be determined automatically or has to be created manually. It just seems that there has to be the tools in Access to do this automatically.

For example, I could put a sub-form on the main data entry form that based on a query would show all of the other pieces of equipment where the 5 concatenated fields match the one I am installing, count them, and add 1 to get the next number. But I have to believe that there is a way for Access to do this w/o the sub-form, just plugging in the value I need, which ,in all likelihood, is not going to change next week, next month, or even next year. Except for perhaps 1 workstation that might have failed, all of the in-room computers in our ED have been their since they were originally installed. Problem is we didn't have this naming scheme back then.

[ButtonMoon]

Packer, there's nothing wrong in principle with a key consisting of multiple attributes. An identifier that encodes multiple pieces of information into a single attribute is something that needs a certain amount of forethought (especially if you are designing a coding scheme of your own rather than using one defined by some authoritative source) but it is not necessarily the "wrong" thing to do. Such keys tend to be viewed with caution by database designers but in the end the business user will need some usable identifier and satisfying a basic business requirement ought to trump any database design rule-of-thumb. Populating the value is not a database design issue as such - it's an application design consideration. Model the database first, then design a UI for it.

Orange is a bit too emphatic on the point about not changing keys. It's certainly a good idea wherever possible to use keys that aren't expected to change or will change only rarely but that's just a guideline rather than an absolute requirement. There's no fundamental reason why you mustn't change a key value if it becomes necessary. In database design you will find that pragmatism is more useful than dogmatic rules.

[ItsMe]

@ButtonMoon

Storing calculated data is generally frowned upon. Concatenating values from many fields is nothing new and I do it in most every DB. Storing concatenated or calculated data in its own field, in a table, is only advantageous in certain situations. If a user is going to do a search of a large database, on a calculated value, then I would consider storing calculated data to aid the query as it retrieves the data.

I doubt I could imagine a need to create a Join on calculated data. Although I may index a field that stores calculated data I would not use it as a Primary or Foreign key.

[June7]

I do use a custom unique ID as a key. It follows the structure of YYYYA-####.

Generating custom unique ID is a common topic in forum.

A custom unique ID that could be changed used as a key has risks but I suppose Cascade Update would handle it.

[ItsMe]

...A custom unique ID that could be changed used as a key has risks but I suppose Cascade Update would handle it.

I agree with your entire post and agreeing might by hypocritical. There is always an exception, and I am sure I have generated custom Keys before, at one level or another. I just don't see it as practical in this instance to store the unique name of the device, let alone use it as a key value. I rarely use advertised names/numbers as key values in asset management. I prefer to create another field. Maybe it boils down to preference after all....