display field question

[jseib1947]

I am new to this forum, and I dont know how to word my question to search the forum.

I have been using a single access database since 1997 to track all my companies customers, and their orders.

My database has four tables: employees, customers, vendors, and orders

My customer table has a field called RID, which is a number that indicates the geographical region where they are located.



I would prefer that the field display in reports as a name. For example, if a customer has 2 in the RID column, that is northern Indiana. Is there a way to let access know that and to display as text not a number>?

[June7]

How many regions? If only a few can use IIf or Switch function in expression in query or textbox.

Switch(RID=1,"Region 1", RID=2,"Region 2", RID=3,"Region 3")

If you have many more regions, build a table of them then join this table to the other on the RID primary/foreign key fields.

[jseib1947]

I have 15 RID fields. I guess building a table is the way to go?

[June7]

You have 15 regions? A Switch expression could probably handle (don't know the limit on pairs) but that is a long enough list to justify table. Just a choice of which you like better, building the expression whenever needed or joining tables to retrieve the related info.

[jseib1947]

I am doing something wrong when I create the table. Itws been so long since I did that I have forgotten how to create a new table that is shairng/referencing data from abnother table (in this case, my RID filed that is numeric in the customer table with the new "regions" table that I will create that will add text description of each RID number)

[June7]

The RID field in Region table must be primary key. This could be an autonumber but since you already have the RID value in Customer table, the ID generated by autonumber will not match, so make it a number datatype. Set it to Index (Yes no duplicates), same for the description field.

[jseib1947]

bingo. thats it. Works perfectly. I have to tell you, I step into forums of one sort or another occasionally to try to find answers on my own, and more often than not I become frustrated at either no repsonse, or getting berated for my lack of knowledge or whatever. But you provided me with accurate and extremely helpful repsonses that allowed my work to get done much faster than I could figure out on my own. I truly thank you.