Calculating percent automatically

[juancarlos]

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!

 

[theDBguy]

You could try something like: [Totals]/DCount("*","TableName")
Hope it helps...

 

[juancarlos]

It did!...but the numbers are off.

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

-JC

 

[theDBguy]

Hi JC. Try it this way: [Totals]/DCount("*","TableName","IsNull([Final Status])=False")

 

[juancarlos]

Whoa! There's no way I was gonna get that. Yup, that worked perfectly.

Thanks!

-JC

 

[theDBguy]

Hi JC. You're welcome. Glad to hear you got it to work. Good luck with your project.

 

[juancarlos]

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.



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.

As always, thanks for all your help.

 

[The_Doc_Man]

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:

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]

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]

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.