Building a table sequential or creating a query

[madpiet]

While I understand you're more comfortable with VB/VBA than SQL, I suggest you don't go there unless you have to. The only time I use VBA (and I used to use it a LOT) was when there was something I couldn't do with regular SQL (well, which can be a bunch given the limits of Access's oddball version of SQL). Most of the time, a SQL statement will be orders of magnitude faster than VBA and recordset messes. That's not to say you never have to use them - sometimes you do, but I'd make sure you really need them before you use them.

[jojowhite]

i also have my entry using VBA and retrieving Random Players.
open form1.

Note the zipped file is an .accdb

[MikeCt]

The whole reason for taking on this project was to involve myself in learning SQL to do a lot of the leg work. I've seen how fast it is, and I know if I can get more comfortable with using it, I can make future projects better. It has a big learning curve, with a different mindset.

Not sure what to do with this post, I do not want to close it with a solved status because I'm still trying to understand it all, and to tell you the truth, I truly believe the answers I'm looking for are contained in your answers. I just haven't had enough time to really dig into it all and see how things were accomplished, which is really the whole reason for this. It's been a great project for what I'm trying to accomplish.

Thanks again for all the help and explanations, they really do help

[CJ_London]

no need to close the thread until you are ready.

The VBA I provided is annotated with what each section of code is intended to do.

SQL (at least in Access) cannot be annotated unless you use a different sql editor - but I can explain what each part does if required - just not right now as I am focussed on paying clients.

Others have mentioned that in their view anyone can be captain and do not need to designate players as captains (or gold/red for that matter). If that is the case then the vba and sql would be much simpler - you are just picking at random a 'player1' and then randomly other players who have not played with that player1 and subsequent members of that team. My version prioritises players designated as captains - then gold and finally red.