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    
post Jul 15 2019, 04:45 AM

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
Go to the top of the page
post Jul 15 2019, 07:05 AM

Posts: 1,939
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
post Jul 15 2019, 07:10 AM

UA Admin
Posts: 35,522
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?


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
post Jul 15 2019, 08:15 AM

Posts: 2,553
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:
    MyCalc: ((DCount("*", "<[Your Query]>", "[Score] > 0") * 0.25) / (DCount("*", "<[Your Query]>") / 20))
  2. Using Literal Values as CONSTANTS:
    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:

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

Posts: 20,447
Joined: 10-January 04

hi Dan,

perhaps something like this:
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,
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    23rd August 2019 - 08:28 AM