Combos and text boxes

  • Thread starter Thread starter Stewart Jefferys
  • Start date Start date
S

Stewart Jefferys

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
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
Click to expand...

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]
 
John W. Vinson said:
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]
Click to expand...

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.
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;
Click to expand...

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]
 
John W. Vinson said:
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;
Click to expand...

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]
Click to expand...


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
Click to expand...

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]
 
John W. Vinson said:
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]
Click to expand...

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.
Click to expand...

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]
 
John W. Vinson said:
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.
Click to expand...

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]
Click to expand...


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
Click to expand...

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 W. Vinson said:
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]
Click to expand...

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?
Click to expand...

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]
 
John W. Vinson said:
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]
Click to expand...


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
 
Back
Top