This is sort of in relation to my previous question which was answered: https://www.experts-exchange.com/Database/MS_Access/Q_28429630.html
Note that it's only taking the data from one table (not 9 as I said before).
I have succeeded in creating a MS ACCESS SQL query that will generate all possible teams (permutations) from 8 positions by name ( i.e. catcher, first baseman, second baseman, etc.). Not including the pitcher position. There's about 30 players at each position.
The query is pretty simple actually and it works:
SELECT T2.C, T3.[1B], T4.[2B], T5.[3B], T6.[SS], T7.[OF] AS OF1, T8.[OF] AS OF2, T9.[OF] AS OF3,
FROM (SELECT Name AS C FROM MASTER WHERE POS='C') AS T2, (SELECT Name AS 1B FROM MASTER WHERE POS='1B') AS T3, (SELECT Name AS 2B FROM MASTER WHERE POS='2B') AS T4, (SELECT Name AS 3B FROM MASTER WHERE POS='3B') AS T5, (SELECT Name AS SS FROM MASTER WHERE POS='SS') AS T6, (SELECT Name AS [OF] FROM MASTER WHERE POS='LF') AS T7, (SELECT Name AS [OF] FROM MASTER WHERE POS='CF') AS T8, (SELECT Name AS [OF] FROM MASTER WHERE POS='RF') AS T9
The problem I have is if there's more than 6 or 7 players at each position it takes forever to complete the query (I had to halt it), because there's too many permutations. And I actually don't need to list all players, I just need to select from it.
Is there any way I can optimize this to use more players? Would MS ACCESS be too slow?