Help creating DSum formula

[Repent34]

I am trying to create a DSum formula (to use as a control source in a chart) in a query that will look at a particular set of records(based on a product type), and take the value of that record's waste number (a calculated number) and divide that by the value of that record's LF Run number (entered by user).

The table name is: tblProductionNumbers
Waste is a calculated field already in the query. Waste: [LF Run]-[LF Produced]
LF Run is a value entered by user
LF Produced is a value entered by the user

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. 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%".

How would I construct this in a DSum formula?

 

[PWise]

Dsum(" [LF Run]-[LF Produced]","tblProductionNumbers","Product=ll100")/dcount("*","tblProductionNumbers","Product=ll100"))

 

[Repent34]

Thank you for the reply PWise.

How would you code this so that the "LL100" could be any product? We produce about 20 products and the query could be any of those, just depends on what they are producing that day.

 

[Repent34]

I modified this code a little bit. A product they are running right now is called "HOC" instead of "LL100". Instead of "Product" as the table field it is actually "Part Number" (sorry).

I am getting an error of "Syntax error (missing operator) in query expression 'Part Number=HOC'.

 

[PWise]

sorry try

"Part Number='HOC'

 

[Repent34]

This is the whole code in the query:

Actual Waste%: DSum(" [LF Run]-[LF Produced]","tblProductionNumbers","Part Number='HOC'")/DCount("*","tblProductionNumbers","Part Number='HOC'")

I still get the error and when I acknowldege it I get a dialog box that says "unknown"

 

[PWise]

Try

[Part Number]='HOC'"

 

[Repent34]

I think part of the problem is that the field for "Part Number resides in a different table called "tblProductList" and not in the table for tblProductionNumbers.

I tried the code below (and several other iterations) to allow for the different table.

Actual Waste%: DSum(" [LF Run]-[LF Produced]","tblProductionNumbers","[tblProductList].[Part Number]='HOC'")/DCount("*","tblProductionNumbers","[tblProductList].[Part Number]='HOC'")

but it's telling me it cannot find

 

[Repent34]

hit submit before finishing my thought.

said it couldn't find the table for tblProductList. "Part Number" resides in a table called "ProductList"

 

[Repent34]

Would your formula give an actual number or an average? Just asking because of the "DCount" keyword. I already have it doing averages on my chart but want an actual number.

The code I'm using for averages is:

SELECT [Part Number],Avg(([Waste%])) AS [-Waste%] FROM [qryWeeklyWastePercentLineChartOnForm] GROUP BY [Part Number];

 

[Repent34]

bump to the top for help

 

[Repent34]

Can I even use DSum with two tables? The part number is in tblProductList and the rest of the data is in tblProductionNumbers.

 

[Repent34]

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.