Sum a column based on two criteria.

[Degs29]

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!

[Degs29]

Never mind, I figured it out. Three separate queries with a WHERE clause. Was making that way too difficult in my head.

[June7]

Maybe one query and one report and apply filter criteria to the report when it opens.

DoCmd.OpenReport "reportname", , , "SupplierID=" & Me.comboboxname

Or no filter criteria and use report Grouping & Sorting features with aggregate calcs in footer section. Set SupplierID as group and force new page after each.

[burrina]

Please mark this thread as solved.

Thanks,