Soccer Team

[PR2AledE]

I'm writing a a database for a soccer team and I'm having a problem with a query.

The structure of my database is that I have a team table where I select 11 players for a particular fixture. I have written a query which shows the players that have been selected for a fixture. Its a select query with the fields Forename and Surname from the player table and Player ID from the Team table. The criteria which I have entered in the Player ID field is:-

<>[tblteam]![p1] And <>[tblteam]![p2] And <>[tblteam]![p3] And <>[tblteam]![p4] And <>[tblteam]![p5] And <>[tblteam]![p6] And <>[tblteam]![p7] And <>[tblteam]![p8] And <>[tblteam]![p9] And <>[tblteam]![p10] And <>[tblteam]![p11]

This shows the players which havn't been chosen therefore the available players. The problem is that I want to base my combo boxes on this query but the Access won't let me. Plus if I want to select a team for a new fixture how do I make everyone available.

 

[FoFa]

How about this:
Use NOT IN and select query to pull those players selected already.

SELECT mycolumns from MyTable
WHERE Playerid not in (Select playerid from MyTable where player is already selected)

 

[Mile-O]

You're going to have to normalise your tables as it is not a good design to have a structure with Player1, Player2, ...Player 11

Imagine if a company did this with employees. You'd have to delete fields and add them as attrition dictated.

I know the number of players in a football team won't change but it's best not to follow the structure you have.

Also, on the other post I directed you to (in another post) I 've added an example that demonstrates a tidier and less complicated way around the combobox issue.

That link again!