Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2011
    Posts
    18

    Unanswered: Trying to create a form to input basketball data actively

    Hi everybody, I am trying to make a database for my friends basketball league and I have a question I feel should be simple but I haven't been able to figure it out. I'd like to create a form that allows my friend to input statistical data for the game while it's going on. So basically the form should have Team Name, Game Number/Week Number, a list of the players names on the team and their individual statistics next to them such as Points, Fouls, etc. Right now I can't figure out a way to make a form like this. I can create a form that has Team Name, Game Number, Player Name, and then statistics for the individual player, it's just getting more than one player that's the problem. Any help would be greatly greatly appreciated, thanks!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    First things must go first. You must create a table (or several) that will store the data, then create one (or more) forms that will interact with the table(s) to perform database operations (Insert, Delete, Update, Select), then eventually use report(s) to retrieve and publish the contents of the table(s).

    The most important being the definition and the creation of a proper data model. See: The Relational Data Model, Normalisation and effective Database Design
    Have a nice day!

  3. #3
    Join Date
    Feb 2011
    Posts
    18
    Quote Originally Posted by Sinndho View Post
    First things must go first. You must create a table (or several) that will store the data, then create one (or more) forms that will interact with the table(s) to perform database operations (Insert, Delete, Update, Select), then eventually use report(s) to retrieve and publish the contents of the table(s).

    The most important being the definition and the creation of a proper data model. See: The Relational Data Model, Normalisation and effective Database Design
    Thanks much for the reply! Sorry maybe I should have been a little clearer, I have a small amount of database experience, I've created a few databases before and have had a couple database classes. I have already created the tables and forms to insert the basic data (Player, Team, and Schedule data) and I believe that the model is correct. I have included a screen shot of the data model in access, if you would like me to upload a copy of the actual database as well I am more than happy to do so. Please let me know if you see any glaring problems.
    http://i55.tinypic.com/mr5jbb.jpg

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In my answer, I had no intention of showing any superiority nor contempt, and re-reading it I realised that it could have been interpreted that way, so sorry for that!

    As far as I can judge, the schema of your database seems to be correct. I'm not sure of how to understand to part of your question where you say that "it's just getting more than one player that's the problem". My first answer would be that you could use a form-subform(s) model for the interface, but I don't know enough about exactly what you want to achieve for being able to provide an accurate answer. Getting a copy of the database could be helpful however a draft drawing or a schema of the interface (form or forms) with their basic functionalities would probably be a good start if you could provide it.

    I know almost nothing about basketball and this ignorance does not help me to imagine the aspect and specific functionalities of a program managing data in this domain. I'd be glad to help if I can, though.
    Have a nice day!

  5. #5
    Join Date
    Feb 2011
    Posts
    18
    No problem at all, I just wanted you to know I wasn't completely inept starting from scratch. I definitely do not have a lot of experience though so I'm very thankful for the help. As to how basketball works or how the database is supposed to work I will try and explain. There are 5 Tables as you saw, for each team there are many players, for each team there will be many team statistics (many games) and for each player there will be many player statistics (again many games to keep track of). Finally for each game in the schedule table there will be 2 teams statistics to keep track of and many players statistics to keep track of on each team. One of my problems is that with the way the database is right now, if I make a query trying to list a specific Game Number and Week Number (From Schedule) with Teams or Players (or player or team stats), I get no results. I figure this must be a problem with my table design but I don't see the problem. Maybe Schedule needs to be directly related to the Player and Team tables? I feel like that may have to do with my form problems. I've PM'd you a copy of the database so you can take a look at it. As far as the Form I am looking for I have attached a screen to show what I am trying to accomplish. In the picture you will notice the subform (I believe that's what it's called) has player first name and player last name with the statistics (Player free throws, fouls, etc) underneath. I would like all the players for the Team be listed on this subform with their statistics there to be filled out as a basketball game is ongoing. Hope that makes it a little clearer, thanks so much again for the help.
    http://i54.tinypic.com/2ut692d.jpg

  6. #6
    Join Date
    Feb 2011
    Posts
    18
    Any updates on this?

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by gambit07
    Hi, just wondering if you've had a chance to look at the stuff I sent you. Thanks
    Sorry for being late. I had a rather busy time at work and, moreover, your database is in Access 2007/2010 format (accdb), which means that I had to reinstall a virtual machine with Access 2010 because I still work in Acc 2003.

    From what you sent and to keep the things simple I basically see two solutions:

    1. In the screen copy of the "Schedule" form you uploaded, convert the subform ("Players Stats") from a single form to a data sheet form (or possibly to a continuous form). You'll have to re-design the general layout but you could have more than one player on the screen at the same time.

    2. A (to me better) solution would consist in adding a second subform object in datasheet mode to the "Schedule" main form which would list the players names. This list would act as a filter (or an index) for the "Player Stats" subform in single mode. This is what is done in the "Double Display" model available in Acc 2007/2010.

    Other solutions would imply modifications of the database schema.
    Attached Thumbnails Attached Thumbnails Frm_Schedule.jpg  
    Have a nice day!

  8. #8
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    You should also add a one-to-many relationship on TeamID from Team to Schedule.

  9. #9
    Join Date
    Feb 2011
    Posts
    18
    Quote Originally Posted by Sinndho View Post
    Sorry for being late. I had a rather busy time at work and, moreover, your database is in Access 2007/2010 format (accdb), which means that I had to reinstall a virtual machine with Access 2010 because I still work in Acc 2003.

    From what you sent and to keep the things simple I basically see two solutions:

    1. In the screen copy of the "Schedule" form you uploaded, convert the subform ("Players Stats") from a single form to a data sheet form (or possibly to a continuous form). You'll have to re-design the general layout but you could have more than one player on the screen at the same time.

    2. A (to me better) solution would consist in adding a second subform object in datasheet mode to the "Schedule" main form which would list the players names. This list would act as a filter (or an index) for the "Player Stats" subform in single mode. This is what is done in the "Double Display" model available in Acc 2007/2010.

    Other solutions would imply modifications of the database schema.
    Thank you much for the response, I'm not exactly sure how to implement either of those solutions but I will look into it now that I know how I can fix it!

  10. #10
    Join Date
    Feb 2011
    Posts
    18
    Quote Originally Posted by rogue View Post
    You should also add a one-to-many relationship on TeamID from Team to Schedule.
    Thank you I had that relationship at one point but wasn't sure if it was redundant and also it is a many to many relationship which I was taught you shouldn't have. Could you elaborate at all about the reason for doing that?

  11. #11
    Join Date
    Aug 2006
    Posts
    126
    Just a quick thought. Your initial post indicated that you wanted to be able to input data while the game is going on. In that case you wouldn't want to be continually switching between individual forms/subforms for each player's stats, the game goes too fast.

    Once you get the table relationships working properly based on the comments from Sinndho and others, I'd create a game entry form. I'd not rely on subforms at all, I'd create a query that had player names and stat fields in it. What teams are used could be selected in a switchboard when the db was being opened for that game.

    On the top half of the form I'd have team 1 and on the bottom half, team 2. Within each half I'd have a horizontal line of fields for each player. On the left would be their name (not editable) followed by a series of 'count' fields, one for each stat that you want to keep. I'd set up an intuitive series of keys that would add +1 to the stat associated with that key. During the game, a mouseclick anywhere on a players line would select that player, and a hit on the numberpad 3 for a 3-pointer, (an 'f' for a foul, etc.) would add +1 to the count for that stat for that player.
    You could also have keys set up that would add +1 to team, game or other non-player stats if needed. And with a little extra programming the key-clicks for counting baskets for the player, could automagically update a game-score counter.
    With a little practice, and keeping the most-used keys close together, a stats guy could keep track of even the fastest moving game. (unless you're like me and can't remember the players names/numbers from one minute to the next).
    Pete
    PGT

  12. #12
    Join Date
    Feb 2011
    Posts
    18
    Quote Originally Posted by Pete Townsend View Post
    Just a quick thought. Your initial post indicated that you wanted to be able to input data while the game is going on. In that case you wouldn't want to be continually switching between individual forms/subforms for each player's stats, the game goes too fast.

    Once you get the table relationships working properly based on the comments from Sinndho and others, I'd create a game entry form. I'd not rely on subforms at all, I'd create a query that had player names and stat fields in it. What teams are used could be selected in a switchboard when the db was being opened for that game.

    On the top half of the form I'd have team 1 and on the bottom half, team 2. Within each half I'd have a horizontal line of fields for each player. On the left would be their name (not editable) followed by a series of 'count' fields, one for each stat that you want to keep. I'd set up an intuitive series of keys that would add +1 to the stat associated with that key. During the game, a mouseclick anywhere on a players line would select that player, and a hit on the numberpad 3 for a 3-pointer, (an 'f' for a foul, etc.) would add +1 to the count for that stat for that player.
    You could also have keys set up that would add +1 to team, game or other non-player stats if needed. And with a little extra programming the key-clicks for counting baskets for the player, could automagically update a game-score counter.
    With a little practice, and keeping the most-used keys close together, a stats guy could keep track of even the fastest moving game. (unless you're like me and can't remember the players names/numbers from one minute to the next).
    Pete
    Hey Pete, I've uploaded a new screenshot of what my updated table structure looks like. I realized I didn't need the schedule table and GameID shouldn't have been used as a PK so I rolled it into the team statistics table. My problem is that (still) if I query any player statistics with team information I get no fields showing up. I guess what I'm trying to do is have Game number and Week number autopopulate for each player in the player statistics table. So if I create a game number and week number in team statistics I want that game number and week number to automatically be generated for the players on that team. Because right now I would have to be creating a Game and Week Number for each player on a team which seems idiotic. Does that make sense? If so, is there a way for this to be done? As far as the rest of your suggestions, that is exactly how I was thinking about trying to do it. I'm not exactly sure how to set up keys but I will look into it. I just wanted to say thanks for all the help again guys, it is much appreciated.
    Edit: After thinking about it I am pretty sure this would be done by using a JOIN of some sort but any other help would be great.
    http://i55.tinypic.com/345zskp.jpg
    Last edited by gambit07; 02-24-11 at 18:39.

  13. #13
    Join Date
    Aug 2006
    Posts
    126
    I'm not sure why you need all those relationships. I'd set specific relationships for each task (form or report, input or output) by how I set up that underlying query. You can only work on one game at a time, so in a form header you tell access what Week it is, what GameNr it is, Which TeamNr is team 1, Which TeamNr is team2. That header then populates the list of players on the screen. Also in the header you provide other essentially non-changing info such as which half it is. During the progress of the game you really only need a PlayerStats table/query open, with a link to the Player table to pick up the playernames for the designated TeamNr. Each when the half ends you have a button to save the stats for each player for that week/game/half and reset the fields to zero ready for the next half. Same at the end of the game. I know I may have over simplified the job, but I've found it is sometimes better to let the defining of relationships take place in the query, rather than in the general relationships. I can get away with that because I rarely work with tables directly, I almost always create a query (even a simple one with just one table and all it's fields).
    Now the disclaimer, I'm also anything but an expert, and rely on these guys for advice.
    They are great.
    PGT

  14. #14
    Join Date
    Feb 2011
    Posts
    18
    Quote Originally Posted by Pete Townsend View Post
    I'm not sure why you need all those relationships. I'd set specific relationships for each task (form or report, input or output) by how I set up that underlying query. You can only work on one game at a time, so in a form header you tell access what Week it is, what GameNr it is, Which TeamNr is team 1, Which TeamNr is team2. That header then populates the list of players on the screen. Also in the header you provide other essentially non-changing info such as which half it is. During the progress of the game you really only need a PlayerStats table/query open, with a link to the Player table to pick up the playernames for the designated TeamNr. Each when the half ends you have a button to save the stats for each player for that week/game/half and reset the fields to zero ready for the next half. Same at the end of the game. I know I may have over simplified the job, but I've found it is sometimes better to let the defining of relationships take place in the query, rather than in the general relationships. I can get away with that because I rarely work with tables directly, I almost always create a query (even a simple one with just one table and all it's fields).
    Now the disclaimer, I'm also anything but an expert, and rely on these guys for advice.
    They are great.
    Thanks for the advice as always. That sounds like it would work although I'm not exactly sure how to implement it, if you have any more details of how to set that up it'd be great. I will be trying to figure it out though, thanks!

  15. #15
    Join Date
    Feb 2011
    Posts
    18
    Quote Originally Posted by Pete Townsend View Post
    Just a quick thought. Your initial post indicated that you wanted to be able to input data while the game is going on. In that case you wouldn't want to be continually switching between individual forms/subforms for each player's stats, the game goes too fast.

    Once you get the table relationships working properly based on the comments from Sinndho and others, I'd create a game entry form. I'd not rely on subforms at all, I'd create a query that had player names and stat fields in it. What teams are used could be selected in a switchboard when the db was being opened for that game.

    On the top half of the form I'd have team 1 and on the bottom half, team 2. Within each half I'd have a horizontal line of fields for each player. On the left would be their name (not editable) followed by a series of 'count' fields, one for each stat that you want to keep. I'd set up an intuitive series of keys that would add +1 to the stat associated with that key. During the game, a mouseclick anywhere on a players line would select that player, and a hit on the numberpad 3 for a 3-pointer, (an 'f' for a foul, etc.) would add +1 to the count for that stat for that player.
    You could also have keys set up that would add +1 to team, game or other non-player stats if needed. And with a little extra programming the key-clicks for counting baskets for the player, could automagically update a game-score counter.
    With a little practice, and keeping the most-used keys close together, a stats guy could keep track of even the fastest moving game. (unless you're like me and can't remember the players names/numbers from one minute to the next).
    Pete
    I've been trying to implement this and I have one question for you, how would I go about telling access which team is team 1 and which is team 2?

    Edit: Along the same lines I'm also wondering how I can get multiple players to show up on the form, right now if I add more than one player on the form it just gives me the same players name for each box. Sorry I wish I had more experience with this, thanks so much for the help.
    Last edited by gambit07; 03-02-11 at 14:47.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •