Building a table sequential or creating a query

[madpiet]

So what happens if you have a foursome and then for each you have roles (Captain, Gold Player, Red Player 1, Red Player 2)... If you have those 4 records, then you can just use that value to filter the players so for the Gold Player "type", you can only select Gold players etc.

Match--(M,M)--Role

and in the MM part, you have (MatchID, RoleID, PlayerID). ?

[MikeCt]

I understand using filters to put the team together (Captain, gold, red, red) if everything was perfect. But it doesn't take into consideration if the players played together in the past, which is the objective of my post.

[madpiet]

So find all the players that PlayerX has played with already. (with proper table design it's stupid easy), and then eliminate them from the list.

Say there's a Match table (has the date etc) and then there are the MatchPlayers table (MatchID, PlayerID, Score).
1. Find all the Matches that PlayerX played.
2. Find all PlayerID from MatchPlayers WHERE MatchID is in result from #1 and <> Player.

SELECT DISTINCT PlayerID
FROM MatchPlayers mp1
WHERE mp1.PlayerID IN
(SELECT MatchID
FROM MatchPlayers mp
WHERE mp.PlayerID = @PlayerID)
AND mp1.PlayerID <> @PlayerID;

CREATE TABLE Match (MatchID INT IDENTITY PRIMARY KEY, MatchDate DATE....);
CREATE TABLE MatchPlayer(MatchID INT, PlayerID INT... (MatchID, PlayerID) is Primary Key.

then it's just some simple set math.

[jojowhite]

I understand using filters to put the team together (Captain, gold, red, red) if everything was perfect. But it doesn't take into consideration if the players played together in the past, which is the objective of my post.

then show us your table with previous games so we can Lookup this table if a Pair of player has played on same team before.

[madpiet]

Why not just make up a handful of records? you don't need many... just use a structure like (MatchID, TeamID, PlayerID), which is what you'd end up with if you queried the whole Matches/Teams/Competes stuff... you only need it for maybe an EXCEPT clause or NOT IN clause.

[CJ_London]

Why not just make up a handful of records?

OP provided a db with a number or records in post #1

[CJ_London]

Please see attached. There are 3 tables, players, teams and eventplayers - the players is your table, the teams is a new table and the EventPlayers is a normaiised table recording events, players and teams - you can add scores etc to this table


the form looks like this




On the left, you select a date for a new or existing event - it defaults to 7 days after the last event.


Below that is a button to create the roster. At the moment this simply takes the active members in the players table when clicked and populates the eventplayers table with the playerPK and the date of the event - this is shown in the middle section together with some numbers showing the number of players in the roster and the required number of 4 and 3 player teams for that number.


You can delete the players in this list who are not on your roster for that event. A bit clunky and probably better if you are maintaining these lists in excel to just use the excel sheet to create the required records - up to you.


Finally there is a button to assign the roster to teams. This is on a role by role basis rather than team by team.


So first to be populated is the captain - if there are insufficient captains in the roster for the required number of teams, it then looks at the gold players. The order is randomises so you don'tget the same captain for each team


Next is gold - as with captains it finds gold players who have not played with the captain, if there are insufficient golds, it looks at captains and then reds -


Then we have red1 - as with gold it finds red players who have not played with captain or gold. If insufficient reds, it looks to golds and then to captaions


Finally red2 - if there are any 3 player teams, these will be left blank at the end. They are populated on the same basis as red1 (but also checks if played against red1)


The team listing is updated after each update and the roster at the end


As you will see in the image, red2 is not fully complete - this is because there were no red/gold/captains who had not played with at least one of the other members of the team. To complete the teams, you need edit the roster to add a team and role to your chosen players.


You will notice I have used a full roster requiring 37 teams, but only 20 teams are specified - you can add new teams as required to the teams folder.


There are a number of ways this can be handled but depends on your process - for example you could select a player on the roster form then double click on the teams form where you want the player to appear.


The app is not fully tested, so you may find bugs or inconsistencies (for example you could end up assigning more than one player to a specific team/role), or perhaps I've misunderstood the requirement - but it demonstrates one way of populating teams where the players have not played together before (at least so far as the historic data is concerned)

[jojowhite]

just a thought.
i don't think that it is a good idea to select a captain from a certain group.
the fact that all can play golf, everyone can be a captain, but only by election
from their team.

[CJ_London]

Easy enough to change the rules

[MikeCt]

Just got back from a small trip. There is a lot to take in here between the two examples I've been given.

With regard to Madpiet.... I'm not sure I'm understanding mp1 (Is mp1 related to one of the two tables) I want to learn this, so I do not want to dismiss anything just because I'm confused. I believe from what I'm seeing the Select statement creates the two tables listed beneath the Select Statement. I look forward to playing with the information.

With regard to CJ_London..... There's a lot to take in from what I see. You definitely have gone out of your way with this endeavor. I'm looking forward to opening your download and seeing how you did things. I'm sure I'll have a few questions going forward

I thank all that have given me the help I needed. I'm sure I'm going to learn a lot in the next few days
Mike

[madpiet]

SELECT DISTINCT PlayerID
FROM MatchPlayers mp1
WHERE mp1.PlayerID IN
(SELECT MatchID
FROM MatchPlayers mp
WHERE mp.PlayerID = @PlayerID)
AND mp1.PlayerID <> @PlayerID;

CREATE TABLE Match (MatchID INT IDENTITY PRIMARY KEY, MatchDate DATE....);
CREATE TABLE MatchPlayer(MatchID INT, PlayerID INT... (MatchID, PlayerID) is Primary Key.

mp1 is an alias for MatchPlayers table.

It's basically showing all the players @PlayerID has played with. Remove playerID because he can't play himself in a match. <g>

The hard part about getting good at SQL with Access is that you have to read the SQL statements. And you kinda can with Access. It just *really* likes parentheses. I should mock this up in SQL Server. just way easier for me to read.

[CJ_London]

my equivalent to that is my PlayedWith query

SELECT DISTINCT 
    tblEventPlayers.PlayerFK
  , EP1.EventDate
  , EP2.PlayerFK

FROM 
(tblEventPlayers INNER JOIN tblEventPlayers AS EP1 
   ON tblEventPlayers.PlayerFK =EP1.PlayerFK) 
INNER JOIN tblEventPlayers AS EP2 
   ON (EP1.TeamFK =EP2.TeamFK) AND (EP1.EventDate =EP2.EventDate)

WHERE 
   EP2.PlayerFK<>[tblEventPlayers].[PlayerFK]

[MikeCt]

Thanks again, this is going to take a while to process it all. There is a lot to get thru and understand. This was my first time seeing forms in access executing data. I looked for the code pertaining to the form buttons only to learn there is none. That's very new for me, I've been programming in Vb.net and C++ so long I'm just not use to all these to processes.
Mike

[CJ_London]

I looked for the code pertaining to the form buttons only to learn there is none.

Open the form in design view by right clicking on the windowbar and select design view


ensure the properties window is open (if not, click on the properties button on the design ribbon)
select a control
select the event tab in the properties window
then select the three dot icon against any event that has [Event Procedure]


Alternatively you can open the vba window by clicking on the visual basic button on the database tools ribbon

[MikeCt]

Thanks... you've been a great help. Maybe in the VBA Windows I'll see things I'm comfortable with