How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask flopcat98 Your Own Question

flopcat98
flopcat98, Consultant
Category: Microsoft Office
Satisfied Customers: 7600
Experience:  degree in comp sci. 20+ years install work with networks for small and large companies
23372949
Type Your Microsoft Office Question Here...
flopcat98 is online now
A new question is answered every 9 seconds

In access 2010, I need a field that calculates the percentage

Customer Question

In access 2010, I need a field that calculates the percentage of courses that an employee has completed. I need the calculation to take all of the fields in a record that represent a course (there are other fields with non course info) and if the field is blank or filled in, to use that info to determine the percentage of courses that are completed based on whether or not there is a number in the course field.

Can anyone help?
thx
LBS
Submitted: 3 years ago.
Category: Microsoft Office
Expert:  flopcat98 replied 3 years ago.

yeah the file should really be in a better structure for access. That structure lends itself more to excel ( which is explained by the fact that it was in excel first ) In access you can create a structure for that data that would work very easily but i guess you are stuck with this one.

 

You probably need a querry or maybe several to check each field to see if the course was taken and if it was completed and add 1 to a new field or variable num-taken if it was taken and add 1 to a new field completed if it was completed and then divide num-taken / completed to get your answer

 

Customer: replied 3 years ago.

Sorry, doesn't help me. I'm hoping for help actually writing an expression unless you know an easier way. I'm looking for a very specific solution if possible. I'm doing it in access because it's much easier to fill in the fields in a form than a wide spreadsheet.

Expert:  flopcat98 replied 3 years ago.

If you need a very specific solution you need to send me the data base.

 

And the way you want to do it in access is with a relational database

so that you have one table for the person taking the class

and a seperate table for each class taken and whether it was passed

that is linked to tables with the class description etc

 

then it is very easy to add up the classes taken and passed and get a percentage.

If you have a flat file with every class in it some of which were never taken it is a lot harder to do because you have to look at every field to see if the class was even taken

 

I dont actually know what you mean by specific solution. The one i gave you was pretty specific given the detail that you gave me. I think that it may be your lack of knowledge on programming access that is holding you back here. You cant make a simple equation. You need to make querries. They still arent all that hard to do. But they tend to look graphical and unless you want to send me the data base and i can build the querry for you there is no way i can give you the exact equation. Think about how usefull the excel calculation is unless you know what is in each cell ?

Customer: replied 3 years ago.

True. I am no expert, but I have managed to build myself a fairly usable system that gives me all kinds of info I need. This one got dumped on me so bare with me.

Assume I have the following fields: Name, DOH, Store, Class1, Class2, Class3, Class4, Class5. (there are actually 35 fields I need in the calculation) Class 1 and 2 are complete, Class 3,4,5 have not been taken. What would you put in the query to calculate the percentage of classes that are complete? These fields are left blank until fully completed. I do understand queries, I've concocted many, (probably not the most efficient) but I get what I need. I'm just not sure how to get to the percentage completed in this scenario. Hope this helps. thanks!

Expert:  flopcat98 replied 3 years ago.

add 3 fields one for classes taken and 1 for classes completed and one for perctent complete

the calculation(s) would say If class1 <> " " classes taken = classes taken plus 1 if class1 = "completed" classes completed = classes completed plus 1 ( 35 times for once for each class* )

it might be a bit easier if you marked the class field started and completed then you could test for started rather than not blank

 

* this is where it is much better to have a relational database with a seperate record for each class. then you only have to do the calculation once and apply it to all the records.

 

then percentage is classes completed / classes taken

 

 

flopcat98, Consultant
Category: Microsoft Office
Satisfied Customers: 7600
Experience: degree in comp sci. 20+ years install work with networks for small and large companies
flopcat98 and other Microsoft Office Specialists are ready to help you
Customer: replied 3 years ago.
Haven't gotten it to work yet, but I thanks for the effort.
Expert:  flopcat98 replied 3 years ago.

your welcome. Other than changing the entire database structure to two related databases which i do recomend as the best way in the long run, What you might consider doing is

making those numeric fields.

0 in both fields if class wasnt taken

1. in class if it was taken

1 in finished if it was completed

 

* for each of the 35 fields of course

 

then just add all of the taken fields and add all the completed fields and divide one by the other so you get something like

( completed1 +completed2 ..... + completed35 ) / ( taken1 + taken2 ....+ taken35 )

Customer: replied 3 years ago.
Yeah, I might have to think about how to change the whole structure. Will be a lot of work to get the data back in. I'll keep working at it! Thanks for the assist.
Customer: replied 3 years ago.

Ok, so I have decided to bite the bullet and redo this whole thing. So in order to be able to get the percentage of classes completed for each employee I will need to have an employee table and a course table and link the 2. Is this correct? The bottom line is, I just need to know the percentage of classes that are completed for each individual employee and the percentage completed per store. I don't need to know if they are started, just that they finished. there will be other fields not important to the percentage. So my fields will be employee name, course name, date completed (for each course). Please explain how to set this up properly to be able to have this info and obtain the percentages I am looking for. thanks again!

Expert:  flopcat98 replied 3 years ago.

You will probably want one table for stores it will have the store number that it is indexed on and any other relevant data for the store name address etc.

 

You will have one table for employees. Including an employee name or number that it will be indexed on and any other relevant information for the employee home address salary whatever is important for any jobs you will be using the data base for. Maybe very few if its only for the classes taken. But it will need to include a field for the store number where they work.

 

Then you have the course data. You may or may not want to have a heading table with a course number that it will be indexed on and a course description etc

 

then you will have the actual data table that includes fields for the course number and the employee number ( just for convenience another field called started with a 1 in it )and a field for whether it was completed or not with a 1 if it was completed as the data

 

To create the report

you link the course data table to the course header table by the course number ( optional )

you link the course data table to the employee table by the employee number in both

you link the employee table to the store table by the store number field in both tables

 

then you create the report

you can easily list out all courses taken all course completed. sort it by store or by employee or even list the stores and employees by course number

 

for the report that you want you would add the completed field and the taken field and subtotal on it them with a subtotal field for one divided by the other

sorting by store. or employee and store if you so desire.

then if you dont want to see the individual courses you can set that portion of the report not to print. then you only see the percentage by store.

 

 

Customer: replied 3 years ago.
I am using my existing employee to database to do this so all I have added is a new table for the courses consisting of the employee # as the primary key to link the two tables the course name with a data type as a date field because I need to track the completion date. So other than that, you are saying I need to add another field for each course that is just a number so the calculation is done on that field? If I do that, is there a way to have that field automatically fill in with that number if a date is entered in the other course description field?
Expert:  flopcat98 replied 3 years ago.
You can do that. a querry should be able to turn it into a 1 if there is a date in another field. tecnically you can do without that field and just use some sort of an if statement as part of the calculation or use a variable rather than a field to be added to. But i am trying to make it as easy as possible and the extra field makes writing the equation and report easier.
Customer: replied 3 years ago.

 

I would prefer not to need to add that extra field so if you want to tell me exactly how to write the expression and were to put it, that would be great. Don't worry, I promise to pay more when I get this to work!

Expert:  flopcat98 replied 3 years ago.
I would have to build a table in access and write a report just to make sure i had it exactly right. I can do that but i cant get to it tonight.
Customer: replied 3 years ago.
that's fine. My intent is to make a form that pulls the employee data from the employee file and the course data from the course file. It will allow me to easily fill in the dates the courses are completed. I don't need help with that, just wanted you to understand how I will be using it. I would love to be able to see a field on the form that shows me the percentage complete. Let me know if that is possible as well. Then I will need to write a series of reports. I'll let you know if I need any help with that after we get the percentage issue worked. I'll be looking forward to your response when you can. thanks again!
Expert:  flopcat98 replied 3 years ago.

well that might be possible but you would have to tell me what pct ? for the course in total for the employee the store etc ?

 

Probably the easiest way to do that would be to keep a running total in each header table. each store would show the total completion rate for the store regardless of which employee and which course each employee the total regardless of which courses they were each course the total for the course regardless of which employee or store. Otherwise you have a question of what pct you want when you are entering an employee and a course

Customer: replied 3 years ago.

No the form would apply to each employee only so I am looking to see a field on the individual employee form that shows the percentage of courses they have completed.

Expert:  flopcat98 replied 3 years ago.
ok i am running out but i will see what i can do for you late tonight or tomorow.
Customer: replied 3 years ago.
great tomorrow is fine. thx
Expert:  flopcat98 replied 3 years ago.
ok i will get back to you tomorow.

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional
 
 
 

Related Microsoft Office Questions

Chat Now With A Microsoft Office Technician
flopcat98
flopcat98
114 Satisfied Customers
degree in comp sci. 20+ years install work with networks for small and large companies