I am trying to create a db for my local bowling club and so far I've got -

tblBowlerDetails, ID, Surname, firstName etc etc

tbl Matches BowlerID, MatchDate, Venue, Team, Game, ClubPlayed.

The relationship between the two tables above is obviously one to many.

There maybe four Teams playing on any given day with up to four bowlers per team. OK so far??

After the games are played I would like to record whether the teams won or lost their respective games. Can anyone throw any light on this one Please?

I am assuming that this is a many to one relationship but how do achieve the end result?

Thanks in advance - Ray

On Sunday, May 30, 2021 at 9:34:44 AM UTC+10, Ray Pearson wrote:
> I am trying to create a db for my local bowling club and so far I've got -
>
> tblBowlerDetails, ID, Surname, firstName etc etc
>
> tbl Matches BowlerID, MatchDate, Venue, Team, Game, ClubPlayed.
>
> The relationship between the two tables above is obviously one to many.
>
> There maybe four Teams playing on any given day with up to four bowlers per team. OK so far??
>
> After the games are played I would like to record whether the teams won or lost their respective games. Can anyone throw any light on this one Please?
>
> I am assuming that this is a many to one relationship but how do achieve the end result?
>
> Thanks in advance - Ray

Ray,
One option would be to have an ID field in the Matches Table. "tblMatchesWonBy" - and store the ID of the winning team there. However it might be better to store each person/teams score...and just calculate the winning team through a query (Whoever has the highest score)
Or perhaps you need that at the GAME level....which might be then storing the PersonID - and handy to track their personal stats if/when they move teams....and still able to total a team of players for overall team winner.
In that case you would need a tblTeams. which can have names, captain (personID), etc.

Hope that helps.
Mal.

I would have the following tables:
Bowlers as you have it
Matches with match date, venue, opponents
Teams with a name
then I would have some linking tables
TeamBowlers with TeamID and BowlerID i.e. who is playing in each team
MatchTeams with MatchID and TeamID; and their result i.e. which teams are playing in each match

I am assuming that a team of, say four players, can play in a number of matches. If one or more member changes it becomes a new team.
Over a time a Bowler could be a member of a number of Teams. You would need to take care that when selecting which teams are to play in a given match no Bowler is the member of more than one team in that match.

Of course I may change these when I develop it but I would start here

Hope this helps

Jim

On Sunday, 30 May 2021 at 00:34:44 UTC+1, Ray Pearson wrote:
> I am trying to create a db for my local bowling club and so far I've got -
>
> tblBowlerDetails, ID, Surname, firstName etc etc
>
> tbl Matches BowlerID, MatchDate, Venue, Team, Game, ClubPlayed.
>
> The relationship between the two tables above is obviously one to many.
>
> There maybe four Teams playing on any given day with up to four bowlers per team. OK so far??
>
> After the games are played I would like to record whether the teams won or lost their respective games. Can anyone throw any light on this one Please?
>
> I am assuming that this is a many to one relationship but how do achieve the end result?
>
> Thanks in advance - Ray

On Monday, May 31, 2021 at 6:54:29 AM UTC+12, Keith Tizzard wrote:
> I would have the following tables:
> Bowlers as you have it
> Matches with match date, venue, opponents
> Teams with a name
>
> then I would have some linking tables
> TeamBowlers with TeamID and BowlerID i.e. who is playing in each team
> MatchTeams with MatchID and TeamID; and their result i.e. which teams are playing in each match
>
> I am assuming that a team of, say four players, can play in a number of matches. If one or more member changes it becomes a new team.
> Over a time a Bowler could be a member of a number of Teams. You would need to take care that when selecting which teams are to play in a given match no Bowler is the member of more than one team in that match.
>
> Of course I may change these when I develop it but I would start here
>
> Hope this helps
>
> Jim
> On Sunday, 30 May 2021 at 00:34:44 UTC+1, Ray Pearson wrote:
> > I am trying to create a db for my local bowling club and so far I've got -
> >
> > tblBowlerDetails, ID, Surname, firstName etc etc
> >
> > tbl Matches BowlerID, MatchDate, Venue, Team, Game, ClubPlayed.
> >
> > The relationship between the two tables above is obviously one to many.
> >
> > There maybe four Teams playing on any given day with up to four bowlers per team. OK so far??
> >
> > After the games are played I would like to record whether the teams won or lost their respective games. Can anyone throw any light on this one Please?
> >
> > I am assuming that this is a many to one relationship but how do achieve the end result?
> >
> > Thanks in advance - Ray
Thanks to Mal and Jim for your suggestions. Now I have a starting point.

Thanks again - Ray