UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query Multi Calculation?, Access 2016    
 
   
ciapul12
post Jul 15 2019, 04:45 AM
Post#1



Posts: 285
Joined: 7-June 14



Hi All,
I need help with the calculation in my formula.
What I need is the following:
1) count total number of records in my query and divide by 20, lets say there is 440 records so the result will be 22
2) count total number of records that contain value grater than 0 lets say there are 367 records
3) multiply the total number of records grater than 0 by 0.25
4) divide everything by the first value (in this case 22)
I think there will be 3 calculation steps in this formula? 440/20=22, 367*0.25=91.75, 95.75/22=4.17 (This is the overall score)

I honestly have no idea how this formula should look like so would appreciate your input.
The name of the field is called: "Score"

Thank You
Dan
Go to the top of the page
 
orange999
post Jul 15 2019, 07:05 AM
Post#2



Posts: 1,933
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


What field in your records are you comparing with 0?

--------------------
Good luck with your project!
Go to the top of the page
 
GroverParkGeorge
post Jul 15 2019, 07:10 AM
Post#3


UA Admin
Posts: 35,508
Joined: 20-June 02
From: Newcastle, WA


Many times it is much easier to have sample data in addition to an abstracted description when trying to come up with suggested approaches.

Could you provide sample data from your Access Relational Database Application for us to analyze and work with?

Thanks.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ADezii
post Jul 15 2019, 08:15 AM
Post#4



Posts: 2,537
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. I agree with GPG as far as Sample Data, but the actual Expression (NOT TESTED) would be something similar to:
    CODE
    MyCalc: ((DCount("*", "<[Your Query]>", "[Score] > 0") * 0.25) / (DCount("*", "<[Your Query]>") / 20))
  2. Using Literal Values as CONSTANTS:
    CODE
    Dim sngResult As Single
    Const conRECS_IN_QUERY = 440
    Const conRECS_OVER_0 = 367

    sngResult = ((conRECS_OVER_0 * 0.25) / (conRECS_IN_QUERY / 20))

    Debug.Print FormatNumber(sngResult, 2)
  3. the result would be:
    CODE
    4.17

This post has been edited by ADezii: Jul 15 2019, 08:19 AM
Go to the top of the page
 
strive4peace
post Jul 16 2019, 02:32 AM
Post#5


strive4peace
Posts: 20,445
Joined: 10-January 04



hi Dan,

perhaps something like this:
CODE
SELECT Count(*)/20 as Count20
, Sum (iif (nz([score],0) > 0, 1, 0)) as NbrPositive
, [NbrPositive] * 0.25 as QtrPositive
, Round([QtrPositive]/[Count20],2) AS Result
FROM [TableOrQueryname]

This post has been edited by strive4peace: Jul 16 2019, 02:37 AM

--------------------
have an awesome day,
crystal
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th August 2019 - 01:46 AM