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, Consultant
Category: Microsoft Office
Satisfied Customers: 9076
Experience:  degree in comp sci. 20+ years install work with networks for small and large companies
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?
Submitted: 5 years ago.
Category: Microsoft Office
Expert:  flopcat98 replied 5 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 5 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 5 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 5 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 5 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 and 4 other Microsoft Office Specialists are ready to help you
Customer: replied 5 years ago.
Haven't gotten it to work yet, but I thanks for the effort.
Expert:  flopcat98 replied 5 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 5 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 5 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 5 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 5 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 5 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 5 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 5 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 5 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 5 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 5 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 5 years ago.
ok i am running out but i will see what i can do for you late tonight or tomorow.
Customer: replied 5 years ago.
great tomorrow is fine. thx
Expert:  flopcat98 replied 5 years ago.
ok i will get back to you tomorow.