Can you have magic numbers in Access 2007?

Question

How do I store numbers in an Access column and then associate some meaningful string to each value?

Because I don't want to be seeing raw numbers when I can define the meaning of each value once at for all, and have those meanings displayed in the Datasheet View, like:

ID  Name      Type

1   Jack      1 (Friend)
2   Jill      1 (Friend)
3   Diago     2 (Enemy)
4   Sally     3 (Colleague)

Answer 1

Example 1

ISO 5218 sex codes.

Small in number (only four possible values). Are considered stable (not much chance of a new sex being discovered and why change the standard?) Therefore, a SWITCH() statement would be appropriate:

SELECT SWITCH(
              C1.sex_code = 9, 'corporate body', 
              C1.sex_code = 1, 'male', 
              C1.sex_code = 2, 'female', 
              TRUE, '(not known)'
             ) AS customer_person_type, ...
  FROM Customers AS C1 ...

Example 2

ISO 4217 currency codes.

Relatively large in number (approx 175). Are considered to be in flux (one clue is that it has an official agency responsible for maintenance). Therefore, these would be most appropriate in a base table:

CREATE TABLE Currencies
(
 currency_code CHAR(3) NOT NULL, 
    CHECK (currency_code NOT ALIKE '%[!A-Z]%'), 
 currency_name VARCHAR(30) NOT NULL
);

INSERT INTO Currencies VALUES ('AED', 'United Arab Emirates dirham');
INSERT INTO Currencies VALUES ('AFN', 'Afghani');
INSERT INTO Currencies VALUES ('ALL', 'Lek');
INSERT INTO Currencies VALUES ...

---

So which is best for you: sex or currency? ;)

Answer 2

You probably want at least two different tables. One that has the ID and Name of the people, and another table with the Type and Description of how they're related to that person.

If a person can have more than one relationship (e.g. a Friend and a Colleague), you would have a third table that has one column for the ID of the person and another column for the ID of the relationship Type. If a person can only ever have one type of relationship, they you could simply add a third column to your Person table with the ID of the Type of person they are.

To get data out of the tables, you can use JOINs in your SQL statements to join the rows of all of the tables together by the IDs you have specified for each.

In general, here's a link that describes what relational databases are all about. Here's a Microsoft Office link that talks about creating relationships between tables using primary and foreign key constraints in Access that might help you out. Here's another with step by step instructions and a fairly relevant example (Students and Grades).

Answer 3

Well, in your query you would need a join with another table that defines the string values for you magic numbers:

SELECT ID, Name, Type_Text AS Type
FROM MyTable
INNER JOIN MyLookupTable
ON MyTable.Type = MyLookupTable.Type

Answer 4

Sounds to me like you need to create another table

• table: Types
• columns: Type ID, Description

Answer 5

Why use magic numbers for type? Why not just use the words?

Solution 1 -- just use the words, never use the magic numbers.

Solution 2 -- Provide a table that maps the magic number to a word and use a join.

Answer 6

I think you are looking for the SWITCH function...

SELECT Name,
       SWITCH(Type=1, "Friend", Type=2, "Enemy", Type=3, "Colleague") as Expr1
FROM MyTable

More info