Double Lookup Problem

[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.

 

[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.

 

[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.