Showing first and second columns of a lookup table

[Ishaaq Datay]

Hi there

I am a novice access user using Access 2003 in a Windows XP Home platform.

I have a table containing 2 colums, the first containing a code (CODE) and the second containing the description (DESCRIPTION) of the code.

the first table thus looks like this

CODE DESCRIPTION
001 infection
002 cancer
003 injury

I use the information contained in this table to catergorise variables in a second table.

Within the second table I have the column with the rows of data that I wish to recatergorise (DATA), a second colum with the code which looks up data from the CODE column of the first table, and a third column in which I would like to have the decription of that code (DESCRIPTION) displayed for that particular code.

Thus I would like the second table to look like this:
DATA CODE DESCRIPTION
tonsillilitis 001 infection
pneumonia 001 infection
lung cancer 002 cancer
Head injury 003 injury
skin cancer 002 cancer

I have managed to do a lookup for the CODE column successfully using a combo box of the lookup of the code field in the second table. I would like the description of that code to be automatically displayed on the second table as I am editing it. I have tried setting the column count and bound column as 2 under the lookup of the code query, but this has not helped.

The only display that I get looks like this:

DATA CODE DESCRIPTION
tonsillilitis 001
pneumonia 001
lung cancer 002
Head injury 003
skin cancer 002


I hope that somone can help me figure this problem out.

With Peace

[Ishaaq Datay]

I am not sure if my last post made sense, so I if I need to rephrase the question, please let me know.

[Rabbit]

Bound column is what is stored. What you're looking for is column widths. So if you have 3 columns and only want to see the third column, you would use 0;0;. This will hide the first 2 columns.

[Ishaaq Datay]

Thank you so much for responding

Your suggestion has helped me somewhat, however it has not completely resolved the issue. Using the colum widths, I set it to 1,3 since I wanted to see both the code and the description in the combo box. This helps me to see the description of the code only when I click on the drop down menu.

I would like to be able to see description of the code alongside the code in a separate uneditable column, next to the code. I have tried to creat a new column in the second table called description of code, and have used the sql statement builder to link in the data from the first table. What this does hoewever is create a seperate dropdown menu in the description column that is unrelated to the code in the column next to it.

I am needing to type in the code and have the corresponding description automatically place in the colum next to it that is visable without having to click on the dropdown menu.

Ishaaq

[Rabbit]

Use a listbox rather than a combobox.

[Ishaaq Datay]

I have tried that but it still does not work.

Just to restate what needs to be done. Table 1 has the numerical code and the description. Table 2 sources from table 1. In table 2, I enter in the numerical code. The list box displays both the numerical code and description now, however once the data is entered, only the numerical code is left in the field. I want both the numerical code and description to be displayed, either in the same column or a different column. The description must correspond its numeric code but must not be editable. The description must come up automatically when the numeric code is entered on the screen and continue to be displayed afterwards when I move to another field. The description must ideally be in a seperate column to the numerical code but must correspond to its numerical code as what it is in table 1.

I hope this is clearer. Would you like me to send a screen shot of what I am doing?
Thanks

[Rabbit]

So you want to store both columns of data? The listbox should let you view multiple columns of data. There's no way to do this at the table design level. You can do it from a query or a form.

[Ishaaq Datay]

Thanks

Building query has given me exactly what I needed.

Your help was greatly appreciated.

[Rabbit]

Not a problem, good luck.