Solved

Obtaining Percentages in MS Access

Posted on 2012-03-14
9
189 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
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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now