display code based on a name

[Lila]

I have a field in one table called "insurance company". I have used this
field for years. Now we want to display a three letter code for the insurance
company based on a new table that contains the insurance company name in one
field and the three letter code in a seperate field.

Is there a way to create a query that displays the code? One more glitch...
not all records have the insurance company field filled in and it is possible
that someone might have an insurance company that is not on the list.

I thought about re-creating the insurance company field based on a look-up
since it would show the company name on the form, but really be storing the
code in the field but when using a lookup field it seems a bit sporatic
whether the code shows up in results or the name of the company.

The bad thing about that idea is that I would have to go through all 6000+
records and re-enter the data, but on the other hand it would give me a
chance to enter any insurance company names into the new table that doesn't
already exist.

Any thoughts?

 

[John Spencer]

Having a table with the Insurance Company name and the abbreviation sounds
is the correct way to do things. Also, any other information that relates
to the Insurance Company should also be in this table (Address, Contact
Number, etc)

In the current table you would have just one field to identify the insurance
company. Then when you need to have other information about the company you
would add the Company table to a query along with your current table. Join
the two tables by dragging from the identifier on the company table to the
corresponding field in your current table. If your current table doesn't
REQUIRE the insurance company to be filled in, then double click on the join
line and select all from the current table and ONLY from the insurance
company table.

I suggest that you only allow values in the current table that are in the
company table.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Lila]

Thank you for your quick reply. After I started playing with this it wasn't
as hard as I thought. Once I created the relationship and added the new table
to the query the matching code came up.

Thanks again!

 

[Lila]

Actually, I just realized that now that I added that table to the query... I
can't modify any information in the query which is very bad because I don't
use the raw information on the form, I get the information from a query.

How did that happen???

 

[Dale Fye]

Check out the list of reasons that a query may not be updateable on Allen
Browne's website (http://www.allenbrowne.com/ser-61.html)

--


email address is invalid
Please reply to newsgroup only.

 

[Lila]

Perfect! I didn't have my indexing correct!

 

[John Spencer]

Did you designate a field in the new table as the primary key? Do you have
a primary key in the other table?

If you use more than one table in a query and want to update records, you
need to have the primary key of both tables in the query.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Lila]

I do have a primary key in both tables, but the relationship was with two
fields that weren't indexed. The "insurance company keycode" is the primary
code on the "insurance company table", so I changed the "insurance company
name" to be indexed with no duplicates as well, and it worked like a charm.

Lila