Newb help PLEASE!

[Jette3173]

Hello, I am trying to create an easy way to update a file name column on an inherited table.
This will need to consist of two existing data fields already on the table and I am trying to see if I can make thing easier in the long run.

I have Job Title and Organization fields. and I am wanting to abbreviate Organization (there are 20 options).

The first thing I would like to accomplish with this is on my table, I'm thinking the best approach is to have a new column and create a query to state Org1=1, Org2=2 etc.

The only way I have been able to find/think of accomplishing this is via an imbedded IIF statement. Does anyone know of a way to accomplish this?

 

[welshgasman]

I would probably do it this way.
Create a table B from a query that produces unique organisation names.
Add a new field in Table B for your 2 etc, or use an autonumber field.
Add a new field to your table A that will hold this new OrganisationID
Then update TableA from TableB using the original organisation name.

I am not usre where your title comes in.
When all is working you can removed the organisation text field, but be careful that you have changed anywhere it is being used to the new numeric version.

 

[Jette3173]

I would probably do it this way.
Create a table B from a query that produces unique organisation names.
Add a new field in Table B for your 2 etc, or use an autonumber field.
Add a new field to your table A that will hold this new OrganisationID
Then update TableA from TableB using the original organisation name.

I am not usre where your title comes in.
When all is working you can removed the organisation text field, but be careful that you have changed anywhere it is being used to the new numeric version.

Thank you for the idea!
In the Query, would an IIF statement be the best approach to link the unique OrganizationID to a abbreviation?

 

[welshgasman]

No. I thought you wanted to replace the organisation name with an Abbreviation or key, like for US states.
So my solution is to create a table with distinct organisation names. Then add your abbreviations. Then you link on the old organisational name.

 

[Jette3173]

No. I thought you wanted to replace the organisation name with an Abbreviation or key, like for US states.
So my solution is to create a table with distinct organisation names. Then add your abbreviations. Then you link on the old organisational name.

Oh okay, I need both. When inputting data my coworkers will be inputting the full name on a form I have created.
I want to use the abbreviation to combine with another column and create a file name.
Kind of like A + B = C where B is the abbreviation.

Using states as an example:
This way when they input Texas the column autofill's with TX and then the file name has TX in it.
This may not be something I can do in Access though.

I have attached an screenshot of an example, I am trying to Link Field3 to Field2 so I can easily create Field4 with hundreds of records for an export.

 

[Micron]

If you have those fields in a table, it is a simple matter of showing Texas in a combo box but storing the id of the record. Then when you want to show the data in a form or query you bring in the short form field based on that id.

 

[welshgasman]

So wherever you org names are, add a new field for the abbreviation.
Then bring that in with your data for the control. I am assuming you are using a combo for that to make data entry easier and less error prone?

You do not store that filename but calculate it when it is required.