Results 1 to 4 of 4
  1. #1
    Degs29 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    May 2013

    Sum a column based on two criteria.

    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?


  2. #2
    Degs29 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    May 2013
    Never mind, I figured it out. Three separate queries with a WHERE clause. Was making that way too difficult in my head.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    The Great Land
    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.
    How to attach file: To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    burrina's Avatar
    burrina is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2012
    Please mark this thread as solved.


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

Similar Threads

  1. Replies: 5
    Last Post: 05-01-2013, 11:39 AM
  2. Add additional Column based on a column value
    By deepanadhi in forum Queries
    Replies: 7
    Last Post: 05-31-2012, 02:02 AM
  3. Replies: 1
    Last Post: 03-14-2011, 10:04 AM
  4. Replies: 1
    Last Post: 04-15-2010, 02:07 AM
  5. inserting values in column based another column
    By wasim_sono in forum Database Design
    Replies: 1
    Last Post: 06-27-2006, 05:23 AM

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 - Senior Forums