I Need Help 1

[xeb]

In Access 2000, I'm trying to write an expression for a report that will average (Avg) four columns in a table.

The following works fine for one field:

=Avg([Field 1])

What should I use to get the average of four fields?

 

[dhookom]

=(Sum([Field1])+Sum([Field2])+Sum([Field3])+Sum([Field4]))/(Count([Field1])+Count([Field2])+Count([Field3])+Count([Field4]))

This sounds like your data is a bit un-normalized.

Duane
MS Access MVP

 

[xeb]

Thanks!

What do you mean by un-normalized?

 

[dhookom]

Whenever an application requires summing or averaging across fields rather than across records, it hints at un-normalized table structures. For instance if your table fields were grades for four different tests for one student. These grades should each be contained in their own record rather than 4 fields in a single record.

You didn't provide any clues as to the values you are storing so my comments are only guesses.

Duane
MS Access MVP

 

[xeb]

Duane:

I got it working!

Thanks

 

[ammujr]

Hi,
I was wondering if I could force a field in a report.

Let me explain, I have a report that is based off a query( Product survey letter). The query is set to ask me which sales order number, it then inserts the the relevant information into the letter for the customers (like there name, the sales order # , todays date).

There is a form that allows me to update and search through the list of Sales orders. I added a command button on the form that allows me to preview the Survey letter. But when i press the button it asks me to for the sales order number. I was wondering if I could force the report to use the sales order number that is already displayed on that form.

Thank you, I hope it makes sense.

 

[dhookom]

I recommend against parameter queries. I prefer to modify the DoCmd.OpenReport... line of code to include a "where" clause.

Dim strWHere as String
strWhere = "[SalesOrderNum]=" & Me.txtSalesOrderNumber
DoCmd.OpenReport "rptLetter",acviewpreview, , strWhere



Duane
MS Access MVP

 

[ammujr]

Hi,
Thank for the help. But when Remove the parameter query and insert what the new code in the expression build. I still get "mismatched in criteria expression" for an error.

Private Sub Command55_Click()
On Error GoTo Err_Command55_Click

Dim strWHere As String
strWHere = "[SO NO]=" & Me.txtSalesOrderNumber
DoCmd.OpenReport "Product Quality Survey", acViewPreview, , strWHere

Exit_Command55_Click:
Exit Sub

Err_Command55_Click:
MsgBox Err.Description
Resume Exit_Command55_Click

End Sub


Thanks
Jill

 

[dhookom]

You are definitely on the right track. I assumed your SO NO was numeric. If it is a text field, try:
strWHere = "[SO NO]=""" & Me.txtSalesOrderNumber & """"


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ammujr]

THANK YOU!!!!