[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Obtaining Percentages in MS Access

Posted on 2012-03-14
9
Medium Priority
?
197 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 93

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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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 2000 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

656 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