Results 1 to 4 of 4
  1. #1
    Divindunk is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jul 2014
    Location
    Birmingham England
    Posts
    11

    How do I sum multiple fields in a query based on multiple criteria results in other fields

    Hi All

    I have a problem which is quite complex and I hope someone can help me please? I have data which is collected from our production facility which includes Machine Down Time and Reason for Down Time for each hour of a 12 hour shift there are two sets of these fields to allow two reason within the same hour. The Down Time is a number of minutes and the Down Time Reasons are by code which is recorded as number in the field. The table into which this data is saved has a total of 119 fields as it also contains fields for Reject Parts, Reject Reasons (two sets for each hour as with the Down Time) part, machine and operator information. I need to have totals of Down Time for each Reason or group of Reasons for each job record. Unfortunately the only way I could find to achieve the results I have is using the following formula



    Group A Hr 1: IIf([Down Time Reason Hr1]=13,[Down Time Hr1],IIf([Down Time Reason Hr1]=31,[Down Time Hr1],IIf([Down Time Reason Hr1]=30,[Down Time Hr1],IIf([Down Time Reason Hr1]=28,[Down Time Hr1],IIf([Down Time Reason Hr1]=32,[Down Time Hr1],IIf([Down Time Reason Hr1]=29,[Down Time Hr1],IIf([Down Time Reason Hr1]=27,[Down Time Hr1],0)))))))

    If I try and include both sets of Down Time and Reason fields it results in the "Too many fields defined" error message, so I have done is produce two queries which give me totals for the first set of Down Time minutes and Reasons in query one and the second set of data in query two.

    This formula gives me the result I need and calculates if the Down Time in Hr1 falls within a group (Group A) of Down Time Reasons (the numbers 13, 31, 30, 28, 32, 29 and 27 within each of the nested if statements is a code for a different down time reason). There a Total Of 34 Down Time Reason Codes, but these are split into 10 groups (A to J) the groups vary in their content from 1 reason only, up to 7. The formula works but it has to be repeated for each group and each Hour and then Totalled for each or the 10 Groups resulting in 175 fields in each query. The queries both have Between Dates criteria set.

    When I try to combine the results of the 2 queries it, again, results in "Too many fields defined" error message even though I am only placing the totals of the calculations in the query.

    Can any suggest a better way of achieving my goal? I had thought that maybe I could use and "If/or" formula or sum all 12 hr Down Time fields falling into a Down Time Reason group to reduce the number of calculation fields but I have tried every permutation I can think of but without success.

    I can use code and am happy to do so if necessary but I am not an expert (I usually use code which is available online and mostly in excel only).

    I hope that my description and question makes sense to someone and look forward to your advise and solutions (if there are any!).

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You would not have the IIf. You'd make a query to get the elapse time from each record:
    dateDiff("h",[startTime],[endTime])

    then make a query using the query above to sum the down time.

  3. #3
    Divindunk is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jul 2014
    Location
    Birmingham England
    Posts
    11
    Unfortunately the data is collected manually on a paper form and then transferred to the database during the next shift so there is no start or finish time, the time lost is logged in minutes for each hour of production and the reason for the loss is recorded in the same hour and there are two fields for lost time and loss reason to allow for two losses in each production hour of differing reasons.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    From what I can gather, I think you have reached the point where you're beginning to suffer from poor design. If all 119 fields contain attributes for one entity and you know what normalization is then I'm wrong about that but it sounds like Reasons should be in their own table as rows, not columns in some huge table. Anyway, did you try something like
    SELECT [Downtime Reason], [Downtime Code] FROM tblMyHugeTable WHERE [Downtime Code] IN(13, 31, 30, 28, 32, 29, 27) WHERE...?
    Don't know what you'd do with that; Sum, Count or a Totals query based on the results...
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 3
    Last Post: 07-26-2016, 02:12 PM
  2. Replies: 3
    Last Post: 10-08-2014, 10:28 AM
  3. Query multiple fields for the same criteria
    By labratKY in forum Queries
    Replies: 3
    Last Post: 08-07-2014, 02:37 PM
  4. Replies: 5
    Last Post: 08-07-2012, 10:12 PM
  5. Replies: 12
    Last Post: 05-07-2012, 12:41 PM

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