Results 1 to 10 of 10
  1. #1
    kelkan is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Dec 2012
    Posts
    26

    Sum values from multiple related records on a form


    I am fairly new to Access and have no formal training on this program, just a lot of trial and error. I have a database with 4 tables. They are tblEmployees, tblCourses, tblDates, and tblTraining. The key for each was an autonumber that is EMPLOYEEID, COURSEID, DATEID, and TRAININGID. tblEmployee lists pertinent information regarding an employee (name, serial #, shift, etc). tblCourse lists all courses that are available for an employee (course name, #hours, required attendees, type of training, etc). tblDates lists all available class dates and times for the courses in tblCourses and has a lookup field for COURSEID and COURSETITLE from tblCourses. tblTraining lists all the training scheduled for and completed by an employee and has a lookup/relationship with tblEmployees for EMPLOYEEID and EMPLOYEENAME. It also has a lookup/relationship field with tblCourses for COURSEID and COURSETITLE. Finally, it has a lookup/relationship with tblDates with lookup field/relationship with DATEID and CLASSDATE. I have successfully created a form where the training can be added to an employee (frmTrainingUpdated) and it has a subform (frmTraining) with all classes for that employee in a multilist at the bottom (from a query of tblTraining). The subform also has a field for whether the training was completed and then the hours are credited to the employee (txtCredit). The problem comes when I try to add all of the credited hours for a single employee in a separate field. I am trying to create a field somewhere on the form that will total all of the hours for all of the completed classes for the one employee on the form. I have tried to list the data for the textbox as =DSUM([txtCredit], tblTraining, WHERE (EMPLOYEE=Me.Employee)) and only get an error message displayed in the textbox. I tried to do a totals SUM on the query and it only gives me the individual hours for each class on the same line for that record. Any suggestions?

  2. #2
    alcapps is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    292
    = dsum("[txtcredit]","tbltraining","employeeid = " & me.employeeid)

    remember leave out the word where in dsum.
    This assumes that txtcredit is your field for class hours.

    Hope this helps

  3. #3
    kelkan is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Dec 2012
    Posts
    26
    I still get #Name? in the text box.

  4. #4
    alcapps is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    292
    Are you able post your database.
    You can try running that in the immediate window.

    Try
    ?dsum("txtcredit","tbltraining","employeeid = 1")
    where 1 is a valid employee number
    this is just to determine the issue

  5. #5
    kelkan is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Dec 2012
    Posts
    26

    database attached

    Database1 - Copy.zip

    I hope this is correct for attaching the database.

  6. #6
    alcapps is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    292
    ?dsum("classhours", "tblTraining", "classcompleted = 'Yes' and employee = 1")
    24

    I was able to see that we had the wrong fieldname and that you wanted only completed classes.

  7. #7
    alcapps is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    292
    I think you can do this in a combined query..
    I created a new query called qryTraningSumHours
    SELECT tblTraining.EMPLOYEE, Sum(tblTraining.CLASSHOURS) AS TotalCompHours
    FROM tblTraining
    WHERE (((tblTraining.CLASSCOMPLETED)="YES"))
    GROUP BY tblTraining.EMPLOYEE;



    changed qryEmployeeTraningList to...
    SELECT tblTraining.EMPLOYEE, tblTraining.COURSETITLE, tblTraining.CLASSDATE, tblTraining.CLASSCOMPLETED, tblTraining.CLASSHOURS, qryTrainingSumHours.TotalCompHours
    FROM tblTraining LEFT JOIN qryTrainingSumHours ON tblTraining.EMPLOYEE = qryTrainingSumHours.EMPLOYEE
    ORDER BY tblTraining.EMPLOYEE;

    this should work for you..

  8. #8
    kelkan is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Dec 2012
    Posts
    26
    this sounds stupid..but where do I find the database with the changes you made so I can see what you did?

  9. #9
    alcapps is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    292
    no need for a new database. These are the changes I made.
    add a new query named qryTrainingSumHours paste in the query. save it.

    open up the old qryEmployeeTrainingList to the query I wrote for that and you should be good to go..

  10. #10
    alcapps is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    292
    Database1 - Copyfix2.zip
    Not sure this will work. I don't have zip and the database file is more than 500mb. That is why i didn't send you the file earlier.

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

Similar Threads

  1. Replies: 10
    Last Post: 10-10-2012, 11:15 PM
  2. Replies: 1
    Last Post: 03-13-2012, 06:11 PM
  3. Lookup to show related values
    By mjhopler in forum Access
    Replies: 1
    Last Post: 08-19-2011, 01:35 PM
  4. query won't display records with no related records
    By James Brazill in forum Queries
    Replies: 1
    Last Post: 06-28-2011, 02:10 AM
  5. Replies: 1
    Last Post: 04-02-2011, 11:55 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