Average based on criteria (1 Viewer)

Adamfm · Jul 31, 2019

Hi,

Sorry new to this site and haven't touched Access for over 10 years! Have tried to search for an answer thought it maybe DAvg but can't get the syntax correct and keeps crashing my Db.

Anyway I have a query which counts number of passengers either successful or not.

Date | Flight No| Successful | PaxTotal

31/07/2019 | BZ0001 | Yes | 150
31/07/2019 | BZ0001 | No | 5

To avg so (5/150)*100
But how do I do this when the values are in the same column? I have tried to create 2 seperate queries and then join them together but that doesn't work either.

Would be grateful for some help!

Thanks
Adam

isladogs · Jul 31, 2019

Welcome Adam
I don't see how that calculation will give the average which would be (5+150)/2=77.5

pbaldy · Jul 31, 2019

Here's one way to get the value from another record:

http://allenbrowne.com/subquery-01.html#AnotherRecord

plog · Jul 31, 2019

You keep using the term "average" but the mathematical operations you use to demonstrate the calculation you want does not produce an average. Here's the formula for average:

(a1 + a2 + a3 +... + an) / n

And in English: The summation of n numbers divided by n where n is a number >0

So when you say:

5+150 = 155 then 5 divided by 155 ...

You are not making sense. You don't get to arbitrarily decide what the denominator is. You can't just declare you divide it by 155. You must divide it by the number of numbers you've added up, which in the example you gave is 2.

Further, never does 100 get multiplied in. Perhaps you don't mean "average", but rate of increase. In either case I think you need to be more explicit with your example. Because I can't get to 5, 150 nor 155 with the image you posted in your prior post.

pbaldy · Jul 31, 2019

The 5/150/155 were in the text of the OP, not the image. The *100 is to make the percentage "people friendly", to turn .03 into 3% Not saying I'd do it that way, but...

plog · Jul 31, 2019

Who's that clarifying statement suppose to help?

I explained why the words he used doesn't make sense, why the numbers in his post didn't make sense and why I was unable to deduce what he wanted when taken in totality with the image he posted.

arnelgp · Jul 31, 2019

Code:

SELECT DISTINCT [Date], [Flight Number], 
FormatPercent((DCOUNT("1", "yourTable", "[Flight Number]='" & [Flight Number] & "' AND Successful='Yes'") / 
(DCOUNT("1", "yourTable", "[Flight Number]='" & [Flight Number] & "'")) As PercentSuccess,
FormatPercent((DCOUNT("1", "yourTable", "[Flight Number]='" & [Flight Number] & "' AND Successful='No'") / 
(DCOUNT("1", "yourTable", "[Flight Number]='" & [Flight Number] & "'")) As PercentFailure 
FROM yourTable;

If [Successful] field is a Yes/No field change the "yes" and "no" to -1, 0 respectively.

pbaldy · Jul 31, 2019

plog said:
Who's that clarifying statement suppose to help?

I explained why the words he used doesn't make sense, why the numbers in his post didn't make sense and why I was unable to deduce what he wanted when taken in totality with the image he posted.

I apologize for trying to help. I believe I was able to deduce the goal from the totality of what was posted. I'll bow out.

Adamfm · Aug 1, 2019

Hi,

Sorry yes you are completely correct, not an average at all! Sorry was having a dumb day.

What I actually mean is what percentage of the total number of passengers have failed to board.

So that is where I get the formula from where out of 100 passengers 95 board and 5 don't therefore (5 / 100)+ 100 = 5%

pbaldy · Aug 1, 2019

Deleted thread restored since it had replies. I'll close it since the OP doesn't want to use it anymore.

Average based on criteria (1 Viewer)

Adamfm

Registered User.

isladogs

MVP / VIP

pbaldy

Wino Moderator

plog

Banishment Pending

pbaldy

Wino Moderator

plog

Banishment Pending

arnelgp

..forever waiting... waiting for jellybean!

pbaldy

Wino Moderator

Adamfm

Registered User.

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread