Results 1 to 15 of 15
  1. #1
    Savannah is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2017
    Location
    Australia
    Posts
    14

    Query Question

    Hi,


    I have a sub-table that records points awarded at horse shows. In that sub-table I total the points from individual shows but I don't have an overall total from every show.

    I would like to run a query (or any other suggestions on how to do this) that will give me the total points from all shows and classes.

    This is a current query that I have created showing the multiple classes and their totals. What can I do to give me one entry per horse with the total of all shows? Thank you.

    Click image for larger version. 

Name:	Query shows.jpg 
Views:	37 
Size:	123.8 KB 
ID:	29940
    Attached Thumbnails Attached Thumbnails Query.jpg  

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    use a group by query - see totals option on the ribbon. Group by horse, owner and status, sum each of the points columns. I don't see any data to identify a show but since you want for all shows this should not matter.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Usually, it is considered bad practice to store calculated values. This should be done via a form.
    If you edit a single value, the calculated result is likely to be wrong. It would be pointless to design a method of dealing with this when the right thing to do is to avoid the situation altogether. I agree that a Totals query would be the way to go IF a form is not involved. If it is, I'd do the calculation in the form itself.

    If the displayed table field names are not captions, you are not following recommended naming practices either, which at best causes extra typing and care to avoid issues; at the worst, non-functioning procedures or methods with or without error messages.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Savannah is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2017
    Location
    Australia
    Posts
    14
    Thank you very much

  5. #5
    Savannah is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2017
    Location
    Australia
    Posts
    14
    I ran the query using the group by option - thank you. The only issue I'm having now is that it is not calculating the decimal points ie: the shows include 1/2 marks (0.5). I've checked the original forms and the individual show results and totals all allow decimals.
    Thank you for your response Micron, unfortunately, I'm not that advanced at using Access and I'm not really following how I would do a calculation on the total of all shows in the initial form. It already has 5 columns of numbers depending on type of class and then there's a total column per show that calculates those 5 columns. The initial values are stored via a form. The queries are something that will be run once a year to calculate totals for annual awards and will be re-run based on the data in the forms each subsequent year.

    Click image for larger version. 

Name:	Query.jpg 
Views:	33 
Size:	156.5 KB 
ID:	29943

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    What Micron is saying is that don't store totals on tables, remove those fields. They become inaccurate over time. Create queries instead so that the data is always up-to-date and always refer to those instead.

    This query is giving you totals by horse, save the query as qryTotals_By_Horse. Then make another query, qryTotals_By_Show and group on show. Etc. You may also want to add in a Year criteria otherwise over time these totals will give incorrect results.

    Did you test this query? What was the total amount when you added it up manually from the tables? Were there any decimals left over or did it end in ".00"? In order to be a developer in software, your job is to know exactly what you want to happen beforehand, don't leave it up to the computer to know what you want. Before running this query, get a total for each of the fields so that you know whether your query is working the way you want it or not.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Apologize if I've incorrectly concluded that the displayed query results are a reflection of the table itself. The total column shown might be a calculation in the query and not a field in the table.

  8. #8
    Savannah is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2017
    Location
    Australia
    Posts
    14
    Hi Aytee, yes I'm trying to create a query to calculate the total of the points awarded to each horse, this particular query can cross years as this one is a running total. I did the query and added up the points manually (I have it all already totalled in a spreadsheet so I'm aware of what I want the result to be) and it is not calculating any of the .5 points within the sub-table. The total has no decimal (.00) at all, only whole number.

    I have done all the calculations and reports I need for this year in a spreadsheet, but now I need to try and set them up within the database so any future people working the data can do it via a quick query or report instead.

    Micron, thank you. I'm just trying to verify, which I know can be hard when you don't have the entire database. The show total column is within a table, however that table is a sub-table in the main Form and data is only entered via the form, (you can search on each horse, bringing up that horses form and add/change data via the form which I can see is recalculating if it ever requires changes and it is recalculating if I open the table behind the form). It is for a total per horse/per show and feeds a number of queries that run on 'between dates' based on section/gender/state etc. The between dates run from August to July so not a standard Jan-Dec year. Would you still suggest to remove this total and run as a query or would that one be ok to leave in the table and therefore the form?

    In regards to naming of queries, should I use underscore for all spacing?

    LOL, I wish I had the skills to be a developer. To give you some background on my knowledge, I had this initially as a spreadsheet and now I'm trying to create something a little easier for someone else to operate (as long as it's working correctly and requires no changes). I've been googling 'how to's' to create this entire database as other than high school Access over 20 years ago and a bit of reporting from a program at work 10 years ago, I have no experience with it. So I'm muddling my way through it.

    Would be even better if I could create an app that incorporated this data but that's so far out of my reach it's not even funny. I have another app I'd like to create too that is related to horses but ah well. One step at a time.

  9. #9
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The points must be defined as data type Number on the table with Double in the Field Size. Not sure what you mean by "sub-table" as this is not a recognized object name in Access.

    Yes, never use spaces (or any special characters except for underscore) in object or field names. Add a suffix or prefix to all objects - some people use tbl...., qry..., others put it at the end of the name, some use _T or _Q, etc. This is to differentiate easily between when your data is coming from a table and when it is coming from a query.

  10. #10
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Would you still suggest to remove this total and run as a query or would that one be ok to leave in the table and therefore the form?
    I would. If this were a financial db product I was evaluating, I'd drop your db off the list as soon as I saw a calculation was being stored for no good reason. You might be OK 99% of the time, and perhaps the form calculation will never bomb out because there's no reason for that to happen (e.g. no code where an error can cause deviation from the expected execution thus the calculation will always be performed). The effect in your case may be no more than damage to your reputation but I don't know what the effect of an incorrect calculation would be. Maybe someone's horse loses a competition, or doesn't get to compete, or doesn't achieve a status based on an incorrect calculation? Given what I've seen, I'd copy your existing form, based on a query without calculations and explore the design whereby you place a textbox in the form footer for each field and total each separately. Then have a calculated textbox off to the right to give you the grand total. Or you can do a total across each record using one calculated textbox by summing each field in a record. To do this in a form might be less useful than simply calculating in a report. Printing forms isn't a great idea, so is the form calculation enough, or is it even required if you have a report that you can distribute?
    In regards to naming of queries, should I use underscore for all spacing?
    I'm too lazy to use the underscore. I haven't trained myself to be able to type the key combination without taking my eye off the screen so I don't use it. Since I use Camel Case, I don't think I need it either. Surely you will ask what camel case is, so an example:
    frmMyFormName - get it?
    I would never have spaces or special characters as has already been pointed out, plus I'd condense as much as practical. In-Hand Points would be InHandPts. When you have to write code while using object names like yours, having to wrap things in brackets is a pain [In-Hand Points]. Your labels can be anything that clarifies what a control is for, plus table fields can have captions containing anything without affecting code.
    Will post some links next - my browser has lost functionality at the moment. Can't get at my bookmarks.
    EDIT
    One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
    What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
    About Auto Numbers
    - http://www.utteraccess.com/wiki/Autonumbers
    - http://access.mvps.org/access/general/gen0025.htm

    The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
    Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
    About calculated table fields - http://allenbrowne.com/casu-14.html
    Last edited by Micron; 08-16-2017 at 01:34 PM. Reason: added links

  11. #11
    Savannah is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2017
    Location
    Australia
    Posts
    14
    Thank you very much for the explanations. I'll sit down and revamp the queries a little to make it more user friendly.

    Oh, sorry, I had my terminology wrong! So the Total is sitting in a SUB-FORM not a sub table. That might make more sense to you perhaps?

    You'll see here the different tables/queries and forms I currently have.

    This is the main Form with the sub-form containing the total column and opens automatically upon opening the document. The idea is, if I leave, someone else can just use the form to enter and retrieve data using the buttons along the top.

    Does that change the issue with the totals at all Micron, seeing as they are in a form, or should I still remove it?

    Click image for larger version. 

Name:	Query2.jpg 
Views:	19 
Size:	96.9 KB 
ID:	29981
    Last edited by Savannah; 08-17-2017 at 12:58 AM.

  12. #12
    Savannah is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2017
    Location
    Australia
    Posts
    14
    Thank you Aytee Changing the field from decimal to number has corrected my calculation issue

  13. #13
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I figured you meant sub form. Since it could also be based on a query or table it's not possible to know if a total field is in a table or is a calculated field in a table unless clues or concrete information is provided.
    Just posting a quick reply on my phone. Will take a closer look tomorrow.

  14. #14
    Savannah is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2017
    Location
    Australia
    Posts
    14
    Ok,so that totals column in the table in design view is a calculated field. (If I'm following what you're looking for). All actual figures are only entered via the form.

    Attachment 29985

  15. #15
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Couldn't discern the picture on my phone, but after viewing, I can't tell if
    The idea is, if I leave, someone else can just use the form to enter and retrieve data using the buttons along the top.
    is possible or not. Can't you test to answer that question? As for the attachment, it's not valid somehow - won't open.
    Ok,so that totals column in the table in design view is a calculated field.
    Again, I would not have calculated fields in a table.
    Maybe read this http://allenbrowne.com/casu-14.html
    Suggest you visit that site often - I do.

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

Similar Threads

  1. Replies: 1
    Last Post: 05-17-2017, 01:21 AM
  2. Query Question
    By gtimmies in forum Queries
    Replies: 1
    Last Post: 05-07-2017, 09:26 PM
  3. Query Question
    By sanderson in forum Access
    Replies: 10
    Last Post: 03-18-2016, 12:02 PM
  4. Replies: 1
    Last Post: 03-05-2016, 05:22 AM
  5. Query question
    By jerrypeco in forum Queries
    Replies: 20
    Last Post: 06-06-2013, 10:27 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