Solved Query Average Computation Display in Form

[Alejo]

Hi XPS35,
Hi All,

XPS35 has provided the solution to my previous query and really works. Single average value per Cust_

I revised the query due to additional requirements by filtering the class"M" from ">#01/09/2022# And <#01/9/2023#"

But the form display several values of Cust_A, Cust_B instead of single average value, and the Cust_C does not display.

May I request you to please check. Attached is the Database, you can apply the the correct formula in the attached file.
Thanks you.

 

[mike60smart]

Hi XPS35,
Hi All,

XPS35 has provided the solution to my previous query and really works. Single average value per Cust_
View attachment 104180

I revised the query due to additional requirements by filtering the class"M" from ">#01/09/2022# And <#01/9/2023#"
View attachment 104181

But the form display several values of Cust_A, Cust_B instead of single average value, and the Cust_C does not display.
View attachment 104182

May I request you to please check. Attached is the Database, you can apply the the correct formula in the attached file.
Thanks you.

See attached

 

[Gasman]

That is beacuse you are grouping on those fields?

 

[Alejo]

See attached

hi,
just want to confirm if you revised something on the attachment, it looks exactly the same as the version 1.3

 

[CJ_London]

Strange date to be filtering on? Do you really mean 9th Jan?

 

[Alejo]

Strange date to be filtering on? Do you really mean 9th Jan?

Hi,

Have to filter the date because it will be a yearly report. In this case from 01/09/22 - 01/09/23

Sept 01 not Jan 9

 

[CJ_London]

SQL requires dates to be in US format of mm/dd/yyyy or the sql standard of yyyy-mm-dd

So you need to use 09/01/2022 or 2022-09-01

easy enough to check in the immediate window

?month(#01/09/2022#)
1

or

?format(#01/09/2022#,"dd mmm yyyy")
09 Jan 2022

 

[Alejo]

SQL requires dates to be in US format of mm/dd/yyyy or the sql standard of yyyy-mm-dd

So you need to use 09/01/2022 or 2022-09-01

easy enough to check in the immediate window

?month(#01/09/2022#)
1

or

?format(#01/09/2022#,"dd mmm yyyy")
09 Jan 2022

thanks Il do that in my actual file..
hope you can assist me with the attached file that i've sent

 

[arnelgp]

the fields: Product, Class_Type and D_Date must be "criteria" on your query:
see the revised qryAvgCustProdA on the attached.

 

[Alejo]

the fields: Product, Class_Type and D_Date must be "criteria" on your query:
see the revised qryAvgCustProdA on the attached.

Solved.

Thank you arnelgp