Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-21-2019, 12:40 PM   #1
juancarlos
Newly Registered User
 
Join Date: Jan 2019
Location: Austin, TX
Posts: 18
Thanks: 5
Thanked 0 Times in 0 Posts
juancarlos is on a distinguished road
Calculating percent automatically

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

juancarlos is offline   Reply With Quote
Old 02-21-2019, 12:42 PM   #2
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,040
Thanks: 36
Thanked 722 Times in 705 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Calculating percent automatically

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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 02-21-2019, 01:04 PM   #3
juancarlos
Newly Registered User
 
Join Date: Jan 2019
Location: Austin, TX
Posts: 18
Thanks: 5
Thanked 0 Times in 0 Posts
juancarlos is on a distinguished road
Re: Calculating percent automatically

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

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

-JC

juancarlos is offline   Reply With Quote
Old 02-21-2019, 01:06 PM   #4
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,040
Thanks: 36
Thanked 722 Times in 705 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Calculating percent automatically

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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
juancarlos (02-21-2019)
Old 02-21-2019, 01:13 PM   #5
juancarlos
Newly Registered User
 
Join Date: Jan 2019
Location: Austin, TX
Posts: 18
Thanks: 5
Thanked 0 Times in 0 Posts
juancarlos is on a distinguished road
Re: Calculating percent automatically

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

Thanks!

-JC
juancarlos is offline   Reply With Quote
Old 02-21-2019, 01:15 PM   #6
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,040
Thanks: 36
Thanked 722 Times in 705 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Calculating percent automatically

Hi JC. You're welcome. Glad to hear you got it to work. Good luck with your project.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 06-17-2019, 03:55 PM   #7
juancarlos
Newly Registered User
 
Join Date: Jan 2019
Location: Austin, TX
Posts: 18
Thanks: 5
Thanked 0 Times in 0 Posts
juancarlos is on a distinguished road
Re: Calculating percent automatically

Hello!

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

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.

juancarlos is offline   Reply With Quote
Old 06-17-2019, 06:53 PM   #8
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,943
Thanks: 79
Thanked 1,566 Times in 1,454 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Calculating percent automatically

Quote:
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(" & TgtFld & ") AS CNT FROM " & Dmn
IF Len( NZ( Crit, "" ) ) <> 0 Then
    strSQL = strSQL & " WHERE " & Crit
End If
strSQL = strSQL & " ;"
' strSQL now is "SELECT COUNT([TgtFld]) AS CNT FROM Dmn WHERE Crit ;"
Set rsDCnt = CurrentDB.Openrecordset( strSQL, dbOpenDynaset )
' no need to do a .MoveFirst because the COUNT aggregate  is going to produce only one record.
DCount = rsDCnt![Cnt]
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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 06-18-2019, 07:03 AM   #9
juancarlos
Newly Registered User
 
Join Date: Jan 2019
Location: Austin, TX
Posts: 18
Thanks: 5
Thanked 0 Times in 0 Posts
juancarlos is on a distinguished road
Re: Calculating percent automatically

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
juancarlos is offline   Reply With Quote
Old 06-18-2019, 07:34 AM   #10
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,943
Thanks: 79
Thanked 1,566 Times in 1,454 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Calculating percent automatically

Quote:
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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Automatically calculating averages based on having the same names? tyrannitar Queries 12 08-10-2010 08:16 AM
Calculating percent complete Thedda Queries 1 09-19-2009 03:51 AM
percent kingsgambit Reports 0 08-25-2003 03:45 PM
Percent Susan Allen Wyoming Forms 7 02-22-2001 10:53 AM
Calculating age automatically from Date Of Birth OutbackInvestments Forms 2 06-09-2000 07:10 AM




All times are GMT -8. The time now is 03:38 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World