Drop down box when creating a table (1 Viewer)

Hello,
I have a table of customers with simple short text fields throughout. I have then created another table where I refer to the customer table in a combo box. I used the lookup option when creating the table which then led me off to choose the customer table and sort the names etc. So in my new table/form I have a nice drop down box where I can choose my customer from a list. This is all ok, however I am running into a problem on other forms where I'm trying to search for a customer and the result is a number.
I've read a lot about making the field types match etc but how do i manage that if the customer name in the table is text and then the lookup reverts to a number. I feel like i am going round in circles with this one and any help would be appreciated. Hope it makes sense too. Thanks.

Hi. If I understand you correctly, you’re supposed to store the number in your table and not the text, so I am guessing you did the lookup correctly. However, you weren’t supposed to use the lookup at all to avoid confusions like this.

It looks like I've figured out what was wrong. I didn't have the field I needed in the Row Source. So i edited to include it and then picked the correct Bound Column.
I say figured out, I Googled what a bound column really means!
Hi, the DBguy, I used the LookUp wizard when i created the table. It was an option in the Data Type.....is that not correct?

In general, lookups in tables were added because some folks thought it would be neat, but it adds a layer of complexity that many of us believe is not needed. Granted, that is an opinion, but I am not alone in suggesting that a lookup data type in a table is the wrong answer, even without knowing the question.

Another way exists to manage that function but it requires you to use a form to enter data to that table. Multi-valued fields in tables have nasty side-effects related to the queries you need later to extract data for some other action within the DB. Sometimes, if you have a JOIN query that involves the lookup field as a non-filtered selected field (not directly involved in the JOIN and not part of a WHERE clause), you end up getting "phantom" records that are caused by Access enumerating all of the possibilities of that lookup. Our general advice is to not use the Lookup field because this great idea was found to have not-so-great side-effects.

Craig6 said:

Hi, the DBguy, I used the LookUp wizard when i created the table. It was an option in the Data Type.....is that not correct?

Click to expand...

Hi. Not sure about "correctness," but it's not recommended. Here's some reasons why.

Ok, well that would explain why I've been having a nightmare time later down the line creating searches etc Thank you for the 'Here's some reasons why', I don't suppose you have a link to 'How to change lookup fields in a table back to short text fields'
I suppose i can do this but first I would need to delete the existing relationships. Then would I just recreate the relationship?
Thanks both.

Craig6 said:

Ok, well that would explain why I've been having a nightmare time later down the line creating searches etc Thank you for the 'Here's some reasons why', I don't suppose you have a link to 'How to change lookup fields in a table back to short text fields'
I suppose i can do this but first I would need to delete the existing relationships. Then would I just recreate the relationship?
Thanks both.

Click to expand...

Hi. Unfortunately, the underlying message we were trying to convey is "you should store the numbers in your table - not the text," so no, I wouldn't have any links to show you how to change your numbers to text because it's not something someone would really tell you to do, let alone show you how to do it. To make sure we completely understand your original problem, are you able to post a sample test db showing the problem you're having?

Here is your problem in a nutshell. From your language, I infer that you are overusing references to tables and neglecting the opportunity to use queries.

When you have translations to make (where you have a code number stored but you wanted to see the name corresponding to that code), the simplest way is to define a translation table separate from everything else. That table contains the code and the text as two columns, and the code column is the prime key (PK) of that table. Then use the relationships window to establish that the code column in your main table (a foreign key or FK) has a many/one relationship to the code column (PK) in the translation table.

Now if you build a query, you can include the main table and the translation table using the query-builder grid and can pull all of the other data from the main table but use the text field from the translation table. The query builder will automagically build a LEFT JOIN for you so that your query can pick up that translation based on the FK in the main table and PK in the translation table.

To STORE a new value and accomplish the lookup, you build a form with a combo box in it and have the PK as the bound column but you can display the text column to let you select the text you wanted. You can (in the combo box) display the PK with a width of zero (which makes it invisible) and display the text as the full width of the combo. Again, because there is a relationship there, the form builder knows a lot of what to do.

Relying solely on tables isn't always right. Sometimes you want to rely on queries to accomplish implied lookups, formatting, and computation. And as it happens, almost anything that can use a table can also use a query for its record source. There are a few things you can't do with a really complex query because of confusing Access about where to put things, but what I described is garden-variety usage of queries.

Ok. I think i understand. Here is a copy of my relationships;

https://www.dropbox.com/s/dh7dkpc2w7zabha/Annotation%202019-06-25%20120040.JPG?dl=0

So I should just have all of my fields in all of my tables as text etc and avoid the lookup option. Then when I need to refer to a list of say, customers i should run a query?

I can't post my db as I've not yet posted enough posts

Craig6 said:

Ok. I think i understand. Here is a copy of my relationships;

https://www.dropbox.com/s/dh7dkpc2w7zabha/Annotation 2019-06-25 120040.JPG?dl=0

So I should just have all of my fields in all of my tables as text etc and avoid the lookup option. Then when I need to refer to a list of say, customers i should run a query?

I can't post my db as I've not yet posted enough posts

Click to expand...

Hmm, I guess I must have not explained it properly. You "should" store Numbers. Don't store Text as foreign keys in your tables.

I’m really sorry, please bear with me.
I’m going to go away and read through this thread again, thoroughly. As it stands my 2nd table does have the number stored in it which I believe is the PK of the customer table. Were you able to view the relationship pic? It’s the part where you suggest not using the lookup when creating a table that’s confusing me. From what I can see this is then linked using queries.

Thanks for your lengthy replies I really appreciate it - it will click soon, promise.

Craig6 said:

I can't post my db as I've not yet posted enough posts

Click to expand...

I thought you only needed 10 posts.?
Even under 10, you can upload a zip file.?

I understand now the relevance of storing the number value in the table and carrying out the lookups and references in queries etc Thanks for your patience.
Thanks to all here and Mike.

Another tip for you. In all but two of your tables, the primary key is called ID. It's much more understandable and easier to work with over the life of the database, if you avoid the generic name ID and put the entity description in the name of the primary key eg ProductID, CustomerID


With the foreign key names, include the ID to indicate that its a numeric value, not text eg in tblSupport change SupportType to SupportTypeID.



And also remove the multi-value fields in tblRemedials. I've not yet seen any experienced respondent on this site who supports the use of multi-valued fields.


The field Customer in tblSupport would seem to be redundant.

Hi Cronk,
Since that picture of my relationships I've removed the redundant Customer field and added ID to the SupportType in the tblSupport.

I'm in the process of adding the name to the ID for the PK in the other tables. However, I haven't got on to omitting the multivalue field in tblRemedials yet. I do need to find a different way for this particular table as in the form I need the user to be able to pick from more than one product and they be registered.

Thanks for the tips. I feel like I'm getting somewhere now.

Instead of multi-value field, you need a dependent related table. Which is essentially what Access does with a multi-value field - a hidden dependent table.