MS Access: Lookup Field show a mix of ‘Looked up’ Names and Record_ID when using Unfiltered View

Several points to make here. 

1. You can filter records without using the poorly designed lookup fields. Experienced developers have found that these cause more problems then they are worth. You can, and should, use list controls on your forms, among other tools, to speed data entry. That's one of the main reasons for forms. I'm not even sure what you mean by; "temporarily 'filtering our' irrelevant records from the Lookup field". If you need to control the RowSource of list controls there are better ways to do it.
2. In point one you refer to "doing things faster...make data entry faster and easier". Yet you talk about entering one order for each item, even if a customer orders multiple items. That is a big contradiction! It is a rare instance where a customer will only order one item per order, usually only with big ticket items like a car. So for efficiency sake, you need an Order Details table. And you need a Products table.
3. This is not a good idea. Every database has its own nuances. So starting with a generic "example" database is a bad idea. You need to describe to us the actual products you are dealing.

Also a questionnaire database requires a very specific structure. If part of your application involves dealing with recording responses to a survey, then you need a structure for that.

4. You don't explain what your criteria are for filtering for selected Salesman/Customers. so how can we help you do it.

So to summarize, you need to start, not with some generic example, but with the real word situation you are trying to model.  Only then can we help you properly.

"However, I am only a beginner trying to do things in different ways. "

By different ways, do you then you were doing or what? The reason I ask is because Access is a Relational Database. The Relational Database model has existed, largely unchanged for over 40 years. There are rules about designing a relational database and you ignore those rules at your peril. Access is not a program where you can just jump in and start using it like Word or Excel. You need to have. at least, an understanding of relational design.

You didn't answer my question about whether an order can include multiple items. It would be a rare instance if it doesn't. So that means you need an Order Details and a Products table. As for entering orders. CustomerID and SalesmanID should be foreign keys in the Order record. They should be selected using list controls on an Order form.

(1) By different ways, do you then you were doing or what? The reason I ask is because Access is a Relational Database. The Relational Database model has existed, largely unchanged for over 40 years. There are rules about designing a relational database and you ignore those rules at your peril. Access is not a program where you can just jump in and start using it like Word or Excel. You need to have. at least, an understanding of relational design. 

(2) You didn't answer my question about whether an order can include multiple items. It would be a rare instance if it doesn't. So that means you need an Order Details and a Products table. As for entering orders. CustomerID and SalesmanID should be foreign keys in the Order record. They should be selected using list controls on an Order form.

Hi,

(1) By 'different ways' I meant methods of doing things faster. By temporarily 'filtering our' irrelevant records from the Lookup field, I can make data entry faster and easier. Later on, I will remove these temporary filters to see and work with all the records. (Para 3 will explain it further.)

(2) In this specific case, each Order will have only one item (or a fixed number; if there are multiple items in an Order, there will be multiple Orders for that -- e.g. if a Salesman receives order for five items from one Customer, this will be managed by entering 5 Orders for same Customer/same Salesman, one each for each order item. (Next para will explain it further.)

(3) I have since uploaded a link to the "example database" that I created for this post. I call this "example database" because this is not the actual database I am actually working with. In this "example database" I used generic field names, tables names, etc to demonstrate the issue I was facing. I wanted to know if what was seeing (Some Names, some Record_ID in datasheet in unfiltered view) is normal/expected or otherwise. The "actual database" that I am working with involve processing/analyzing questionnaires/responses (of a market survey) related to different products/gadgets where each of the surveyor (read Salesman) would approach any individual/respondent and fill out a standard questionnaire/response sheet (read Order) to record their responses regarding one/more (but never too many) of the 50-70 products/gadgets (read Customer) that are subject matter of the market survey. (In future phases, the surveyor (=Salesman) may remain same but the products/gadgets (=Customer) may change (hence I need to filter Customers of the present phase).)

(4) I get these response sheets for data entry in batches: i.e. one surveyor (=Salesman) may contact, say, 10 individuals/respondents in a week and record their views regarding products/gadgets in a standard format/questionnaire; a separate questionnaire would be recorded for each of the product/gadget that the individual/respondent is familiar with and agrees to give his/her opinion. One individual/respondent is normally supposed to answer about only 5-7 (of the total 50-70) products/gadgets that are to be covered in the present phase of market survey.

(5) From above, it may be understood that my work becomes easier if I am able to 'filter in' only selected Salesman/Customers of the present phase. As the end of a phase, data of the phase would be analysed (Min, Max, Average, etc) in Access itself or exported to Excel. For the future phases, the same database is to be used by modifying filters for Salesman/Customers.

I hope this explains the situation. Sorry for the long post. And thanks for your help. I think, relationship between the tables and the ability to look up data in other tables is the beauty of Access.

-- Naresh Kumar Saini

Thanks, Scott.

(I have since edited my original post to provide a link to the database (in first paragraph), so the issue can be understood better.)

From "Evils of Lookup fields" I see that it is better not to use Lookup fields in tables.

However, I am only a beginner trying to do things in different ways. As my database grow more complex and I create Queries and Forms, I will shift the lookup part from Table to Forms/Queries.

As of now, my table contain only three Tables and I work in Datasheet view most of the time. In fact, the issue I described is more of academic interest/curiosity than an actual problem.

Consider that:

• The next couple of hundreds of Order records I will be adding are from 20-25 Customers (out of total 350+ in the Customer Table), obtained by any 2-3 of Salesman.
• I open Salesman Table in Datasheet view; expand (+) the view and add all the records obtained the Salesman.
• I start typing the first few character of Customer's and select when exact name is displayed. Then I add data to other fields. It is fastest to work like this.

I am already trying to design a Linked Form (Mother Form-Child Form) to implement the above. I think that will be more professional solution.

Thanks for your help.

-- Naresh Kumar Saini