Lookup list

[BCote689]

I have field BALL in tblPLAY for which I want to input data based on a lookup list.


The structure of tblPLAY looks like this

PLAYID...GAMEID...BALL...


The field GAMEID is the primary key in a second table called tblGAMES which looks like this

GAMEID...TEAM1...TEAM2


For each record, I want the lookup list for BALL to include only TEAM1 and TEAM2 based on the value of field GAMEID in the same record of tblPLAY

The following works if I set in the SQL statement that GAMEID=3


SELECT tblGames.TEAM1
FROM tblGames
WHERE (((tblGames.GameID)=3))
UNION
SELECT tblGames.TEAM2
FROM tblGames
WHERE (((tblGames.GameID)=3));

now I would like this query for the lookup field to work for all games (not just game #3) ...

I try to use the statement ... WHERE (((tblGames.GameID)=(tblPlay.GameID))) and that does not work

Any advice?

 

[Uncle Gizmo]

>>>Any advice?<<<

Yes, your table structure:
GAMEID ...TEAM1...TEAM2

is the root of your problems.

I suggest you read the following posts and then get back here if you have more questions.
http://www.access-programmers.co.uk/forums/showthread.php?t=143336

 

[BCote689]

Thanks UncleGizmo for the quick reply

... I reviewed the thread you suggested and I understand that I should consider changing the structure of tblGames. I assume this is based on the fact that it is not good practice to have a table such as

CUSTOMERID...ORDER1...ORDER2...ORDER3..ORDER4..

as we are in trouble once a customer wants to make a 5th order.

I am well aware of this standard practice and have applied it in the bigger picture of the database. In the particular case you were referring to, I will never need more than the fields TEAM1 and TEAM2. I hope I understood your advice correctly.

Do you think it is possible to fix the query for the lookup fields?

Thanks

 

[Uncle Gizmo]

>>>Do you think it is possible to fix the query for the lookup fields?<<<

Yes, I'm sure you could find a way to do this, access is a very powerful tool, however I don't know of anyone that's done it yet.

Alternatively if you modify your table structure as indicated in the posts I linked to, then it is a simple matter to extract the information you want.

 

[Uncle Gizmo]

Actually looking at your question properly, I think that GAMEID...TEAM1...TEAM2 could well be the correct structure for this table.

 

[BCote689]

Thanks UncleGizmo...

I now understand why you made the recommendation...indeed it makes sense

Considering the bigger picture, I think the overall structure is indeed most logical...the problem with the lookup list of one single field would not warrant changing the entire structure

The problem is transposing the query which yields (TEAM1,TEAM2) on one "row" in order to get (TEAM1,TEAM2) in a "column" which can be used as the lookup list. I have used a UNION query to that effect...

Maybe that helps clarifying

 

[Uncle Gizmo]

I'm still not 100% sure about your structure, however if you maintain the same structure, then you were on the right track with your original query, however you need to remove the "where clause" from both queries, and then create a new query based on the results of the first query, in this extra query, add your "where clause".

 

[BCote689]

I finally found one simple solution which was to perform the WHERE criteria on the form control rather than on the table itself.....solution looks as follows...


SELECT tblGames.TEAM1
FROM tblGames
WHERE (((tblGames.GameID)=[Forms]![FormPlay_Input][GameID]))
UNION
SELECT tblGames.TEAM2
FROM tblGames
WHERE (((tblGames.GameID)=[Forms]![FormPlay_Input][GameID]));

need to refresh the records (me.refresh) AfterUpdate of GameID

Sincere thanks to you UncleGizmo for your effort to help me out.