Solved

Obtaining Percentages in MS Access

Posted on 2012-03-14
9
195 Views
Last Modified: 2012-03-15
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.
0
Comment
Question by:jsawicki
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 37722730
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
 

Author Comment

by:jsawicki
ID: 37723076
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 37723131
Perhaps you should upload a file with some sample data, and the output you would expect given that sample.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37723169
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
 

Author Comment

by:jsawicki
ID: 37725716
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 37725966
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
 

Author Comment

by:jsawicki
ID: 37726074
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37726575
<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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37726595
<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

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question