Obtaining Percentages in MS Access

I have two fields for which i am trying to obtain a percentage of users accessing an application.  The first field [login name] shows everyone having the application whereas the second field [Last User] shows the individuals who have actually accessed it.  For individuals not accessing the application, the field is blank.  My ultimate goal is to create a report so my director can retrieve this info at anytime.  I have researched and tested many ideas but can't seem to get it to work.
jsawickiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jerry MillerCommented:
In your report, add 3 text boxes (1 for users with app access, 1 for users using it and 1 for the percentage). For the first 2, do a COUNT(userID) from <tableName> where is not null. And in the third box, do a =sum(textBox2 (users) / textBox1 (user w/access)). Format box 3 as a percent.

http://office.microsoft.com/en-us/access-help/counting-in-reports-HA001137763.aspx

http://office.microsoft.com/en-us/access-help/summing-in-reports-HA001122444.aspx
0
jsawickiAuthor Commented:
Since we are talking about 4000 plus names, i don't think i want to generate a report that lists all the user names, then the totals and then the percentage.  That would be too long of a report.  Is there a way only these totals can be displayed in the report.  Can a query be created that provides these sums so once the sums are linked to the report, then the 3rd column can be created as you described to list the percentage?
0
Patrick MatthewsCommented:
Perhaps you should upload a file with some sample data, and the output you would expect given that sample.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jeffrey CoachmanMIS LiasonCommented:
Lets be clear,
1. What exactly is in these fields?
2. What is the exact formula for this percentage?
(Don't describe what you want, ...show me a formula)
3. What *exactly* do you want this report to display?
4. <I have researched and tested many ideas but can't seem to get it to work.>
Can you post what you tested?, ...perhaps a simple tweak will get you up and going...
5. What version of Access are you using?

This is why it is so important to include a sample of your raw data, and an example of the exact output you are seeking, based on the sample data.

Here is my take on this:
Database70.mdb
0
jsawickiAuthor Commented:
Sorry Boag2000 i am new to these types of sites.  Basically the mock database you attached is right on, but let me give you a little history.  Our agency uses dragon speaking software and we are trying to determine the number of licenses that our being utilized.  The LoginName is tied to signing into the computer whereas the LastUsed is who actually initiates the software.  When it is blank, it means the software is not being utilized.  

Since i am building additional queries and reports for this project, i have to do this process in access versus excel.  I basically need to find a way to obtain a total number for each column and then divide those totals for LastUsed/LoginName.  This will give me the usage percentage.  If these totals populate in a query then i can just use the query to provide numbers versus a report.    Due to the nature of the info in these reports, i can't upload anything and if i did, it would be generic like u provided.  

I did attach about as far as i got in trying to get total numbers for each category in a query.  All other variations i tried resulted in query mismatch criteria expressions.  

If you require any additional info, please let me know.
percentage.doc
0
Jeffrey CoachmanMIS LiasonCommented:
Well if you just want a query that displays the percentage, then use something like this in my sample:
SELECT DCount("LastUsed","YourTable","Len(LastUsed)<>0")/Count([LoginName]) AS Utilization
FROM YourTable;

(Again, then format this column to be a Percentage)

Just note that you still have not posted a clear graphical example of the *exact* output you are requiring...

Jeff
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jsawickiAuthor Commented:
Jeff this worked perfectly.  What if i wanted to also display on this query the totals for LoginName and LastUsed so they can not only see the percentage, but the totals that make up the percentage.  Since i am not familiar with writing it as SQL can you write the whole thing out so i can fully understand how it connects including the punctuation used.  Once i grasp these concepts the first time, i usually don't require this type of assistance in the future.  

For education purposes, what does this exactly mean "clear graphical example of the *exact* output you are requiring..."  

Thanks for your patience with me being a newbee
0
Jeffrey CoachmanMIS LiasonCommented:
<What if i wanted to also display on this query the totals for LoginName and LastUsed so they can not only see the percentage, but the totals that make up the percentage. >

SELECT Count([LoginName]) AS LogInCount, CLng(DCount("LastUsed","YourTable","Len(LastUsed)<>0")) AS LastUsedCount, CLng(DCount("LastUsed","YourTable","Len(LastUsed)<>0"))/Count([LoginName]) AS Utilization
FROM YourTable;
0
Jeffrey CoachmanMIS LiasonCommented:
<what does this exactly mean "clear graphical example of the *exact* output you are requiring..."  >

It means just that...
It is far simpler to just post an example of what you want than to describe it.

For example, you could have just done this in your first post and nothing else would have been needed...

Dear experts, I have this:

LogInName      LastUsed
User1      
User2      User2
User3      
User4      
User5      
User6      
User7      
User8      User8
User9      
User10      User10
User11      User11
User12      User12
User13      User13
User14      
User15      User15
User16      User16
User17      User17
User18      User18
User19      
User20      User20
User21      
User22      
User23      
User24      
User25      
User26      
User27      
User28      
User29      
User30      User30
User31      
User32


...And I want this:
LogInCount      LastUsedCount      Utilization
32                      12                              0.375      


;-)

JeffCoachman
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.