14 Replies Latest reply on May 31, 2016 12:57 PM by crazycoder

    Using ExecuteSQL to sum a field with multiple criteria

    crazycoder

      I am very new to Filemaker. I am pretty well versed in VBA programming and have a little smattering of knowledge of SQL. I am working on a database redesign for my local fire department, but I have hit a snag. The fire department currently records all overtime in an access database. When a new record is opened for the Captain's to enter the data, a subform opens that shows which firefighters have the least amount of overtime hours to their name. The Captains base their choice for assignment on this list. The hours are calculated by several criteria:

                               The hours are only summed IF -- the overtime was accepted OR rejected by the firefighter (if the firefighter is not reached, etc., it is recorded as "attempted" and those are not counted toward the sum)

                                                                            AND -- The overtime has the same shift type as the new overtime.

      For example, if the Captain on duty opens his overtime program and chooses to enter a "Day" shift overtime, the subform only shows the sum of hours for each firefighter for overtimes that are "Accepted" or "Rejected" AND have a shift of "Day" or "Detail". If he chooses to enter a "Night" shift overtime, it shows the sum for "Accepted" or "Rejected" AND have a shift of "Night". Below is a simplified table to represent the gist of what we are working with.

      Overtime FieldPossible Values
      EmployeeIDThe firefighter's ID
      DateDate of the overtime
      Status"Accepted", "Rejected", or "Attempted"
      Shift"Detail", "Day", "Night", or "Box"
      HoursAmount of time for the individual overtime record

      However, I am having a difficult time getting Filemaker to sum based on both criteria (Status and Shift). I have finally decided to try the ExecuteSQL function in the calculation field, but every select statement I use either returns a blank field or a "?". I am attaching a simplified version of my database with made-up data. Here are some need to know's for those that are willing to help me:

      1. When "Detail" or "Day" is chosen for shift, it needs to sum for both detail and day overtimes. "Night" only sums night, and "Box" only sums box.

      2. The status is set by the admin in a layout that I did not include in this copy. The Admin decides which status options are summed. It is set for "Accepted" and "Rejected" for now through a field in my tblVariable.

      3. The total hours in the employee table is where I would like sum all of the overtime for an employee. You will find my current SQL statement there which is not working.

      4. I have set it to show the hours for a "Detail" on open, until the user chooses the shift for the new record. This is set in the Database.Open script.

       

      Thank you in advance for any help you can give me. I am open to ideas if there is a simpler way to do this.