Results 1 to 15 of 15
  1. #1
    Join Date
    May 2009
    Posts
    8

    Unanswered: calculating total and average in form

    i want help with how to calculate the average and the totals for this table. i am doing a project on cricket.. i tried using ecpression builder but the only problem is that it only calculates the total in the first row, which emans its not really the total and its just the number in the first row.. i basically want to add the first rows runs and the second rows runs to create a total.. i will add a picture for you to get a better diea of what i am talking about

    as you can see from the pic the totaql runs are being shown as 4 where it should be 10 and same for the overs bowled.. i dont really know how to caluclate the average.. please help..
    Attached Thumbnails Attached Thumbnails total copy.gif  

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Please post a copy of your database with some sample data and I will look at it over the next few days. You may need to do some coding so that the totals and averages are relative to each player.

  3. #3
    Join Date
    May 2009
    Posts
    8
    i have attached the version... could u please try to do it by tommorrow... coz this is for a coursework which is due in on the tuesday... if i can get it by today night or tommorrow it would be really helpful coz i need the database for doing screenshots and doing some tests on it... please try to do it by today or tommorrow morning... plz..
    Attached Files Attached Files

  4. #4
    Join Date
    May 2009
    Posts
    8
    the form you need to do the total in is the form FRM_PlayerStats... and if you hve time then could you do FRM_Match where you just need a total and no average or anything other thing... i could try and use the code for this form if its the same as the other one..

  5. #5
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Any reason why you are displaying the total runs and average on the screen and not storing the results?

  6. #6
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Please find an updated version.

    I made the following changes to the form FRM_Playerstats

    Changed the caption to read Total Runs Scored and called the text box tot_runs. Now in the control source for tot_runs entered =DSum("[RunsFor]","TBL_TeamPlayer","playerid= forms!frm_playerstats!playerid") this will give you the Total runs for the selected player.

    Added an unbound text box called tot_innings this will give you the total innings for the selected player then in the control source for this text box added =DCount("[RunsFor]","TBL_TeamPlayer","playerid= forms!frm_playerstats!playerid") to give you the total innings for the selected player.

    Finally,in the ave_runs text box I added the following in the control source to give you the average runs per innings =Format((Forms!frm_playerstats!tot_runs/Forms!frm_playerstats!tot_innings),"0.00").

    You will need to HIDE the tot_innings text and label by setting their Visible properties to No.

    Another question, the entries in the sub-form are for a player who actually faced a ball and does not include a player who was on the team and did not bat?

    This will give you a start, sorry I can not provide more assistance now as it is night here and I have to travel for work during the next few days,
    Attached Files Attached Files

  7. #7
    Join Date
    May 2009
    Posts
    8
    this is more than enough... my teacher could not do this.. he took about 4 weeks of my lessons using microsoft expressions to do it.. you guys ae gr8.. i am so greatfull.. atleast i can achieve some good marks in coursework nw.. my coursework was based on solving problems and this was one of them.. coz manually calculating would be a pain... thankyou so much... really greatful to u...

  8. #8
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Thank you for your reply.

    I suggest that you have a look at HELP for DCOUNT and DSUM.

    For more BROWNIE points I have changed your subform column Fixture ID to Game and also changed the Row Source for that drop down so that a more meaningful item is display e.g. Date played Opponent and Type of Game. To another user a reference number e.g. FixtureID is meaningless.

    See attached file.

    In future do not leave it to the last minute to submit your problem as some forum members do not have the time to immediately respond to your question. If you submit it earlier enough then someone on this forum will provide some tips etc. for you to investigate.
    Attached Files Attached Files

  9. #9
    Join Date
    May 2009
    Posts
    8
    thx for this... really greatful to u... i knw i shouldnt hve left for this long.. but u c u wont expect your teacher to leave u at the last moment saying i dont knw go do it urself.. i can provide any more help.. this is after spending most of my lessons with him trying to do it and hving 5 copies of my database after mucking the others up.. lol..

  10. #10
    Join Date
    May 2009
    Posts
    8
    may i ask you... wht the qry1 in the queries section is for.. i need to explain all parts of the database when i screenshot it.. is tht used to calculate the runs for.. coz i used user formula for runs for i mean the control source.. to calculate the rest.. i.e. runs against, overs bowled and number of wickets taken... i just chaanged the bit in the forumla where it said runs for inside the brackets.... so is the formula linked to the query.. i went inside the design view of the query and it has just got runs for in it..

    last question.. i used the runs against and overs bowled to calculate the economy rate of the bowler.. the only problem is that its working properly when there are number but like when u hve a scorer where the fields overs bowled and runs agianst are zero.. it shows "#Num!".. would i be able to change this... i am sorry i am asking so many questions...

  11. #11
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Sorry, I should have deleted that query. It is not linked to any of your forms etc. I used this query for testing etc.

    Regarding your scorer you should be able to change it but, I do not have the time over the next couple of days to look at the scorer.

  12. #12
    Join Date
    May 2009
    Posts
    8
    ok so i will delete tht query... ok noprobs if u dnt hve time.. thx for ur help...

  13. #13
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by jaison2
    ok so i will delete tht query... ok noprobs if u dnt hve time.. thx for ur help...
    Wow, you mean you have a teacher who is teaching this to you, but just says they don't know how to do it and you go find out how yourself? Hmmm. How will they know if you have it just right? And just a tip for the future, though Poppa Smurf was kind enough to do your assignment for you, it doesn't help you any in learning how to do it. You will have to actually learn your work somehow. I suggest you hit the books and play with Access and actually learn. Funny, I worked for a company and finally designed the database that later ran the company and would not upgrade me to DBA or anything near it, just kept me as a DataEntry person. What chapped my hide was the boss hired his buddy who needed a job, and made him the DBA and paid handsomely. What I was to do was bring him up to speed on what everything was that I had done. Hahaha, that was fine and dandy until I got laid off much later. He called me asking how to do this that and the other. I just laughed at him. Getting paid to know something that he knew nothing about, THOUGH he had his degree and certification. Wow, what a real bummer.
    So be nice and try to actually LEARN designing a database.

    Have a nice one,
    BUD

  14. #14
    Join Date
    May 2009
    Posts
    8
    Quote Originally Posted by Bud
    Wow, you mean you have a teacher who is teaching this to you, but just says they don't know how to do it and you go find out how yourself? Hmmm. How will they know if you have it just right? And just a tip for the future, though Poppa Smurf was kind enough to do your assignment for you, it doesn't help you any in learning how to do it. You will have to actually learn your work somehow. I suggest you hit the books and play with Access and actually learn. Funny, I worked for a company and finally designed the database that later ran the company and would not upgrade me to DBA or anything near it, just kept me as a DataEntry person. What chapped my hide was the boss hired his buddy who needed a job, and made him the DBA and paid handsomely. What I was to do was bring him up to speed on what everything was that I had done. Hahaha, that was fine and dandy until I got laid off much later. He called me asking how to do this that and the other. I just laughed at him. Getting paid to know something that he knew nothing about, THOUGH he had his degree and certification. Wow, what a real bummer.
    So be nice and try to actually LEARN designing a database.

    Have a nice one,
    BUD
    i am only in year 13.. i tried my best from all the tutorials i could find online.. i did find some but just tht it did not work in adding it up properly which i have described.. thts the only readon why i looked out for someone else to help me who knew what i wanted... fortunately popa smurf helped me.. i get what you are saying bud.. will learn everything in future when i get into uni.. lol.. thx for the advice.

  15. #15
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Jaison2

    I now have some spare time so if you still need some help with your project please attach your latest version and a list of your requirements.

    Also, have a look at this site, it will give you a good understanding of Access, note some of the tutorials are advanced.

    Technology Tutorials

Posting Permissions

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