 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / February 2008Combos and text boxes
The combobox in table2 is reading from table1 which contains two fields, an autonumber ID field and a textbox field. Table2 also includes other fields which I want included in the query. When I perform a union query on a combobox field it is always returning the ID (autonumber first field) instead of the textbox field. This also happens when I perform a crosstab query. Is there some setting that I need to change somewhere, or perhaps change the property of one of the fields? Stewart >The combobox in table2 is reading from table1 which contains two fields, an >autonumber ID field and a textbox field. Table2 also includes other fields [quoted text clipped - 5 lines] >property of one of the fields? >Stewart The queries are returning the actual contents of your table. The value in the table IS a number. That simple fact is concealed from your view by Microsoft's misdesigned, misleading, obnoxious and infuriationg Lookup Field type. The table APPEARS to contain text - but it doesn't. See http://www.mvps.org/access/lookupfields.htm for a critique of this misfeature. (If you get the idea that I don't care for lookup fields you're right). To include the text value in your UNION query, include both table1 and table2 in each SELECT statement of the UNION query, joining on the ID field and selecting the text field from the lookup table: SELECT table1.textfield, table2.this, table2.that, table2.theother FROM table1 INNER JOIN table2 ON table1.ID = table2.ID WHERE <some criteria> UNION ALL SELECT table1.textfield, table2.this, table2.that, table2.theother FROM table1 INNER JOIN table2 ON table1.ID = table2.ID WHERE <other criteria> John W. Vinson [MVP] > >The combobox in table2 is reading from table1 which contains two fields, an > >autonumber ID field and a textbox field. Table2 also includes other fields [quoted text clipped - 32 lines] > > John W. Vinson [MVP] Thanks for your response John. I think I have a better understanding now, but unfortunately it is still not working for me. The code I have is below. There are no WHERE clauses as I want to show all the records. SELECT [PlayerStats.1stInnBatRuns] as Runs, [PlayerStats.1stInnBatHowOut] as HowOut, [PlayerList.Player] as PlayerName, PlayerStats.BentleyGameNo FROM PlayerList INNER JOIN PlayerStats ON PlayerList.ID = PlayerStats.ID UNION ALL SELECT [PlayerStats.2ndInnBatRuns], [PlayerStats.2ndInnBatHowOut], PlayerList.Player, PlayerStats.BentleyGameNo FROM PlayerList INNER JOIN PlayerStats ON PlayerList.ID = PlayerStats.ID; The Lookup properties for PlayerStats.Player are Combo Box Table/Query SELECT DISTINCTROW PlayerList.ID, PlayerList.PLAYER FROM PlayerList ORDER BY PlayerList.PLAYER; 1 2 No What am I getting when I run the union query? It is asking for the PlayerStats.ID. Stewart >Thanks for your response John. I think I have a better understanding now, >but unfortunately it is still not working for me. The code I have is below. [quoted text clipped - 8 lines] >FROM PlayerList INNER JOIN PlayerStats >ON PlayerList.ID = PlayerStats.ID; In what way is it "not working"? I can see that there are some missing brackets: should be SELECT [PlayerStats].[1stInnBatRuns] as Runs, [PlayerStats].[1stInnBatHowOut] as HowOut, [PlayerList].[Player] as PlayerName, PlayerStats.BentleyGameNo FROM PlayerList INNER JOIN PlayerStats ON PlayerList.ID = PlayerStats.ID UNION ALL SELECT [PlayerStats].[2ndInnBatRuns], [PlayerStats].[2ndInnBatHowOut], PlayerList.Player, PlayerStats.BentleyGameNo FROM PlayerList INNER JOIN PlayerStats ON PlayerList.ID = PlayerStats.ID; Does that help? John W. Vinson [MVP] > >Thanks for your response John. I think I have a better understanding now, > >but unfortunately it is still not working for me. The code I have is below. [quoted text clipped - 26 lines] > > John W. Vinson [MVP] Made no difference, when I run the query it is still asking for PlayerStats.ID. I dont want it to ask this, I need it to give me all records for all Players in PlayerStats. Stewart >Made no difference, when I run the query it is still asking for >PlayerStats.ID. I dont want it to ask this, I need it to give me all records >for all Players in PlayerStats. >Stewart That suggests that there is no field named ID in the table PlayerStats. Is there? Might the field be named Player? John W. Vinson [MVP] > >Made no difference, when I run the query it is still asking for > >PlayerStats.ID. I dont want it to ask this, I need it to give me all records [quoted text clipped - 5 lines] > > John W. Vinson [MVP] That is correct, there is no ID field in PlayerStats. The first field is Player, which is the combobox field looking at the PlayerList table. The Lookup properties for PlayerStats.Player are Combo Box Table/Query SELECT DISTINCTROW PlayerList.ID, PlayerList.PLAYER FROM PlayerList ORDER BY PlayerList.PLAYER; 1 2 Shoould I create another field in PlayerStats, that somehow gets the ID from PlayerList when the Player is selected in the combobox? Thanks again John Stewart No >That is correct, there is no ID field in PlayerStats. The first field is >Player, which is the combobox field looking at the PlayerList table. In that case join PLAYER to ID: SELECT [PlayerStats].[1stInnBatRuns] as Runs, [PlayerStats].[1stInnBatHowOut] as HowOut, [PlayerList].[Player] as PlayerName, PlayerStats.BentleyGameNo FROM PlayerList INNER JOIN PlayerStats ON PlayerList.ID = PlayerStats.PLAYER UNION ALL SELECT [PlayerStats].[2ndInnBatRuns], [PlayerStats].[2ndInnBatHowOut], PlayerList.Player, PlayerStats.BentleyGameNo FROM PlayerList INNER JOIN PlayerStats ON PlayerList.ID = PlayerStats.PLAYER; John W. Vinson [MVP] > >That is correct, there is no ID field in PlayerStats. The first field is > >Player, which is the combobox field looking at the PlayerList table. [quoted text clipped - 12 lines] > > John W. Vinson [MVP] John, I tried this last week ( and again this morning), both returned a Type mismatch on expression error. Probably understandable given we are joining a number PlayerList.ID to text PlayerStats.Player. Any other suggestions much appreciated. Stewart >John, >I tried this last week ( and again this morning), both returned a Type >mismatch on expression error. Probably understandable given we are joining a >number PlayerList.ID to text PlayerStats.Player. >Any other suggestions much appreciated. >Stewart Please open your two tables in design view and tell me the name and datatype of each field. If a field is a Lookup field post its lookup properties. I don't know which field is which nor how the tables should be joined. John W. Vinson [MVP] > >John, > >I tried this last week ( and again this morning), both returned a Type [quoted text clipped - 9 lines] > > John W. Vinson [MVP] John, Fields as below. PlayerList ID Autonumber Player Text PlayerStats Player Text (Combobox see below) BentleyGameNo Number 1stInnBatRuns Number 1stInnHowOut Number 2ndInnBatRuns Number 2ndInnHowOut Number Lookup properties for PlayerStats.Player are Combo Box Table/Query SELECT DISTINCTROW PlayerList.ID, PlayerList.PLAYER FROM PlayerList ORDER BY PlayerList.PLAYER; 1 2 Is it worth making the PlaterStats.Player field again using the Lookup Wizard in a new field, and populating it somehow? Thanks Stewart >Is it worth making the PlaterStats.Player field again using the Lookup >Wizard in a new field, and populating it somehow? I'd simply TURN OFF the lookup. Change the Player field's Lookup tab from Combo Box to Textbox. This will display what is actually *in* your table. You can then join that field to the ID field in the players table. John W. Vinson [MVP] > >Is it worth making the PlaterStats.Player field again using the Lookup > >Wizard in a new field, and populating it somehow? [quoted text clipped - 4 lines] > > John W. Vinson [MVP] Thanks JOhn, this worked a treat. I have since been going through all my queries to cater for this change (but one for the better!) Stewart |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.