Sum a Control 1

[Gaffi1]

I am creating an employee production tracking database. I've had no problems up until now while making the report. In the report, in the detail section I have the data with two text box' calculating weighted averages. In the group footer, I have a text box to sum the weighted averages. When I try to view the report, it asks for the parameter of the weighted average textbox. If I hit enter I get a blank. If I enter a number, the result is the number of records times the value entered.

Reading through, the closest post I found to this problem was

http://www.tek-tips.com/viewthread.cfm?qid=905748

but the person gave up and went to a different program instead of creating a work around.

The weighted average box is absolutly essential for this to work as it's the single value that determines incentives for our employees. If needed, I can provide a link to a dummy version of the database online for someone to review. I really appreciate your help!

 

[willir]

Alright, I like to think that there are three important types of controls...
- Bound to a field in a table. The value in the table is reflected in the control. A value entered in the control updates the value in the table.
- Unbound controls such as combo boxes and list boxes used to select / filter / manipulate output and such. For example, select all invoices assigned to a customer, order invoices by descending date, find all paid invoices. I just use invoices here, but hopefully you get the gist.
- Calculated values depending values on the form, or from a table, or combination thereof.

Both you and xicana are working with the calculated values. However, xicana had problems in two areas -- an out of scope issue where totals between sections of a report, and not pursuing aggregate functions. (Too bad she gave up - Duane / dhookom is top notch.)

Aggregate functions reference the tables directly which is can be very effective. These functions include DLookup, DSum, etc.

You can also perform this action "behind the scenes" using VBA code. This apporach is actually easier to use once you have worked through a couple of examples.

Your post is in the "Form" forum section and not the "Report" forum which you have already visited, so I will focus on this.

(But I have to wonder about your comment on...

...In the report, in the detail section ... When I try to view the report

)


To use the aggregate function, use, for example, DSum...
=DSum("[TargetField]", "TableName", "[KeyField] = " & Me.FieldName)

The other functions work pretty much the same way.

For the total of invoice for InvoiceID, assuming InvoiceID is numeric, the syntax would something like...
=DSum("[ExtendedAmount]", "tblInvoiceDetail", "[InvoiceID] = " & Me.InvoiceID)

From the coding level, you would use OnCurrent event property, AfterUpdate property, and perhaps other events. In this case, the amount of fat in meat with three types of measurements for a month...

Dim sngMethod1 as Single, sngMethod2 as Single, sngMethod3 as Single
Dim strQ as String

strQ = Chr$(34)
'Note: WHERE clause is complex - two checks, 
'      MethodType is a text string, the Month is numeric
sngMethod1 = DAvg("FatReading", tblMeatQuality", "MethodType = " & strQ & "Method1" & strQ & " and Month(SampleDate) = " & Month(Me.SampleDate)

sngMethod2 = DAvg("FatReading", tblMeatQuality", "MethodType = " & strQ & "Method2" & strQ & " and Month(SampleDate) = " & Month(Me.SampleDate)

sngMethod3 = DAvg("FatReading", tblMeatQuality", "MethodType = " & strQ & "Method3" & strQ & " and Month(SampleDate) = " & Month(Me.SampleDate)

Me.AvgReading = (sngMethod1 + sngMethod2 + sngMethod3)/3

Where Me.AvgReading is a text box on a form.

Two more points....
- You can create a query and reference the query for your total or average
- I feel, by far, the most flexible way to perform complicated calculations from either a form or report is to create your own function. Using the Fat content in meat as an example...

Function CalcFat (ReadingMonth as Interger) as Single

Dim sngMethod1 as Single, sngMethod2 as Single, sngMethod3 as Single
Dim strQ as String

strQ = Chr$(34)

sngMethod1 = DAvg("FatReading", tblMeatQuality", "MethodType = " & strQ & "Method1" & strQ & " and Month(SampleDate) = " & ReadingMonth 

sngMethod2 = DAvg("FatReading", tblMeatQuality", "MethodType = " & strQ & "Method2" & strQ & " and Month(SampleDate) = " & ReadingMonth 

sngMethod3 = DAvg("FatReading", tblMeatQuality", "MethodType = " & strQ & "Method3" & strQ & " and Month(SampleDate) = " & ReadingMonth )

CalcFat = (sngMethod1 + sngMethod2 + sngMethod3)/3

End Function

The advantage of using the function is that it can be called from just about anywhere - a control on a form, a report, even a query.

Since you have not provided specifics, I can not provide a specific answer, but hopefully, at least one of these ideas helped.

Richard