Question Combine fields into one field in same record

[mayestom]

Using Windows 7,
Access 2010.
Is there a way to combine four fields into one field in the same record? I’m trying to build Equipment Code field by using the abbreviated values of Category, Item, Type and Dimension fields.
Example: if Category=Storage System (SS), Item=Wire Deck (WD), Type=Flared (F) Dim=42x52 are the user selected values the corresponding equipment code field would be: SS-WD-F-42x51
This is done in order for all users of the database to build a proper code in the correct format.
What I have so far:
I have the four fields and the equipment code field setup in a form, the equipment code text box has a control source of: =[Category] & "-" & [Item] & "-" & [Type] But when I select values of the four fields and the equipment code is populated I try to save the record and I get an error stating that the Equipment code field needs to be filled out.
Three fields (Category, Item, and Type) are look-ups from other tables and Dimension is typed in.
Can someone tell me if I'm even on the right path as to what I'm trying to do?
Any time spent on this would be greatly appreciated. Thank you.

 

[plog]

Is there a way to combine four fields into one field in the same record?

You shouldn't store redundant/calculated values in a table. Instead you should calculate the value you want whenever you need it. It sounds like you already have the expression to generate the Equipment Code (=[Category] & "-" & [Item] & "-" & [Type] ) to you should just use that expression whenever you need to display the equipment code.

Your specific issue about not saving the value sounds like it is a required field in your table and you aren't giving it a value on the form (its box is calculating it). Like I said before, Equipment code shouldn't be a field in your table, much less a required one. Get rid of it and your form shoudl work.

 

[CJ_London]

sounds like you have an unbound control to calculate the equipment code, but you don't have a control with the equipment code as a control source

I recommend you keep the 3 (or is it 4) codes separate in your table rather than creating a combined value. You can always combine it as you are for display purposes in queries, forms and reports.

To prevent the risk of duplicates you can either have a simple bit of code to check if the code already exists before updating the record and/or in your table create a combined index on the 3 (or 4) fields which does not allow duplicates.

See this link for more details. Post order is out of order but find post #15

http://stackoverflow.com/questions/...icolumn-unique-indexes-on-ms-access-databases

 

[mayestom]

Thank you for your quick response plog.

Equipment code field is required in the table it's supposed to be unique instead of an autonumber. It would take the abbreviated values of the three other user selected fields and typed in Dimension field.
The Equipment code is used in another table but in order to use it the user needs it to be recognizable to them, not just an autonumber.

Example: In Project tbl they will pick a piece of equipment to add to that project. And the way they pick that piece of equipment would be from a lookup on Equipment code field.

 

[CJ_London]

Equipment code field is required in the table it's supposed to be unique

see my post as to how you do this

 

[CJ_London]

And the way they pick that piece of equipment would be from a lookup on Equipment code field.

So the rowsource would be

SELECT ID, [Category] & "-" & [Item] & "-" & [Type] FROM tblEquipment

and you would hide the first column

 

[plog]

The Equipment code is used in another table but in order to use it the user needs it to be recognizable to them, not just an autonumber.

These are 2 different actions: user recognition and data storage. You can have the user select one thing, but store another. If you look at a combo box it allows you to show one thing, but have another value go to the database (this is called the Bound Column).

That means you can use your expression to display whatever the user will recognize, but store the corresponding autonumber primary key in the foreign table.


Edit: I'm slow--CJ and I are talking about the same thing.

 

[CJ_London]

@Plog - since the OP is responding to you, I'll unsubscribe from this thread, otherwise it just gets confusing and I have a lot to get done

 

[plog]

Oh, you're good--just don't type so fast.

 

[mayestom]

CJ London, I'm sorry please don't be offended, I missed your original post - thank you for your input. I appreciate both (you and plog) your time and expert opinions.
I don't think I'm explaining this the right way. I don't need to display the code for queries or reports. I need to use it as my primary key in the Equipment tbl. I understand how to have it being the source of a lookup. But what I can't figure out is how to create the code to begin with.
If I'm understanding what you guys are saying correctly: The user will enter in the Equipment tbl category, item, type, and dimensions and in the Project tbl have a lookup back to Equipment tbl that is a combination of all four fields with a name that they will recognize?

 

[mayestom]

I've attached a screen shot of what my Equipment tbl looks like presently. I hope it helps.

 

[plog]

You've got a few things to change in that table.

A. Put an autonumber primary key on it.

B. Category, Item & Type fields should not hold the full description ("Building"), but the code ("BLD").

C. Equipment_CODE shouldn't be a field in this table, instead you would concatenate the pieces that comprise it whenever you need it.


And I have a question: How did the record with "PKG-MUC-CTN-24x15x11" get the "MUC"? It has no Item value.

 

[mayestom]

-I'll put an autonumber on the table.
-Category, Item and Type fields are lookups from tables that list the full description name and the respective code.
-If I were to take the Equipment_Code out of the table how would a user be able to identify that piece of equipment when using it in another (Project tbl) table? Through the autonumber?

The reason "PKG-MUC-CTN-24x15x11" has the "MUC" is because currently the user is able to make up the code and also input the data in the Item field. Which is one of the main reasons I'm trying to make this "four fields into one" work so all the field values are inputted correctly and the code is generated in the proper format.

 

[plog]

Like CJ and I said in a few posts back, you would have a drop down where users could chose products. The drop down would display your code, but use the autonumber. The drop down would have as its data source a query:

SELECT ([Category] & "-" & [Item] & "-" & [Type] & "-" & [Dimensions]) AS DisplayValue, Equipment_ID
FROM tblEquipment

You then set the BoundColumn property to 2 for the Equipment_ID field. That gets saved in the table, but the code you want to displayed is shown.

 

[mayestom]

I'm sorry plog I'm not following what you're are saying. I've lost track if we are talking about doing this in a form or a table (I'm hoping in a form). Where would the drop down you're talking about be? And how would a drop down display a code if the code is not made yet for that piece of equipment? Would the code you gave me be set-up in a query and used how/where? I'm sorry to be asking such basic questions. I'm new to this forum and I'm relatively new to Access so you need to spell things out for me, if you wouldn't mind. Thank you.

 

[plog]

Where would the drop down you're talking about be?

First lets be clear about what part of the database we are working on. We are a few steps down the road from the Equipment issue you initially talked about and now are working on a way to populate tblProjects. So the drop down we are referencing is on the Project form and will be used to assign a piece of Equipment to a Project.

For reference sake, lets say you have [EquipmentID] field in tblProjects. The form's Record source will be tblProjects and the Control source for the drop down will be [EquipmentID].

And how would a drop down display a code if the code is not made yet for that piece of equipment?

It wouldn't. You will have to populate tblEquipment before you choose a piece of equipment.

Would the code you gave me be set-up in a query and used how/where?

The code I posted in #14 would be the Row Source for the dropdown.

 

[mayestom]

ok I understand. I will implement the changes you are talking about when I get to that point. The only thing is that I'm not at that point yet. I still need to resolve my first problem of trying to create an equipment code. I'm sorry I'm just not getting what I should do for that. I put in an autonumber and used the code instead of the full description for category, Item and Type fields in the Equipment table but how would I make up the Equipment_Code or do I at all?

And when this piece of equipment is used in a drop down in Project table the Category, Item, Type and Dim's would be shown to the user but the database would only store the autonumber in the Project table correct?

 

[plog]

how would I make up the Equipment_Code or do I at all?

You've already posted how to make the Code. In a query you would use this:

EquipmentCode: [Category] & "-" & [Item] & "-" & [Type] & "-" & [Dimensions]

I would make a query based on tblEquipment, bring in all its fields and then use the code above as a field as well. Now whenever you need to reference tblEquipment you use that query and have everything you need. Of course, if you need edit/update the data in tblEquipment you use the table itself.

And when this piece of equipment is used in a drop down in Project table the Category, Item, Type and Dim's would be shown to the user but the database would only store the autonumber in the Project table correct?

Correct.

 

[mayestom]

I'm sorry I'm just not understanding how the code will me made? If it's through the query, how does that result populate the Equipment_code field? And the query would have to be run after a user would enter the other data (cate, item, type, dim)?

 

[plog]

Where is this Equipment_code field you speak of?