Double Lookup Problem (1 Viewer)

[depawl52]

Greetings and Happy New Year all.
I'm having a problem with using a Lookup to bring the Customer ID and Customer's name from a Customer's Table into a Products table.
So in the Products table, I have a lookup field:
SELECT tblCustomer.CustomerID, [Lastname] & ", " & [Firstname] AS CustomerName FROM tblCustomer
With the bound column = 1, a column count of 2, and column widths of 0" and 1".
This works fine for Showing the Customer name, but I also need to show the CustomerID in this table.
I can reverse the column widths to show only the CustomerID but for some reason it will not show both.
And if I add another field with basically the same SELECT statement, it always shows up blank, no matter
what I try.
I've researched and finagled (technical term) this since last year (literally) but no luck or clue yet as to how to proceed.
As always thanks in advance for any suggestions.

 

[MajP]

Get rid of the the table level lookups. They are nothing but trouble.

The Access Web - The Evils of Lookup Fields in Tables

You should be doing this in a query, not a table. Make a query that joins the product table to the customer table by CustomerID. Then in this query you can show Customer ID and customer name from table customers.

 

[ebs17]

Lookup fields mask what is really happening, and hide good relational methodology from the user.

Despite this, almost every newcomer wants to kiss this frog.

 

[Pat Hartman]

@depawl52 You've fallen into the trap of one of what experts refer to as the "abomination" data types that were introduced in 2007. They look good on paper (like most progressive bills) but in reality, they are nothing but problems. You THINK you have the Customer name in the Products table but in reality you have the CustomerID. It is the abomination data type that is masking what is actually going on.

As @MajP recommended, remove the lookup from the table. Then when you open the Product table, you will see that you in fact have the CustomerID and not the CustomerName stored there You would use a combo or sometimes a listbox on a form to select items. That allows you to choose by CustomerName but actually store CustomerID which is the objective. You almost never want to duplicate data from the lookup table. All you store is the ID and this is referred to as a Foreign Key (FK). Then whenever you need to see data from the lookup table, you create a query that joins the two tables.

One more thing. You didn't describe your application (not that it matters) but to me, with 50+ years of experience, it would be very odd to see a CustomerID in a Product table UNLESS, the "customer" is actually a "vendor" in this particular situation.

Despite this, almost every newcomer wants to kiss this frog.

I was at the presentation in Redmond when MS first revealed A2007 to a group of about 20 people at a 3-day meeting in the spring of 2006. The participants reacted with horror at what MS was proposing as new data types to make Access more compatible with SharePoint. By that time, they were committed to their path and so there was no talking them out of it. They were adding the "abomination" data types to A2007 and that was that. And we have been fixing problems like this ever since. NO ONE actually understands them when they use them but they sure look like a "good" idea. We left the conference with a beta version of Office 2007 and started testing it and providing bug reports and suggestions. One of my suggestions actually got implemented. Well, I think of it as mine but I don't know if others also suggested it. I suggested that each record navigation control be given a name. That way when a form has a navigation control for both a subform and a mainform or for multiple subforms, it is easy to tell which recordset you are scrolling. Its name is the "Navigation Caption" and you will see it on the Format tab of the form properties dialog.

The basic problem with the abomination data types (all of them) is that they obfuscate the actual contents of a table. (your specific confusion). Most of them use deeply hidden tables to implement these data types so they are technically normalized. But WE can never see the hidden tables so the "feature" cannot be interfered with and therefore none of the really cool controls that went along with the abomination data types can be used when we use our own normalized versions of the abomination data types.

 

[cheekybuddha]

it would be very odd to see a CustomerID in a Product table

Unless your company name is Google or Meta or MS or ....

 

[GPGeorge]

One analogy I've heard is that Lookup Fields in tables are like a spoonful of coffee in a cup full of sugar. At first it can seem really sweet, but you regret the experience quickly and for a long time.

 

[depawl52]

Thanks for all the responses. Just as an fyi, this is a database that I (more or less) inherited and was trying to do repairs and make some improvements.
I've never used a lookup for all the reasons stated above, I was just hoping that in this instance there might be a quick fix, without having to rebuild the whole thing from scratch.
Thanks again, much appreciated.

 

[Pat Hartman]

Unless your company name is Google or Meta or MS or ....

No, the Product table defines the product and has nothing to do with customers. When you add a customerID to a Product, that limits the use of that product to a SINGLE customer. In the real world, there would be a junction table to connect customers to products so that one product can be used by many customers and one customer can use many products.

When you have a VendorID in the Product table, that means that only ONE vendor supplies a product. That at least makes some sense. That relationship should still be made with a junction table for flexibility but I'm pretty sure lots of Order entry systems start this way.

I was just hoping that in this instance there might be a quick fix, without having to rebuild the whole thing from scratch.

This isn't a big deal. You won't need to change any forms and you probably won't need to change any queries except perhaps those bound to reports. Usually we don't use combo boxes in reports so, for the report, we would want to see the name rather than the ID and the way to do that would be to add the lookup table to the query so you can select the name from that table. You might need to fix the ControlSource to pick up the new field from the query. And if you don't mind the look of a combo on a report, then change the control to a combo so you don't have to change the RecordSource query for the report.

For a small app, you're probably looking at less than an hour and depending on how the queries are built, could be less Don't depress yourself. The sooner you fix basic bad practices like this the fewer objects need to be changed. The only reason I suggested an hour is because you actually need to open all forms and reports to ensure you haven't broken anything.

 

[Isaac]

Just one more vote. don't use the lookup at the table level.

Adhere to these as diligently as you would a religious rite:

1) Tables are for storing information only - and the more Simple that information is, the easier all further steps will be.
Generally, don't get creative in datatypes. Stick with the basics.
Nobody looks at Tables but a developer
2) Queries are for retrieving, manipulating. calculating, and displaying information
Nobody looks at queries but a developer
3) Forms are for display, action, flow, inserts, edits, deletes
This is what your users see, and the only thing they see, unless you use Access Reports, which I have rarely found to be worth the trouble when most users either want 1) data to excel, or 2) data on a Screen [i.e., a Form], so they can DO something with it (edit, tag, mark, disposition in some way). Nothing wrong with Reports. Just one man's experience. Reports are great if you have users who want to "just look" at something and then go home....Or, if you work at a business that still uses paper and prints.

 

[cheekybuddha]

No, the Product table defines the product and has nothing to do with customers. When you add a customerID to a Product, that limits the use of that product to a SINGLE customer.

Sorry, I forgot to add a more obvious joke indicator!

I was referring to this situation where effectively ProductID = CustomerID

 

[arnelgp]

I can reverse the column widths to show only the CustomerID but for some reason it will not show both.

you can only Show 1 column to the Textbox portion of a Combobox although you can see All Columns (if not set to 0) when
you Dropdown the combo.

to show more columns, you can Concatenate all columns you want to display into one column.

 

[Pat Hartman]

I was referring to this situation where effectively ProductID = CustomerID

As with gmail? I get it