I'll try to give a little back story, so this is understandable. We order some sheet material from a supplier and send it out to one of three external laser cutter companies, who then ship the cut components to us. What I'd like to do is track the amount of sheet stock each laser cutter has. There's approximately ten different sizes of sheet that we currently use, but there will be more in the future.
So what I've done so far is make a form [SheetMetalForm] where I pick one of the three laser cutters from a list [SheetMetalForm.Supplier], pick the sheet size from a list [SheetMetalForm.Sheet], then enter a quantity [SheetMetalForm.Quantity] that I have shipped there (positive number for sending sheet there and a negative number for consuming sheets). This info is recorded in a table [SheetMetal].
What I'd like to be able to do is create three separate reports: SheetMetal_Supplier1, SheetMetal_Supplier2, SheetMetal_Supplier3. These reports would show each sheet size applicable to that laser cutter and the quantities they have remaining in stock. What I can't figure out how to do is sum the quantities of sheet stock at one supplier without adding in the same size sheets located at the other three suppliers!
Do I have to create three separate queries with an IF statement and if so, how would that look? Or is there another method to do this?
Thanks!