Calculating percent automatically (1 Viewer)

juancarlos

Registered User.
Local time
Today, 05:03
Joined
Jan 29, 2019
Messages
18
Hello!

I have a table that records the status of cases submitted. I was able to count how many of each there are, and get a percentage, but it's wonky.

The way I did it was by counting the number and adding a Totals row. Then I manually copy that number into the percent expression (literally [Totals]/whatever number). Is there a way to automatically calculate the percent?

Thanks!

querytcod.png

queryt.png
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:03
Joined
Oct 29, 2018
Messages
21,357
You could try something like: [Totals]/DCount("*","TableName")
Hope it helps...
 

juancarlos

Registered User.
Local time
Today, 05:03
Joined
Jan 29, 2019
Messages
18
It did!...but the numbers are off.

I'm getting 85.02% for Issued which was 1566/1723 = 90.95%

-JC
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:03
Joined
Oct 29, 2018
Messages
21,357
Hi JC. Try it this way: [Totals]/DCount("*","TableName","IsNull([Final Status])=False")
 

juancarlos

Registered User.
Local time
Today, 05:03
Joined
Jan 29, 2019
Messages
18
Whoa! There's no way I was gonna get that. Yup, that worked perfectly.

Thanks!

-JC
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:03
Joined
Oct 29, 2018
Messages
21,357
Hi JC. You're welcome. Glad to hear you got it to work. Good luck with your project.
 

juancarlos

Registered User.
Local time
Today, 05:03
Joined
Jan 29, 2019
Messages
18
Hello!

I'm resurrecting this to see if I can get pointed in the right direction. :banghead:

The solution above has been working great, but now that we have more data and more time has passed, we want to see how these numbers change month to month.

So, I added a new column [Opened Date], and in the totals row I put "Where" and then I set it up to ask for criteria like this ">[Greater than how many days]".

The idea is that you'd put a date 30 days, 60 days, 90 days in the past and identify trends.

The query works, but I think it's still counting the total files, not just the ones I want to see. It's super easy to just look at the numbers and divide, but I want it done automagically.

dddsd.png

As you can see, the totals look correct, but the percentages make it seem as if they're being divided by the original total. How does one limit the "DCount"?

Here's the query in design view. sdfd.png

As always, thanks for all your help.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:03
Joined
Feb 28, 2001
Messages
26,996
How does one limit the "DCount"?

A DCount has three arguments - a target field, a domain, and the criteria clause. Imagine, if you will, that Access does this:

Code:
Public Function DCount( TgtFld as String, Dmn as String, Crit as String ) As Variant

Dim strSQL as String, rsDCnt as DAO.Recordset

strSQL = "SELECT COUNT(" & [B]TgtFld[/B] & ") AS CNT FROM " & [B]Dmn[/B]
IF Len( NZ( [B]Crit[/B], "" ) ) <> 0 Then
    strSQL = strSQL & " WHERE " & [B]Crit[/B]
End If
strSQL = strSQL & " ;"
' strSQL now is "SELECT COUNT([[B]TgtFld[/B]]) AS CNT FROM [B]Dmn[/B] WHERE [B]Crit[/B] ;"
Set rsDCnt = CurrentDB.Openrecordset( strSQL, dbOpenDynaset )
' no need to do a .MoveFirst because the COUNT aggregate  is going to produce only one record.
[B]DCount[/B] = rsDCnt![[B]Cnt[/B]]
rsDCnt.Close
End Function

What I just posted is NOT the way it actually happens because you can't open a recordset on an SQL string like that. The domain actually has to be named. It's a visibility issue. A couple of other items don't actually work that way either.

However, if you wanted to limit the DCount then imagine that what I just posted is very close to what actually happens. Figure out how you would limit the implied SQL statement and express those limits in the criteria clause.
 

juancarlos

Registered User.
Local time
Today, 05:03
Joined
Jan 29, 2019
Messages
18
Alright! Yes I see where it's headed. Great that it can be contained within the same query (I keep reading to make several queries and then combine, which I want to avoid at the moment).

Thanks for the explanation, I think I can figure it out from there.

Come to think of it, it might be better to create three queries with a "hardwired" parameter of "30 days ago", "60 days ago", etc. This might just be a preference thing, but I want to make it super easy for the people who will use it (I literally want them to press one button to get their info).

Thank you!
-JC
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:03
Joined
Feb 28, 2001
Messages
26,996
it might be better to create three queries with a "hardwired" parameter

Different strokes for different folks, but if it works for you and you are happy with it, and it is more conceptually easy to do it that way and get onto other things, then I'm all for it. And it is DEFINITELY NOT WRONG to do it that way.
 

Users who are viewing this thread

Top Bottom