alfamikefoxtrot
asked on
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.....!
2ndColumnPull.accdb
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.....!
2ndColumnPull.accdb
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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:
https://support.microsoft.com/en-us/help/304462/how-to-add-lookup-fields-in-a-microsoft-access-table
https://support.office.com/en-us/article/Add-a-lookup-field-to-a-table-361968B8-2A7D-455B-8175-92B380F7320B
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?
https://support.microsoft.com/en-us/help/304462/how-to-add-lookup-fields-in-a-microsoft-access-table
https://support.office.com/en-us/article/Add-a-lookup-field-to-a-table-361968B8-2A7D-455B-8175-92B380F7320B
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?
ASKER
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.
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.
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.
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.
ASKER
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....!
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....!
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.
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.
SELECT Table2.ValueD
FROM Table2 INNER JOIN Table1 ON Table2.TB2ID = Table1.LookupTable2
WHERE Tabl1ID=4
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.