Many to many

[Andyjones]

I recored the results so the query you will see in old db produces a player league table of which player is performing the best etc.

Write here goes, one last attempt at trying to explain what Im trying to do, I have a database that works but want to re-design it to do the the following

1. store and record match data, see below
Players
Team Played
Date of Home Home/away Game

Played at home - Yes No
our score
opp score
Won H - yes/no
Lost H - yes/no
Points - 2 points 4 each player if they win at home

Played away - yes No field
Our score - if player reaches 21 they have won there game
Opp score
Won Away Yes/no
Lost Away Yes/ No
Points Away 3 points 4 each player of they win away.

However in my 1st attempt at re-designing it with some help, how i want the data entry form to look is perfect but i do not now how to get the query i have in the 1st database t work in the 2nd, because the query in the 1st database relys on two sets of data to add up.

ideay i would like two data entry forms, for the home and away matches, put players that make up my sunday team is a total of 8 but the players names can very from match to match depending on who is avalible or who is performing. like many to many.

If anyone could help with either a design that they no will work or a query that will work then please feel free to have a go.

Link 1 and link to i have had to use box.com to upload even though i have cut the info down as much as i can.

so could someone please help resolve this issue.



http://www.box.com/s/zl0afvze86rzg2infk5h - What im trying to do re-design
http://www.box.com/s/4rmrkrqbz9y8by6ihlao- What old DB looked like

[RayMilhon]

Your design needs to be adjusted. The Opponents Score field is in the wrong table. Your players do not score for both teams. Also there are 2 fields in your tblMatchPlayers Ourscore and Points Whats the difference between the 2? I'm presuming that your teams table is your opponents as your players table does not have a link to a team.

Your Design:

Table: tblMatchDatePage: 1
Columns
Name Type Size
MatchdateID Long Integer4
TeamID Long Integer4
Match Date Date/Time8
MatchTypeID Long Integer4

Table: tblMatchPlayersPage: 2
Columns
Name Type Size
MatchPlayersID Long Integer4
MatchDateID Long Integer4
PlayerID Long Integer4
OurScore Long Integer 4 Individual score or team score? If it's team score should be in table above.
OpponentsScore Long Integer4 Wrong location should be in table above
Points Long Integer4 What's the diff from OurScore

Table: tbluMatchTypePage: 3 Useless table there's only 2 values and that is in the match table keep the ID Numbers but in your forms and reports you use an IIF statement to display IIF([typeid]=1,"Home","Away") you only need a lookup table if there are multiple values > 2 or there is the possibility of additions. You could also use a field called Home that is yes/no If not home then it's away.

Columns
Name Type Size
MatchTypeID Long Integer4
MatchType Text 255

Table: tbluPlayersPage: 4
Columns
Name Type Size
PlayerID Long Integer4
Player Text 255

Table: tbluTeamsPage: 5
Columns
Name Type Size
TeamID Long Integer4
TeamName Text 255

[Andyjones]

Each player if my team has to score 21 to win. If they win at home they get 2 points. If they win away they get three points if they loose they get 0 points.

When we have a club meeting b4 the season starts we decided to have 2 points per player for a home game win and 3 points of away wins . At the end of the season and throughout we put up league tables for each team to show who is winning the team averages but it's set out like a league table in foot as the Sunday averages query will show.

The Sunday game consists of a team of 8 players either side the aim of game us 4 each player to win there individual matches to produce a team win and the same for Saturdays but Saturday team is 12 players so if we can get the Sunday one sorted I can then use that design for Saturday.

And yes it is the 1st player to 21 to win

I only need to record my players name and score and openets score

Pls feel free to show me how you would design the tables and forms.

My email is jonesstanwellatsky.com

[RayMilhon]

Ok, not quite sure I understand so let me try to clarify something first. Which sport is it? In your initial post you talked about team now your saying each player so I'm a bit confused.

[Andyjones]

Hi

Sorr

the design that you have come up with looks good, click on this link and this should show you what my old db used to do.

if you would be so king in sorting the table design out and main data entry form i would be most apreciate of this.

http://www.box.com/s/88xjepx4902vos0zjfec
http://www.box.com/s/88xjepx4902vos0zjfec

[RayMilhon]

I will look at this tonight and post it back tomorrow.

[RayMilhon]

Here's your table structure

Table: tbl_Location
FieldName Type Size
LocID Long Primary Key
LocText Text 55 Description of Location. In your case there are only 2 values Home and Away. Using a location table you can easily change it if required at a later time to track the actual locations

Table: tbl_Matches
FieldName Type Size
MatchID Long Primary Key
TeamID Long Foreign Key to Team Table
PlayerID Long Foreign Key to Player Table
LocID Long Foreign Key to Location Table
Matchdate Datetime Date of the match
OurScore Long Your Score
OppScore Long Opponents Score

Table: tbl_Players
FieldName Type Size
PlayerID Long Primary Key
Playername Text 50
Status Long Foreign Key to Status Table

Table: tbl_Status
FieldName Type Size
statusID Long Primary Key
statusDesc Text 15 Status Descriptions ("Saturday","Sunday","Both")

Table: tbl_Teams
FieldName Type Size
TeamID Long Primary Key
TeamName Text 50

Your Form would be designed as the following
Form Recordsource = tbl_matchs

First you would have a textbox controlsource = Matchdate

you would have 3 comboboxes for the following:

1. Opponents set the properties as follows:
opponents Team Row Source = Select teamid, teamname from tbl_teams
control source = teamid
bound column is 1
column Widths = 0;1

2. Location
control source = locid
Row Source = Select locid, locdesc from tbl_location
bound column = 1
column count = 2
column width = 0;1

3. Player this ones is built in code.

Put the following function in in a module

Public Function Players(Optional ByRef dtval as date) as string

Dim sqlstring as string, sqlwhere as string, lngWeekday as long
sqlstring = "Select PlayerID, PlayerName from tbl_Players "
If Isdate(dtval) Then
lngweekday = Datepart("ww",dtval)
Select Case lngweekday
Case is = 1
strwhere = "Where status In (2,3) "
Case is = 7
strwhere = "Where status In (1,3) "
End select
Else
strwhere = ""
End if
Players = sqlstring & strwhere
End Function

Then you would call that function in the Load Event of the form would return all players regardless of day and in the change event of the matchdate textbox above you would call it like this

LoadEvent
cboplayers.rowsource = Players() 'Since you wont have the date field populated yet

textbox on change event
cboplayers.rowsource = players(me.textbox)

Your score and opp score would be 2 textboxes.

Everything else can be calculated from that

Points can be calculated by the following function

Public function points(ByRef lngourscore as long, lngoppscore as long, lngloc as long) As long

If lngourscore > lngoppscore Then
If lngloc = 1 Then
points = 3
else
points = 2
End if
end if

end function


Experiment with that and let me know what you think