Getting ID field instead of data field displayed

[sierra467]

I am not sure if this is a Report question or a Query question, so if I am asking in the wrong place, please forgive me.

I am building a report that will be deriving its information from a single table tblStudent. In this table there are 2 fields fldTeacher1 and fldTeacher2 and these fields are populated from a lookup in tblStudent from tblTeacher.

In tblStudent, the fldID from the tblTeacher is being stored in tblStudents.fldTeacher1 and tblStudents.fldTeacher2, while the teacher's name is being displayed in the table.

Having said that, when creating the record source in the query builder, I can obtain the names of Teacher1 (Bob Smith) and Teacher2 (Fred Star) seperately - each in its own column. However, what I really need is to have Teachers: fldTeacher1 & " and " & fldTeacher2 (Bob Smith and Fred Star) in one column.

The problem is that instead of getting the text "Bob Smith and Fred Star", I get "3 and 7" where 3 and 7 are the fldID# for Bob Smith and Fred Star. How is it that I can get the names and not the ID numbers for these teachers?

Please help if you can see what I am talking about.

 

[sierra467]

Thanks Pat for the clear instructions and I think I followed them correctly, but it did not work.

I got an error stating "The SQL Statement could not be executed because it contains ambiguous outer joins. To force one of the jons to be performed first, create a seperate query that performs the first join and then include that query in your SQL statement"

I am new at reports and they seem very aloof to me. Although I could not get your suggestion to work, I played around and created my own solution - good or bad it seemed to work.

What I discovered was that I originally added a text box to the report and then tried assign the fldTeacher1 as the control source - this displayed the ID field number and not the data field as I discribed above. So then I displayed the field list and decided to drag off the field I wanted and when I did, I observed that it showed up as a combo box. When the report was placed in Print Preview View, the Teachers Name finally displayed instead of its id number!

With that new revolation, I realized that I could place each teachers name in a combo box to be displayed on the report or I could hide the combo boxes and then use their contents as a reference to fill and concatinate the names into a text box. It may be a bit of a cluster, but it seemed to work.

In your post, you also mentioned that I should never use a lookup at the table level - what should be done in stead? I would think that if you only left the lookup at the form level instead of the table level, someone could get into your tables and enter inconsistant data that would not meet the criteria for that field (like limit to list, or in my case, a teacher's name that might not be in the list or a slight misspelling of the name - that sort of thing) and cause your data to become inconsistant or "corrupt" (if that is the correct use of the term).

Is there someway to keep users out of your tables and keep them only in the forms? I tend to have people who like to "poke around", not knowing that they are causeing problems.

Thanks again for your responce, not sure what I did wrong, but I appreciate the helpful directions!!

 

[sierra467]

Thanks Pat. I got it running they way you discribed. I kept getting mismatch errors because of a mistake in the table that i had. Once I fixed that, the query the way you discribed it, worked great. Thanks for the detailed descriptions and the help on the security issues search terms.