locked
Need Help ASAP RRS feed

  • Question

  • Hello,

    I'm creating a database on ms access for a basketball league.

    I want to be able to input data from each game played like points scored by one player etc. into a table, calculate averages after a number of games played and then display those averages somehow. Also if there's a way that I can get the system to automatically calculate the averages and then display them then please let me know.

    Any sort of help will be much appreciated!

    Monday, October 19, 2020 7:30 PM

Answers

  • I have created a bunch of tables, I entered some data into them and tried to make a query to get the averages of certain data, but after running the query it just displays some of the data and not the averages. I also looked at some tutorials but still have this issue.


    Did you remember to change Group By to Average for those field you need averages values?

    If that's not the issue, can you post the SQL for your aggregate query

    • Marked as answer by MegaElijah Thursday, October 22, 2020 9:12 AM
    Tuesday, October 20, 2020 9:41 PM
  • I constructed a test database with the following tables, fields and data types:

    And the following relationships:

    After inputting some test data and constructing a query, I was able to get Totals and Averages for each team, each game and each player. As you can see, each game has a date and location (optional) and 5 players for each TeamOpponentID. This scenario seems to work well. 

    Test data is shown below:

    You can see The Eagles and The Who played each other twice. I hope this can help you get started in the right direction. From here you can construct data input forms and data input queries and other queries using Group By Sum and Avg as Isladogs52 stated above.

    • Marked as answer by MegaElijah Thursday, October 22, 2020 9:12 AM
    Wednesday, October 21, 2020 9:50 PM

All replies

  • You will need to create an 'aggregate' query to calculate averages.

    The results can be displayed in a form.

    What have you done so far? What exactly do you need help on?

    Monday, October 19, 2020 8:41 PM
  • I have created a bunch of tables, I entered some data into them and tried to make a query to get the averages of certain data, but after running the query it just displays some of the data and not the averages. I also looked at some tutorials but still have this issue.


    Tuesday, October 20, 2020 8:49 PM
  • I have created a bunch of tables, I entered some data into them and tried to make a query to get the averages of certain data, but after running the query it just displays some of the data and not the averages. I also looked at some tutorials but still have this issue.


    Did you remember to change Group By to Average for those field you need averages values?

    If that's not the issue, can you post the SQL for your aggregate query

    • Marked as answer by MegaElijah Thursday, October 22, 2020 9:12 AM
    Tuesday, October 20, 2020 9:41 PM
  • I constructed a test database with the following tables, fields and data types:

    And the following relationships:

    After inputting some test data and constructing a query, I was able to get Totals and Averages for each team, each game and each player. As you can see, each game has a date and location (optional) and 5 players for each TeamOpponentID. This scenario seems to work well. 

    Test data is shown below:

    You can see The Eagles and The Who played each other twice. I hope this can help you get started in the right direction. From here you can construct data input forms and data input queries and other queries using Group By Sum and Avg as Isladogs52 stated above.

    • Marked as answer by MegaElijah Thursday, October 22, 2020 9:12 AM
    Wednesday, October 21, 2020 9:50 PM
  • Thanks so much for the help I used the guides that you gave me and it is working really well now!!
    Thursday, October 22, 2020 9:10 AM
  • After going along with Lawrence's guides and now creating the queries it is working properly and doing exactly what I want it to do. Thank you for your help!
    Thursday, October 22, 2020 9:11 AM