MS Access programmatic select statement -- pulling in value from second column

Hi all -- I'm trying to resolve a limitation in Access regarding how it handles programmatic select queries -- e.g., where the select query and related code to pull it are embedded in a procedure.  My question is specific to how the select query pulls information from lookup tables in this context -- in the attached example, I have a Table1 and Table2. Table2 provides a lookup value to the records in Table1 (in this example, astrological signs). In a normal (e.g., non-programmatic) select query, the results would display as text (Pisces, Taurus, and so forth). However, when executed programmatically, the index values themselves display (1,2, 3). My workaround for this is to create a second query which uses the results of the first to grab the associated text value from the supplied index value after the first query returns results. Clumsy, but it works. However, I'd like to know if anyone has a more elegant way to restate the original select query to just pull the text value out without having to resort to a second pass.  Access doesn't accept the usual trick of using syntax like Column(2) that can be used with combo boxes.

For the record, I'm not interested in discussing why I want to do this, just suffice it to say that I do and therefore, I'd like to explore resolving my question. Thanks in advance for your help.....!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Looks to me like you want to get the value of Table2.ValueD, based on the value in Table1.TblID? If so, just Join the two in the query:

SELECT Table2.ValueD
FROM Table2 INNER JOIN Table1 ON Table2.TB2ID = Table1.LookupTable2

If not, please restate your requirement

I also note that you have a Table-level lookup defined on Table1, which is a bad design decision. Lookups should be defined at the Form level, not the table level.
alfamikefoxtrotAuthor Commented:
Thanks for the quick turnaround, Scott...! That said -- hmmmm. This starts to get to the sense of where I want to go with the query but I still need to pull the other values from Table1 in the process. Is there a way to flip it around so that the query itself is still primarily tied to Table1 but perhaps with an outer join to get the appropriate text value from Table2? Otherwise, I'd still wind up with two queries, just with a different structure than what I have now.

Also -- this is just a prototype so I'm a little less worried about the lookup in Table1 being bad -- but I'm definitely curious about your perspective there. What's your sense of why it's a bad design decision?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can add any of the fields from Table1 or Table2. If you want to get all records from Table1, and any related records from Table2:

SELECT Table2.ValueD, Table1.*
FROM Table1 LEFT JOIN Table2 ON Table2.TB2ID = Table1.LookupTable2

If you want all values from Table2 and only related values from Table1

SELECT Table2.ValueD, Table1.*
FROM Table2 LEFT JOIN Table1 ON Table2.TB2ID = Table1.LookupTable2

but I'm definitely curious about your perspective there. What's your sense of why it's a bad design decision?
Lookups defined at the Table Design level are bad for a variety of reasons, most of them explained here:

Note that I'm referring to Lookups defined in this manner:

Lookup at the Table levelYou should never have any value other than "Textbox" in the Display Control section. Using a Table as a lookup is fine (and is generally good relational design). Defining those lookups at the table design level is not.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

alfamikefoxtrotAuthor Commented:
Thanks Scott -- I think that syntax is probably close enough for me to mark as complete and close the question. As to the discussion on lookup fields in-table, I get what you're saying in concept but I'm not sure I'm buying it all the way. That seems to also go against what MS itself recommends for table design, per the following:

When I've created search functions, those are typically lookup fields also, so you're comparing attribute-to-attribute. I can see the point, though, if you were inclined to do a free-text search on what would otherwise be the contents of the field. It's an interesting discussion -- so I'm getting you, you'd basically propose that the contents of a records table would basically be integers and strings and when a user selects something in a combo-box, they're writing the text value (not the index value) into the records table, correct?
alfamikefoxtrotAuthor Commented:
Thanks to Scott for the code and discussion -- the discussion on table design is interesting and I'd like to continue it, even though I'm not totally aboard with it.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Just because Microsoft added them doesn't mean they're a good idea :). If you were to poll 100 professional Access developers, I doubt you'd find more than one or two using table-level lookup fields. If you polled the Access MVPs, you wouldn't find any that use them.
so I'm getting you, you'd basically propose that the contents of a records table would basically be integers and strings and when a user selects something in a combo-box, they're writing the text value (not the index value) into the records table, correct?
No. The Parent table of a looked-up value would hold the ID field of that value. In your Table1, the "LookupTable3" field would hold the value from Table2.TB2ID, not the value from VauleD. That's proper normalization, and you've got that part right. What you should consider NOT doing is adding a lookup at the table level, for the reasons spelled out in the link above.

I'm not sure how search functionality is relevant to table-level lookups. You can perform a free text search on a table that has no table level lookups by creating the correct query. You cannot, of course, do this at the table level, but then users should never be working directly with tables, so that's kind of a moot point.
alfamikefoxtrotAuthor Commented:
Touché -- you have a point regarding Microsoft's functionality -- I take what Windows 10 has done with its Groove music installation on my laptop as proof of that. OK, so I think I was confusing your earlier post for basically going away from a full relational design and I totally agree on the item regarding search. Ironically, after the last post, I went to the ten commandments and am aligned with all of them (and have been for a long time without know it, I suppose), but the item on the table-level lookup still had me confused. So this leads to a bunch of other questions for me about how you get there --

1. So you have your relational design and you have a text field at the table level, and presumably still a combo box at the form level. What would the relationships window look like?

2. In that scenario, how do you normally create the design -- I'm guessing you don't go through the wizard. If you DID go through the wizard, is it as simple as going back and then changing the combo to a text field at the table level?

3. The datatype in the design screen will still be Long, correct?

4. How does an upsizing wizard (ex, to MSSSQL) handle conversions that use the table-level lookup? Would it tank, or would it convert it to something like your design?

Thanks for humoring me as I work through this -- it's valuable interaction and I appreciate it....!
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
1. I don't know that you'd have a Text field at the table level, at least in the "Parent" record. Referring to your earlier database, Table1 seems to be properly designed in regard to Datatypes, with the LookupTable2 field storing a Number. That number is the TB2ID value. The relationship window would look exactly like your Relationship in your database.

2. I haven't used the wizards in many years. I'd create the form, then add a combo that is bound to the field in the Parent table (your LookupTable2 field). The Rowsource of the combo would be something like "SELECT TB2ID, ValudD FROM Table2", and I'd set the ColumnWidths and other properties to show the ValueD. The user would select the alpha value (Libra, Pices,etc), and Access would store the ID value.

You can certainly go back and change the Combo to Textbox at the table design level. It won't have any impact on your existing forms/reports, although it could possibly impact queries (both stored and dynamic).

3. The datatype for the Parent table's field would be numeric.

4. The upsizing wizards CANNOT handle table level lookups, which is one of points of the Evils of Lookup tables. Table-level lookups are an Access-specific feature, and cannot be moved over to other database engines.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.