Solved text box to average specific rows (1 Viewer)

[Char409]

So, imagine having a list of customers and all of their different order amounts;
EX: customer1 order1 amount1
customer1 order2 amount2
customer2 order3 amount3
customer1 order4 amount4
say all of that information is in a query and I want to average each customer's total amount, so essentially take row 1 and 2 and 4, average the amount and take row 3 and average the amount. Is this possible?

Right now I'm just trying to get it to average just one set of rows but instead of customers I'm using machines and PM's completed trying to get a percent of PM's completed per machine. I have a form with a text box and control source set to: = SELECT Avg(Forms![frm1]![Expr2]), Forms![frm1]![Machine] FROM Forms![frm1] WHERE (((Forms![frm1]![Machine])="EMLK Blue"));
but I get syntax of the subquery in this expression is incorrect. I'm pretty new to all of this so I'm not sure what I'm doing wrong.
Now, I can just set it to =avg(expr2) and then filter by machine and that works but my end goal is to have another form that lists every machine once with it's percent PM's completed next to it and I pretty much have it set up to list every PM the machine has and if it is up to date to equal 1 otherwise equal 0 and then I'm just trying to average that out which gives me percent of PMs up to date.

 

[pbaldy]

You could use DAvg(), here's the syntax:

General: DLookup Usage Samples

You typically query a table, not a form, so something like:

SELECT Machine, Avg(Amount) As AvgAmount
FROM TableName
GROUP BY Machine

 

[Char409]

So this is my query and I am trying to average expr2 for each machine, I tried making a form and using a text box with control source set to: =DAvg("Expr2","Query1","Machine = EMLK Blue")
I just get error.

and expr2 is: Expr2: IIf([Expr1]>=[Frequency (#)],"0","1")
which seems by itself it doesn't register as a number, but when I make a form from this query then in the form it counts as a number and I can average it out.

I also tried to query "Query1" so that Expr2 wouldn't be an expression anymore and just be numbers and then =DAvg("Expr2","Query2","Machine = EMLK Blue") but still just get error

 

[arnelgp]

Expr2 should be Numeric for it to work:

Expr2: IIf([Expr1]>=[Frequency (#)],0,1)


=DAvg("Expr2","Query1","Machine = 'EMLK Blue'")

 

[pbaldy]

I'll get out of the way.

 

[Char409]

Ohhhh I didn't realize I could just leave off "" to make it a number and also I didn't know I had to have '' around EMLK Blue

Works wonderfully. Thank you both.