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!).