Counting unique values based on multiple criteria

[davey]

Hi there, I am trying to make a report more efficient in its output and am struggling to get the correct formula for it. In the below example, you can see that I am calculating the number of batches created in a shift for each of our production lines, this is done in my query by counting the batch IDs. I have then brought in a field with the number 1 in it so that I can calculate the average number of batches produced by production line on a given shift and day. Clearly this is not the best approach but I am a stumped as to how to fix this. In my second screenshot I have given what my desired output will be - the number of batches by date and shift will be summed and then there is a count given of the number of unit produciton lines, rather than having to list them individually.



Thanks in advance for your help.

[isladogs]

Do an aggregate query with 4 fields
Group by Shift date & Shift.
Add Batches and change Group By to Sum
Add Production Line & change Group By to Count