Fantasy Football League Draft DB

[ElSteveO]

I have a DB with 3 tables:
Fantasy Teams
Players
Transactions

This database will keep track of which fantasy team drafted which (real football) players.

The Transactions table has a lookup field that displays all the players. I want the players to be eliminated from the lookup once they are chosen, but I can't figure out how.

Thanks for any help!
Steve

 

[lespaul]

The Transactions table has a lookup field that displays all the players.

perhaps you need to read The Access Web - The Evils of Lookup Fields in Tables

you really need to design a form to do this..then you could eliminate the players once they have been chosen....

Leslie

Have you met Hardy Heron?

 

[ElSteveO]

Thanks Leslie. I will do some reading. Will your suggested reading give me an idea on how to eliminate players in the form?
Thanks

 

[ElSteveO]

Ok Leslie, I see the error of my ways. I must use a query as the source for a combo box in a form. Any clue as to how I eliminate the chosen players?
Thanks

 

[lespaul]

no, it will just describe why you shouldn't use the lookup field.

In order to find all the players still available, you'll need a query. I'm guessing you have some field that indicates if a player is available?

SELECT * FROM Players WHERE Avialable = true

and then you can use this query as the source for the form control that you want users to use to pick players.
HTH


Leslie

Have you met Hardy Heron?

 

[ElSteveO]

Leslie, I really appreciate your input. In which table would I put a field that would indicate whether a player is available? If I put it in the Players table, then how would I mark them available/unavailable through the Transactions table/form?

Thanks,
Steve

 

[dhookom]

Your available players would be

SELECT Players.*
FROM Players
WHERE PlayerID Not IN (SELECT PlayerID FROM Transactions)

You will need to requery this combo box whenever a new player is added to Transactions.

Duane
Hook'D on Access
MS Access MVP

 

[ElSteveO]

Great advice. Thanks dhookom! I appreciate your input and that of les.
Steve

 

[vepz]

I am trying to set up a similar db for our league. As a player is selected, you will need to record which team selected him. Use a query that locates only players without an assigned team and use this in your form list box.

Greg

 

[ElSteveO]

Greg,
Any way to send me a copy of your database so I can see how it's structured? If not, don't worry.
Thanks