Record ID numbers showing on a report instead of name

[tlyons]

Hi,



I am building a recruiting database and each time a request is submitted you select the hiring manager. These managers come from a seperate table which holds a profile for each manager, including all of their recruiting requests. When I go to look at the report, the hiring managers' ids are the only thing that show. Is there a way to display the name of the manager instead of their ID number?

Thanks for your help.

[NTC]

sure; this is not an uncommon confusion point when using a 'look up field' where the display is text but what actually is recorded is the ID....

what you do is add that table in the underlying query/record source - is add that separate manager table with the join (the join may occur automatically) and then add its text name field into the query. then modify your report easily by renaming the control source of that field to this new text field.

hope this helps.

[tlyons]

Thanks, is there anyway to store the information as a text file in the table instead of the ID #?

[NTC]

yes; it really depends how far down the road you are with this design. If it is a long established db - then the change is not something you want to do at this point.

But it if it is new - go ahead. Generally the situation is this: when one makes a 'look up field' type in a table - - it is going to default to 'bind' the key/ID of the lookup table - particularly if the key/ID is the first field. It is trying to do you a favor - because this is the right thing to do generally. However in some cases it doesn't help. If the text is unique - then it is easier not to have a key/ID field at all or to at least control the 'bind' during set up so it is on the text field. Consider the typical case of the 2 letter USA state abbreviation. It is simpler just to bind to the text and not bother with any key/ID field at all.

In your case you can go to the table, in design view - and change that fields look up query bind info. This does not reverse any stored data - so you would need to delete those records or modify them via an Update Query.

Happy 2011.

[claven123]

I may be off, but. If you have it set to column count 1 or 2 etc... it will only show that many.

The next part is showing how big each column is, say 0" or 2" etc...

I would get a number instead of the real text. Until I changed the order of data in the query or placed the first column to 0 or the second to 0 until you get the value you want.

Hope this helps.

Dennis

[LillMcGill]

Thanks, is there anyway to store the information as a text file in the table instead of the ID #?

Yes; it is called Excel. Seriously though, NTC's first post is simple and how databases are done.

[drogers1988]

I am having a similar issue... I have a combo box on a form that is looking up the values from a table, the table that is actually storing the data is saving the ID and not what was selected in the combo box, I changed the boundcolumn and it worked but it messed up a few other fields I was using. So I have to keep the boundcolumn to 1. I think NTC's first post would probably work for me but I'm not understanding how to do it. Any help would be greatly appreciated.

[shaha]

anyone found solution to this? i need help with this.

[NTC]

My first post remains valid.

To implement inside a combobox - in the combobox property look for the Data property - which is an sql statement; press the "..." icon on the right and it will open in standard query design mode...

there you can add the necessary table and join so that you can include the textual description of whatever ID is being currently displayed.

note that when you add a column in query design, that when you return to the combobox's property you must modify the properties involving the number of columns/fields, their widths, etc.

Hope this helps.

[Stingaway]

I am having a similar issue... I have a combo box on a form that is looking up the values from a table, the table that is actually storing the data is saving the ID and not what was selected in the combo box, I changed the boundcolumn and it worked but it messed up a few other fields I was using. So I have to keep the boundcolumn to 1. I think NTC's first post would probably work for me but I'm not understanding how to do it. Any help would be greatly appreciated.

You don't change the bound column, you change the column width property as mentioned above... set the first property (the bound column) to 0, the second to 1. Separate each column width with a semicolon. You can type in 0;1.5; 2 and Access will change it for you by adding the " sign as in the example below:

Example: Column Widths - 0"; 1.5"; 2"