Results 1 to 5 of 5
  1. #1
    cmumphr is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    4

    Query Not Summing Correctly

    I am attempting to sum a column on MS Access but when I do the value (hours) change which then affects the sum of hours column. What am I doing wrong?



    When I activate the Totals button on the home ribbon, I do not see the Sum option. I enter the design view and activate the the totals button there. After doing so, I go to the hours column while still in the design view and change the group by to Sum, then i run the query. I return to the the database view and notice the values are different/wrong. When I go ahead and hit the drop-down button for the totals the Sum option appears then. But it is no use if the sum will be wrong. Any help would be great!!!! Please the screenshots below. I have zipped the actual database for anyone wanting to take a deeper look at what I've done wrong. I am very much new to Access!

    Click image for larger version. 

Name:	Capture1.PNG 
Views:	21 
Size:	48.0 KB 
ID:	36043Click image for larger version. 

Name:	capture2.png 
Views:	21 
Size:	60.6 KB 
ID:	36044Click image for larger version. 

Name:	capture3.png 
Views:	21 
Size:	18.0 KB 
ID:	36045Click image for larger version. 

Name:	capture4.png 
Views:	21 
Size:	81.8 KB 
ID:	36046

    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Hours field in tblClassInfo is defined as text. It needs to be number type.

    You seem to be duplicating data into tblClassInfo instead of saving keys so there is no need to join tables to retrieve related data. This is somewhat contrary to nature of relational database. It will use up more storage space.

    I question the need for tblObserver, just use tblInstructor as source for observers. Can add another field to flag instructor as having observer role, a Yes/No field can serve.

    Don't have a Log query for each instructor, have one query and dynamically apply filter criteria when needed.

    In tblClassInfo, should Semiar be spelled Seminar?

    Kudos for not using spaces nor punctuation/special characters (underscore only exception) in object names, need to carry through for field names - remove spaces.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    cmumphr is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    4
    Thanks for the advice. I have implemented some of it into the database. My question is when I hit the Totals button on the query window and run the query, it changes the hours? Take a look at the first and last screenshots. The first screenshots displays the correct amount of hours where as the last screenshot after i hitting the totals button on the query window changes the hours.

    Thanks!

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Okay, you have built LOOKUP fields in table. I NEVER do this and did not think to check for this before.

    The actual value in the Hours field is the ID from Modules table, not the Hours. So you are summing the ID, not hours. In fact, you are saving ID from Modules in two fields. This is redundant. This also means my earlier comment about not having to join tables is incorrect.

    This is one of the reasons to NOT build LOOKUPS in table - they cause confusion.

    Now you will have to join tables in query in order to retrieve the actual Hours to sum. Remove Hours field from tblClassInfo and do the join on Module and ID fields. Module field needs to be changed to number type.

    Again, build one Log query and apply filter for instructor.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    cmumphr is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    4
    Okay. I think I may got what you're saying. Sorry if im being frustrating as I am new to Access. I started a new database and taken into account your advice. I have uploaded the new database. Please let me know what you think. The form I created, i want the tables to feed the form that way when the user inputs the data, there is less chance for error. How do i go about creating a drop-down on the forms that will only display the information from the corresponding tables? Are my tables also setup correctly so that I can start summing the hours correctly once i start inputting data?

    thanks for all your help!
    Attached Files Attached Files

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

Similar Threads

  1. grouping and summing query
    By ShostyFan in forum Queries
    Replies: 5
    Last Post: 06-29-2015, 07:44 PM
  2. Summing Multiple Queries & Summing Time
    By WEJ in forum Queries
    Replies: 2
    Last Post: 10-04-2013, 04:46 PM
  3. Replies: 15
    Last Post: 12-10-2012, 06:37 PM
  4. A summing query?
    By russweb in forum Queries
    Replies: 8
    Last Post: 03-26-2012, 06:57 PM
  5. summing in a query
    By nparrillo in forum Queries
    Replies: 1
    Last Post: 04-11-2011, 10:37 AM

Tags for this Thread

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