Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2010
    Posts
    14

    Unanswered: Help creating a DSum formula. My DB is available to look at.

    I thought it might be easier to provide you a copy of my DB so you can see what I have going on. I'm a noobie so I'm sure that things are not as optimized as they could be.

    When you open the DB the main form will open up. On the left side is where the operator enters in their records. The charts on the right side are on a subform and will update themselves based on the info in the records being entered.
    There are line charts for "waste%" and "productivity"and on each chart is a "totals box" either in red or green boxes (data from DLookup). There are tabs for current "Weekly, Monthly, Quarterly, Yearly, Operator Waste%, and Operator Productivity" They all currently show the same data as I removed most of the records to trim down the file size.

    Currently the figures all reflect "averages" and I want them to now show "actuals". I won't attach the code used to get the averages as you will readily see the code in the properties section. Below are how the numbers are calculated in the form on the left side. I'm just not sure how to get the charts to show actuals as well.

    Waste =[LF Run]-[LF Produced]
    Waste% =[Waste]/[LF Run]
    Productivity =[LF Produced]/33.75 (hours--I will be changing this value soon)

    If I can see how to make the actuals show on the weekly tab for waste% and productivity as well as the totals I should be able to duplicate that code onto the other tabs. That is, if it's that straight forward.

    I greatly appreciate you taking a look at this for me.

  2. #2
    Join Date
    Aug 2010
    Posts
    14

    Copy of DB attached

    Thought it would help you gurus to see what I'm looking for. If someone could point me in the right driection...

  3. #3
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    I'll have a look at your database and see if I can help. Expect some additional questions, though. Some remarks in the mean time:

    1. You should post a zipped file of your database here, as an attachment. Many people don't like downloading unknown files from an external site.

    2. The database you uploaded is in .accdb format, which means that the possibility to open it are limited to those who use Access 2007 or 2010. There's nothing you can do to change that but you should specify which version of Access was used: as far as I know, many people here still work with Access 2000 or 2003.

    3. You should post the code you use and precisely explain what you mean with "averages" and "actuals", for reasons exposed above among others, and also because not everybody wants to spend time exploring your database just to understand what you mean.
    Have a nice day!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    The database you posted is unusable. When opening it an error pops up with a message saying that "Z:\Dropbox\_All Programming & Development\Abacus Dev\Avacus_be.accdb is not a valid path". Moreover a recurrent error message (every second or so) pops up too:
    Error Line: 0
    Error (0) Reserved error
    Form_Timer
    Form_frm_productionNumbers
    Have a nice day!

  5. #5
    Join Date
    Aug 2010
    Posts
    14

    Fixed and attached database

    Sinndho;

    Thank you for offering to take a look at this for me. Thank you also for your comments they are very helpful. I have created one whole database instead of the split DB you had received the front end for. It is attached to the post here and is in Access v2010 format.

    The database tracks manufacturing of several different products. The worker enters in a record for the product he is making every two hours and all the queries look to only the "End-Days" and "End-Nights" records as those are the most important records. When you open the DB, you'll be on the "Production Data Entry Form". On the left side of the form are data fields that the users fills in. Some of the fields, namely the "Waste, Waste% and Productivity" fields are auto calculated.

    On the right side of the form are charts in a subform/tabbed control. There are tabs for "Weekly, Monthly, Quarterly, Yearly, Operator Waste% and Operator Productivity".

    Currently only averages are shown and charted. The code on the "Weekly" tab for waste% is:
    Code:
    SELECT [Part Number],Avg([Waste%]) AS [-Waste%] FROM [qryWeeklyWastePercentLineChartOnForm]   GROUP BY [Part Number];
    The code for the Weekly Productivity on the same tab is:
    Code:
    SELECT [Part Number],Avg([Productivity]) AS [-Productivity] FROM [qryWeeklyProductivityLineChartOnForm]   GROUP BY [Part Number];
    The other tabs have similar query code, the differences being that the query behind the chart is named differently to reflect "Monthly, Quarterly, Yearly" and so on.

    What I'm trying to do is to get the numbers to be "Actual" numbers and not "Averages" on the charts as they are now.

    Fields used are:
    LF Run = How much total product came out of the machine?
    LF Produced = How much product was good product?
    Waste = What is the difference between how much was run and how much was good product?
    Waste% = Dividing "Waste" number by "LF Run" number
    Productivity = LF Produced / 33.75 (Set amount of man hours per every two hours)


    The math is as follows:
    LF Run is entered by worker
    LF Produced is entered by worker
    Waste = LF Run-LF Produced is auto calculated
    Waste% = Waste / LF Run
    Productivity = LF Produced / 33.75


    So to do that "longhand" I would look at the query, which could contain dozens of records, and find all the records that are for the same product and are either "End-Days" or "End-Nights" records. Let's say that product is called "LL100". I would then sum up the waste numbers for the LL100 product, and then take that total and divide it by the sum of the LL100 product's LF Run numbers to arrive at a figure called "Waste%". To get the "Productivity" numbers, I would add up all the "End-Days" and "End-Nights" "LF Produced" numbers for "LL100" and divide that by 33.75. I would do that for each product run so that the chart would show totals for each Part Number, that is, they are grouped by Part Number.

    I hope that I made some sense here with my description and what I'm trying to do.
    Attached Files Attached Files
    Last edited by Repent; 10-03-11 at 13:38.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Sorry for the delay: The 2010 version of Access I have at home runs on a virtual machine and this one now crashes regularly (every 5 to 10 min.) since it was recently updated.

    Anyway, I succeeded in extracting the tables from your database and importing them into an Access 2003 database where I could experiment with the queries you need.

    Just a piece of advice: try avoiding the use of non-alphanumeric characters in the names of your objects (queries, columns, aliases). The only exception being the underscore character, which should be used instead of a space. The same is also true for reserved words (Date, Name, Type, etc.).

    After examining the queries you already have in your database, I think that most of the problems comes from the confusion you seem to make between the WHERE clause and the HAVING clause in a query with a GROUP BY clause.

    As far as I can understand what results you try to obtain, you do not need a HAVING clause: a WHERE clause is enough and it simplifies the SQL statement of the query. As an example, I've refactored the QryWeeklyWastePercentLineChartOnForm like this (see the attached screenshots):
    Code:
    SELECT tblProductionNumbers.ID, 
           tblProductionNumbers.ProductionDate, 
           tblProductionHours.Time, 
           tblProductList.[Part Number], 
           tblProductionNumbers.LineID, 
           tblProductionNumbers.OperatorID, 
           tblProductionNumbers.TailOffID, 
           tblProductionNumbers.[LF Run], 
           tblProductionNumbers.[LF Produced], 
           [LF Run]-[LF Produced] AS Waste, 
           [Waste]/[LF Run] AS WastePercent, 
           [LF Produced]/33.75 AS Productivity
        FROM tblProductionHours 
           INNER JOIN (tblProductionEmployeesTailoff 
               INNER JOIN (tblProductionEmployeesOperator 
                   INNER JOIN (tblProductList 
                       INNER JOIN tblProductionNumbers 
                           ON tblProductList.ID = tblProductionNumbers.ProductID) 
                   ON tblProductionEmployeesOperator.ID = tblProductionNumbers.OperatorID) 
               ON tblProductionEmployeesTailoff.ID = tblProductionNumbers.TailOffID) 
           ON tblProductionHours.ID = tblProductionNumbers.TimeID
        WHERE ((tblProductionHours.Time In ("END-Days","End-Nights")) 
           AND (DatePart("ww",[ProductionDate])=35) 
           AND (Year([ProductionDate])=Year(Date())))
        GROUP BY tblProductionNumbers.ID, 
                 tblProductionNumbers.ProductionDate, 
                 tblProductionHours.Time, 
                 tblProductList.[Part Number], 
                 tblProductionNumbers.LineID, 
                 tblProductionNumbers.OperatorID, 
                 tblProductionNumbers.TailOffID, 
                 tblProductionNumbers.[LF Run], 
                 tblProductionNumbers.[LF Produced], 
                 [LF Run]-[LF Produced];
    More generally it's easier to use a subquery whith a GROUP BY clause, then perform the computations outside of it. As an example, I've written the query you describe in:
    The math is as follows:
    LF Run is entered by worker
    LF Produced is entered by worker
    Waste = LF Run-LF Produced is auto calculated
    Waste% = Waste / LF Run
    Productivity = LF Produced / 33.75
    Here is it:
    Code:
    SELECT g.Product, g.Total_LF_Run, g.Total_LF_Produced, g.[Total_LF_Run]-g.[Total_LF_Produced] AS Waste, (g.[Total_LF_Run]-g.[Total_LF_Produced])/g.[Total_LF_Run] AS Percent_Waste, [g].[Total_LF_Produced]/33.75 AS Productivity
    FROM [SELECT tblProductList.ID, 
                        tblProductList.Product, 
                        Sum(tblProductionNumbers.[LF Run]) AS [Total_LF_Run], 
                        Sum(tblProductionNumbers.[LF Produced]) AS [Total_LF_Produced]
                    FROM (tblProductList 
                        INNER JOIN tblProductionNumbers 
                            ON tblProductList.ID = tblProductionNumbers.ProductID) 
                        INNER JOIN tblProductionHours 
                            ON tblProductionNumbers.TimeID = tblProductionHours.ID
                    WHERE (tblProductionHours.Time In ("END-Days","End-Nights"))
                    GROUP BY tblProductList.ID, tblProductList.Product
             ]. AS g;
    I don't understand how you compute the productivity, however I did not checked the validity of the formulas: I assert that you know what you're doing.

    From there (and if I'm right) you should not encounter too many problems when building the queries you need.
    Attached Thumbnails Attached Thumbnails Query2.jpg   qryWeeklyWastePercentLineChartOnForm_DataView.jpg   qryWeeklyWastePercentLineChartOnForm_DesignView.jpg  
    Have a nice day!

  7. #7
    Join Date
    Aug 2010
    Posts
    14

    But How to graph now?

    Sinndho;

    Thank you very much for the work done so far.

    I can understand the frustration of having a VM crash. I currently run Access 2010 on an XP VM running inside Parallels Desktop on my Mac. Had that VM start crashing when using Access 2010 and ended up restoring a VM image from an earlier date.

    I was hoping you'd be able to see the charts on the form that opens with the DB. The charting is the critical piece. The form has a tabbed control sitting on a sub-form to that main form. There are six tabs representing Weekly, Monthly, Quarterly, Yearly, Operator Waste, and Operator Productivity charts.

    The charts track changes in the WastePercent (I know, Waste% was a mistake to use. and Productivity as records are entered by the user.

    Based on what you've provided so far, I need to track the values for WastePercent and Productivity for each product in the product line. The current charts on my form track changes nicely but the values tracked are averages and not actual numbers, given the "math" section I provided. The code in the row source of the chart for the Weekly WastePercent chart is "SELECT [Part Number],Avg([WastePercent]) AS [-WastePercent] FROM [qryWeeklyWastePercentLineChartOnForm] GROUP BY [Part Number];" and so it is giving averages and not what an actual number would be.

    The actual number is derived from, for each product, by getting the total Waste number from each record and taking that total Sum and dividing it by the total sum of the LF Run.

    So for example:

    Product is HOC
    There are 15 records for product HOC, for example. Sometimes more/less
    I take all the Waste numbers for product HOC and add them up.
    I then take all the LF Run numbers for HOC and add them up.
    Then I take these totals, and divide the Waste total by the LF Run total to arrive at total Waste% for HOC.

    This should be done for each product and shown on the chart. currently only show averages using code from above.

    How do I get actual? This is my big problem.

    I will post a jpeg of the front of the form showing the charts in my thread.

    The actual numbers should be:

    Actual total = .66% and not the average of .87%
    The HOC = .89% and not average of 1.78%
    The LL100 = .58% and not average of 2.59%


    Chris
    Attached Thumbnails Attached Thumbnails charts_on_form.jpg  
    Last edited by Repent; 10-05-11 at 19:44.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    I'm sorry but I don't understand what you mean with "actual" values. The query performs a GROUP BY operation, which means that it aggregates several rows into a single one.

    For the Part Number column, it's easy: all aggregated values are the same (the P.N.), so no further aggregation function is required.

    For the WastePercent column however, we have several rows with different distinct values in each row. As we only want a single row for each distinct Part Number value, we must provide an additional aggregate function which combines the different WastePercent values into a single one.

    You used the AVG function which computes the mean (average) value, but perhaps the SUM function would be more appropriate for the results you're looking for. Here are three cases of Part Number values as they appear in the base query and what they become after aggregation, one time using the AVG function on the WastePercent column, the other time using the SUM function on the same column.
    Code:
                   Base Query  |                         AVG  |                          SUM        
    Part Number  WastePercent  |  Part Number  -WastePercent  |  Part Number   -WastePercent
    CAOC                    0  |  CAOC                     0  |  CAOC                      0
    CAOC                    0  |  HBOC           0,055596196  |  HBOC            0,111192392
    CAOC                    0  |  HOC                      0  |  HOC                       0
    CAOC                    0  |                                              
    CAOC                    0  |
    Code:
                   Base Query  |                         AVG  |                          SUM        
    Part Number  WastePercent  |  Part Number  -WastePercent  |  Part Number   -WastePercent
    HBOC          0,111192392  |  CAOC                     0  |  CAOC             0
    HBOC                    0  |  HBOC           0,055596196  |  HBOC            0,111192392
    Code:
                    Base Query |                          AVG |                          SUM      
    Part Number   WastePercent |  Part Number   -WastePercent |  Part Number   -WastePercent
    SOC        0,002067015     |  CAOC                      0 |  CAOC                      0
    SOC        0,059602649     |  HBOC            0,055596196 |  HBOC            0,111192392
    SOC        0,017295597     |  SOC              0,01647733 |  SOC             0,148295971
    SOC        0,008088441     |
    SOC        0,035757332     |
    SOC        0,011847065     |
    SOC        0,000503025     |
    SOC        0,002336057     |
    SOC        0,010798789     |

    One way of another, there must be a function for grouping the different values of the WastePercent column. Otherwise, one value would be chosen randomly (probably the first), which would provide a meaningless result.
    Have a nice day!

  9. #9
    Join Date
    Aug 2010
    Posts
    14

    Additional screenshots and explaination

    Good Morning Sinndho;

    What I mean by "actual values" are those obtained from doing the math of:
    Waste%=Waste/LF Run

    You are right in that the queries return several listings for the same part numbers because there are End-Days and End-Nights records for the same product most times as well as multiple (and different) days worth of records.

    Somehow, (what I need to get figured out) all the waste numbers and LF Run numbers for each product regardless of shift or day need to be added together and those "totals" used in the formula of "Waste%=Waste/LF Run" to arrive at what I have been referring to as the "actual" value" for Waste%. Right now the queries and charting show "averages" which is what I no longer want.

    I've attached a screenshot of a query I made showing multiple records (8) for Products called "HOC" and "LL100". The records are for three days worth of production from both the End-Nights (Nightshift) and End-Days (Dayshift).
    To do all the calculations "longhand", I would start with "HOC" and add the values for Waste together getting a total of 3710. I would then add together the values for LF Run for a total of 479600. I would then divide the Waste (3710) by the LF Run (479600) to get my Waste% value of .77%. I would then place the value of .77% on one line under the field of Waste%.

    I would then do the same thing for the product called LL100 to arrive at a Waste% value of .59% and again place that value on a single line so instead of there being 8 records or lines in the query with all their field data there would now only be 2 records in the query, one for HOC and the other for LL100. Of course I would want the other fields to be summed totals so their values could be used in other places, reports, etc.

    If you look at the screenshot I've made notes on it to hopefully more fully explain what I'm looking for.
    Attached Thumbnails Attached Thumbnails Screenshot for Sinndho.jpg  

  10. #10
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Sorry for the delay! I did not forget about your problem but it's been a busy week.

    This query will yield the figures for [LF Run] (summed) and for [LF Produced] (summed), grouped by ProductID (i.e. one row for each ProductID), with several conditions regarding the production date (I added Week 35 to get results when testing):
    Code:
    SELECT tblProductionNumbers.ProductID, 
           Sum(tblProductionNumbers.[LF Run]) AS Sum_Of_LF_Run, 
           Sum(tblProductionNumbers.[LF Produced]) AS Sum_Of_LF_Produced
        FROM tblProductionHours 
            INNER JOIN (tblProductList 
                INNER JOIN tblProductionNumbers ON tblProductList.ID = tblProductionNumbers.ProductID) 
            ON tblProductionHours.ID = tblProductionNumbers.TimeID
        WHERE ((tblProductionHours.Time In ("END-Days","End-Nights")) AND 
               (DatePart("ww",[ProductionDate])=DatePart("ww",Date())) AND 
               (Year([ProductionDate])=Year(Date()))) OR ((DatePart("ww",[ProductionDate])=35))
        GROUP BY tblProductionNumbers.ProductID
    As I wrote before, it's easier to perform additional calculations (Waste, Waste%) outside the query with a Group By clause (which then becomes a subquery), like this:
    Code:
    SELECT g.ProductID, 
           g.Sum_Of_LF_Run, 
           g.Sum_Of_LF_Produced, 
           g.Sum_Of_LF_Run - g.Sum_Of_LF_Produced AS Waste, 
           (g.Sum_Of_LF_Run - g.Sum_Of_LF_Produced) / g.Sum_Of_LF_Run AS Waste_Percent
        FROM (
               SELECT tblProductionNumbers.ProductID, 
                      Sum(tblProductionNumbers.[LF Run]) AS Sum_Of_LF_Run, 
                      Sum(tblProductionNumbers.[LF Produced]) AS Sum_Of_LF_Produced
                   FROM tblProductionHours 
                       INNER JOIN (tblProductList 
                           INNER JOIN tblProductionNumbers ON tblProductList.ID = tblProductionNumbers.ProductID) 
                       ON tblProductionHours.ID = tblProductionNumbers.TimeID
                   WHERE ((tblProductionHours.Time In ("END-Days","End-Nights")) AND 
                          (DatePart("ww",[ProductionDate])=DatePart("ww",Date())) AND 
                          (Year([ProductionDate])=Year(Date()))) OR ((DatePart("ww",[ProductionDate])=35))
                   GROUP BY tblProductionNumbers.ProductID
             ) AS g
    ;
    If needed, you can add supplemental columns in the outer query, using ProductID to create a join if necessary.

    Is it what you're looking for?
    Attached Thumbnails Attached Thumbnails qry_GroupBy.jpg   qry_GroupBy_Plus_Waste.jpg  
    Have a nice day!

Posting Permissions

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