Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    7

    Unanswered: "Sum" over groups...

    I'm having trouble figuring out how to get a "sum" over groupings.

    Bascially from a MaterialAssign table, material is assigned to a machine.
    A second table, MaterialUsage, has the same material, along with gallons, month and year. (This is were the end-user inputs to) A third table, RunHours, takes in the machine's id number, run hours, month and year. My objective is to show the percentage of production of the materials used on a machine, but it's complicated by the fact that not all the machines use the all the material.
    I can easily get a report showing the machine with a breakdown of the material it used, it's runhours, month and year.
    I just have been struggling with figuring out how to get the total material only used by certain machines so then I can figure by the percentage of run hours to total hours run, what the likely percentage of production material is used on a specific machine.

    Any help or suggestion are welcome... I done Db work before, but I'm new to MS Access 2000.
    Thanks,
    ron

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    cannot help without seeing your table layouts and how they join

    and please explain "likely percentage"


    rudy
    http://r937.com/

  3. #3
    Join Date
    Jul 2003
    Posts
    7
    I'm at home since it's easier to work in solitude...
    Consequently I had to create a facsimile of the work problem at home since the work MS Access is over 30 megs.

    If it's more convenient here's a link to the 6meg demo of the concept;
    InventoryControl1.mdb

    A Material Assign table was created in which the materials are assigned to a machine, or in the demo case, a person. Beer, wine and soda are assigned to Carol, soda to Jessica and beer, wine and whiskey to ron.

    I have a demo table of MaterialUsage which has a column of material; beer, wine, whiskey and soda, with another column Gals, Month column and Year column. The end-user would use a form to input the monthly consumption of the materials.

    The last table of concern has a column of the machine's id, or this case, the person's name, a column of Hours of operation, Month and Year. Basically, jessica runs 250 hours, carol at 500 hours and ron at 750 hours per month. Another form would allowed the end-user to input hours of operation per month.

    The attempt is to get a report grouped by the person's name (done), showing what material they consumed (done), the hours of operation and relative percentage to the three total hours by all three parties. The problem I have begins in the Percent of Production, that is; the relation of hours that Carol and Jess have to each other, and the fact that they are the only two that consume soda, they should have percentages of production of 66% and 33%. Likewise, since ROn is the only one that consumes whiskey, that percentage of production should always be 100%. no matter what the actual gallons used are. That's where I can't get to. How to get MS Access to give me that percentage back correctly. I'm hoping that I'm to close to the trees to see the forest....

    Thanks in advanced...
    ron

  4. #4
    Join Date
    Jul 2003
    Posts
    7
    I have a possible way to do this, by using a subquery, but now I'm having trouble getting a query parameter to be used by the subquery.

    ron


    SELECT DISTINCT MaterialAssign.Material, MaterialAssign.PressAssign_id, Sum(RunHours.Hours) AS RHHours, RunHours.Month, RunHours.Year, MaterialUsage.Lbs, MaterialUsage.Gals, Sum(MaterialUsage.Gals) AS RSGals,

    (SELECT DISTINCT Sum(RunHours.Hours)
    FROM MaterialUsage INNER JOIN (MaterialAssign INNER JOIN RunHours ON MaterialAssign.PressAssign_id = RunHours.RunHours_id) ON (MaterialUsage.Material = MaterialAssign.Material) AND (MaterialUsage.Month = RunHours.Month) AND (MaterialUsage.Year = RunHours.Year)
    WHERE MaterialAssign.Material= 'MaterialAssign.Material'
    GROUP BY MaterialAssign.Material, RunHours.Month, RunHours.Year
    HAVING (((MaterialAssign.Material)=[MaterialAssign.Material]) AND ((RunHours.Month)=[Select Month]) AND ((RunHours.Year)="03"))) AS MaterialTotalHours

    FROM MaterialUsage INNER JOIN ((MaterialAssign INNER JOIN Material ON MaterialAssign.Material=Material.Material) INNER JOIN RunHours ON MaterialAssign.PressAssign_id=RunHours.RunHours_id ) ON (MaterialUsage.Material=MaterialAssign.Material) AND (MaterialUsage.Month=RunHours.Month) AND (MaterialUsage.Year=RunHours.Year)
    GROUP BY MaterialAssign.Material, MaterialAssign.PressAssign_id, RunHours.Month, RunHours.Year, MaterialUsage.Lbs, MaterialUsage.Gals
    HAVING (((RunHours.Month)=[Select Month such as May]) AND ((RunHours.Year)="03"));

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, your mdb file is no good to me, all i have is 97

    the query you posted here has several problems

    the subquery can possibly return more than one row, which would cause the outer query to crash

    the conditions in your HAVING clauses should be in either WHERE clauses or ON conditions

    DISTINCT is usually not required with a GROUP BY

    let me re-write your query to make it more easy to read

    i do not pretend to understand what you're trying to do --

    Code:
    SELECT DISTINCT 
           MaterialAssign.Material
         , MaterialAssign.PressAssign_id
         , Sum(RunHours.Hours) AS RHHours
         , RunHours.Month
         , RunHours.Year
         , MaterialUsage.Lbs
         , MaterialUsage.Gals
         , Sum(MaterialUsage.Gals) AS RSGals
         , ( SELECT DISTINCT 
                    Sum(RunHours.Hours) 
               FROM MaterialUsage
             INNER
               JOIN (
                    MaterialAssign
             INNER
               JOIN RunHours 
                 ON MaterialAssign.PressAssign_id 
                  = RunHours.RunHours_id
                    ) 
                 ON MaterialUsage.Material 
                  = MaterialAssign.Material 
                AND MaterialUsage.Month 
                  = RunHours.Month 
                AND MaterialUsage.Year 
                  = RunHours.Year 
              WHERE MaterialAssign.Material
                  = 'MaterialAssign.Material'
             GROUP
                 BY MaterialAssign.Material
                  , RunHours.Month
                  , RunHours.Year 
             HAVING MaterialAssign.Material
                  = [MaterialAssign.Material] 
                AND RunHours.Month
                  = [Select Month] 
                AND RunHours.Year="03"
           ) AS MaterialTotalHours
      FROM MaterialUsage
    INNER
      JOIN (
           (
           MaterialAssign
    INNER
      JOIN Material 
        ON MaterialAssign.Material
         = Material.Material
           )
    INNER
      JOIN RunHours 
        ON MaterialAssign.PressAssign_id
         = RunHours.RunHours_id
           )
        ON MaterialUsage.Material
         = MaterialAssign.Material
       AND MaterialUsage.Month
         = RunHours.Month
       AND MaterialUsage.Year
         = RunHours.Year
    GROUP 
        BY MaterialAssign.Material
         , MaterialAssign.PressAssign_id
         , RunHours.Month
         , RunHours.Year
         , MaterialUsage.Lbs
         , MaterialUsage.Gals
    HAVING RunHours.Month
         = [Select Month such as May]
       AND RunHours.Year ="03"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •