Results 1 to 11 of 11
  1. #1
    olmstead.jim.w is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2018
    Location
    Warren Center, PA
    Posts
    6

    Question Learning and Creating: Am I in Over My Head?

    Hi Folks,

    I am new to Access and am trying to learn from tutorials and online guidance. I'd like to give you all an idea of what I would like to accomplish and get an opinion if you think its possible to learn to do it on my own or if I am in too far over my head.

    I play on a dart league (baseball style if that helps) and in the past we have kept track of each players Weekly scores for each game they play (3 games a week) and what their average for that week was. The way we have done it in the past was sitting down with pen and paper at the end of the week, write out everyone's scores, use a calculator, get the averages and then also write down each players total score (all weeks combined each week) listed highest to lowest.

    Should this seem like a simple task to do? Can I create tables and a form to enter in what the players names are for the season, and then have a form where I can select a name from a dropdown box, what week it is, and enter the 3 individual scores for that week - then have a query that will return a printable sheet with all the figures that we usually have to do by hand each week with the push of a button?

    In my head this all sounds simple but the more I'm looking into it I wonder if I'm over my head or if there's certain material I should read/watch to get me in the right direction.



    Any advice, help and/or opinions would be welcome at this point.

    Thank you, Jim

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    See if this will get you started, very basic no frills:
    I know nothing of darts, so the example scores I used are probably bizarre.

    darts_V01.zip

    Two tables, players and scores
    A form/subform for data entry
    A totals/avg query
    A report based on the above query to show player average each week.

  3. #3
    olmstead.jim.w is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2018
    Location
    Warren Center, PA
    Posts
    6
    davegri

    Thank you for that. That will work for what I want to do. I plan to learn how to do this but this give me a great starting point. Would you have any advice on material that would be helpful to learn? I have read a couple online tutorials but so far ive only found ones that tell you what you can do no necessarily how to do it properly...

    Thank you again, I really do appreciate it and its inspiring me to learn even more at this point.
    Jim



  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Jim, I would approach it this way. When you want to add functionality, Google it. You will almost certainly find a solution.
    If the solution doesn't quite fit, you can go to a forum such as this to get fine tuning help.
    Given enough time an effort, it will all start to make sense.
    The example DB I provided covers a lot of basic ground: Related tables, a form/subform based on those tables, a query and a report. That covers about 90% of what most databases provide.

    You probably want to add a form to allow adding/editing the players table.
    Add a combobox to the data entry form for finding a player.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    How you learn is very much affected by your preference for learning, as far as I'm concerned. Books are great for those who aren't as adept with certain aspects of on line learning. They're also quite good for being able to interrupt a session then pick up from where you left off of. You can also scan Contents for subjects that have immediate interest since the chance that everything is laid out in the right order for you is slim at best, and it's easy to repeat an effort to understand a concept. They also often contain disc media or provide access to online files that have built and illustrate the concepts taught in the book. Just sayin...

    Whether or not you would be in over your head is not something we can readily advise about - unless you're like my wife, who can barely operate functions on her phone. Then I'd say forget it.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #7
    olmstead.jim.w is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2018
    Location
    Warren Center, PA
    Posts
    6
    Thank you everyone for the responses.

    davegri - Thank you for the advice, it makes sense and I will take what you have given me and use that to help me learn farther what I want to know to do more.

    Micron - I agree that a book might be useful for me and I like the idea of scanning info that I might want to use later. I'm constantly making note on paper that I try to use later, but scanning them would make it easier for me to keep things organized I think. I feel more confident after reading all of everyone's advices that I will be able to learn this and make use of it in many ways.

    orange - I took a brief look at your link you provided with all the helpful stuff in it. I plan to look farther into it and see what all you have there but it looks like there is a lot of good information to keep me busy for a while. I think that with a little more effort and time I will be able to make some good progress.

    Thank you again everyone for the help and advice. I do appreciate it.
    Jim

  8. #8
    olmstead.jim.w is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2018
    Location
    Warren Center, PA
    Posts
    6

    Question

    Hi All,

    Here is what I have started on my own with some guidance from the form that was given to me along with some reading and video watching (a lot of a little at this point it probably seams). So far I have my tables, relationships and form the way I imagine them to be (with further improvements on looks when its all functional).

    What I need to learn if anyone would be willing to give me a little description or a good place to get the info (I haven't found exactly what I'm looking for in my searches so far) is how to create a query that will return the name date, each score and the average of the scores. Every time I do it I seem to come up with the average of the individual columns (Average of all in column Score G1) but not the Average of the 3 columns (Average of Score G1, Score G2 and Score G3).

    I'm not sure if what I'm asking make sense or not. I can see it in my head but as I am new to all of this I feel I don't know how to properly ask what I need to know.

    Thank you for any help in advance.

    Click image for larger version. 

Name:	Players Table.PNG 
Views:	29 
Size:	8.1 KB 
ID:	33669

    Click image for larger version. 

Name:	Scores Table.PNG 
Views:	28 
Size:	11.8 KB 
ID:	33668

    Click image for larger version. 

Name:	Relationships.PNG 
Views:	28 
Size:	11.9 KB 
ID:	33667

    Click image for larger version. 

Name:	Scores Form.PNG 
Views:	28 
Size:	10.7 KB 
ID:	33670

  9. #9
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    As you have discovered, queries like to process data vertically, not horizontally. If you want horizontal calculations, a spreadsheet is more suitable.
    That's why in the example DB that I supplied, each Scores record contains only one score. For 3 games, 3 records are needed with same date and player.
    Take a look at the tables and the query in that example and see how it easily accomplishes the averages.

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Further to davegri's comments, you can calculate the average of the 3 game scores in a query using
    Code:
    ([score g1]+[score g2]+[score g3])/3
    However there are a number of issues with this
    1. If any of the scores are blank, you will get an error. This can be fixed by wrapping each score in the Nz function
    Code:
    Nz([score g1],0)
    2. At some point you may need a 4th or 5th game added. Then you will need to add additional fields to your table and redesign your query.

    For those reasons, spreadsheet like tables where you have more than one field of the same type are a bad idea.
    Replace with e.g. Score and GameNo so each record contains one game score as in davegri's example
    Last edited by isladogs; 04-22-2018 at 11:22 AM. Reason: Correction
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    olmstead.jim.w is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2018
    Location
    Warren Center, PA
    Posts
    6
    Hi Folks, I just wanted to update on my progress in learning...

    I unfortunately didn't see ridders52's comment until today and had learned a bit from davegri's help and example and also found a nice tutorial on iTunesU. Currently I started using:

    Ave: ([G1]+[G2]+[G3])/3

    in my Query and that's been working nicely in limited practice and playing around with numbers, however ridders52 brings up some good points as well so I may be playing around with those additions for another version for a friend of mine. In the League that we play during the winter we only ever play 3 games, however the one that my friend is going to be doing it may very from week to week depending on various things so being able to add more games than three would be perfect for him.

    So at this point I have 2 designs that I think I will be building with my specific user in mind and am planning to modify each using the help that you have both given me. I want to say thank you, I appreciate it very much, this has been and continues to be a fun learning experience for me.

    Thank you again, Jim

Please reply to this thread with any new information or opinions.

Similar Threads

  1. soccer table head-to-head query
    By sgramesh75 in forum Queries
    Replies: 2
    Last Post: 03-29-2017, 11:09 PM
  2. Learning VBA
    By AishlinnAnne in forum Programming
    Replies: 12
    Last Post: 08-24-2016, 03:10 PM
  3. in Way over my head HELP
    By 91hrdbdy in forum Programming
    Replies: 8
    Last Post: 11-26-2013, 01:06 PM
  4. In over my head
    By TylerB in forum Access
    Replies: 2
    Last Post: 05-04-2011, 04:07 PM
  5. Learning VB
    By aabh in forum Programming
    Replies: 3
    Last Post: 02-16-2010, 02:37 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums