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
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.
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 ?
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!
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
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 )
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 botXXX XXne 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!
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.
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!
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
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.