Code or Description 1

[Prufrock]

I have a database I am building. I have a lot of tables which come from a Government source we need to follow. They have codes and descriptions which appear to be unique. For the basic end users it would be easier to store the description such as country rather than the code. Or I can used lookups and they can see both and select country but code is stored.

I suspect the second way is preferable but then if I am going to run reports etc and need the descriptions and only have the code stored will that mean I have to run dlookups? Or is there a simpler way to both cover the store issue and make developing the reports, allowing access to forms which will often be referred to etc easier.

If I store code won't they just see that when they go back to look at data in the forms? What is the best way to overcome this dilemma? Thanks in advance.

 

[lameid]

You are right, definitely store the code. This is a called a foreign key. It relates to a value that is unique in another table.

To handle the form, use a combobox. Do yourself a favor and ensure you have the advanced wizards installed for Access.

Back to the tables for a moment. You should have a table that has at least 2 fields. I.e.

RegionCode Text 3
RegionDesc Text 50

I assumed the governments code is text but if possible you should use a number datatype.

Then just add the RegionCode field with the same size and datatype to the other table.

Once both tables are created, make your entry screen (form) for your main table.

Find the text box the form wizard created for the Region Code and delete it. Next add a combo box to the form. Tell the wizard (that appears if advanced wizards are installed) to use a table or query to look up values and specify the table with the list. Tell it to store the value in a field and specify RegionCode (or the field name you actually used). Finally, let it Hide the key column. On the screen in form view you will only see the region descriptions.

Rereading the below, a novice may not be able to muddle through it. If not, just post again.
Next for reporting, you will have to make a query and include both tables. Hopefully it will automatically draw a line between the 2 region code fields. If not drag and drop region code from one table to the next. You can double click fields from the tables to add them to the list below and these are more or less the fields the query will return (less if we were to cover more than the simplest query).

Technically speaking the line is a Join. You can control behavior about it by double clicking it. If you hit the line you will see a dialog with 3 numbers and bullet options (option group control). If you miss and double click the gray area you will get a dialog that says query properties. The three options are self explanatory so I'll leave that for you to choose but chances are you want a 2 or a 3. Which one you want is determined by which table you dragged the region code from.

 

[Prufrock]

Thanks for that. Confirms most of my thoughts and will sort out going back over forms to see what country, language etc is listed rather than code. My only query is if I am putting a detailed report together which is really a printout of a form which contains many of these combo boxes for language, country etc, is there an easier way than having to run a query which would have many many linked tables wouldn't it? Or would I be best to try to print the form with a single record in it?

Thanks for your help

 

[lameid]

It is best to use the query for a report. A query will be much more efficient than a combobox on a report used to find a value. And yes, definitely make a report. Forms are really not made to be printed although you can. Reports have a clear background. Forms will just waste toner.