Trying to get a Percentage

[Windsor302]

Hey guys, not sure of the best way to go about this. I have several workers assigned to one job. While on the job their tasks are randomly tested. There is criteria that states each worker must be tested a certain amount. How can I calculate the percentage he or she has been tested based on the tasks completed. Thanks

 

[Beetle]

It would help if you could provide a few more specifics.

You want the tasks completed by a single worker as a percentage of all tasks assigned to all workers for a single job?

As a percentage of all tasks assigned to only that worker for a single job?

As a percentage of all tasks assigned to only that worker for all jobs?

Something else?

Also, some info about your basic table structure may be helpful, at least for the tables that would be involved in determining this.

 

[Windsor302]

Sorry, I will try to explain it better, lets say each worker performs 100 tasks. 20% of those tasks have to tested. The database is populated as the job progresses and I want a way to check the percentage that each worker has reached at that time. Does that make sense? Thanks for your help.

 

[Beetle]

Well, generally speaking this is just a simple matter of dividing the number of tasks completed by the number of tasks required, which probably isn't anything you don't already know, but that's about as specific as I can be since I still know next to nothing about the basic structure of your database.

Does each job have a predetermined number of tasks? Do all jobs require the same overall number of tasks, or does it vary? If it varies, then you must have a field where you store the total number of required tasks for each job, or you have a table that lists all tasks by JobID? If the latter, then determining the total number of required tasks would be a simple count from that table.

 

[Windsor302]

I believe it is the latter, how do i make it count by individual worker?

 

[Beetle]

I believe it is the latter

Meaning you're not sure, or what? Was the application designed by someone else and you're unsure of the table structure?

how do i make it count by individual worker?

I don't know because, as I said before, I know essentially nothing about the structure of your database.

If you want to upload a copy with any sensitive data removed I can look at it. If you do, make sure it is in .mdb (A2003 or older) format.

 

[Windsor302]

how do I save it into an older version? I am using Access 2007

 

[Windsor302]

I cannot save it to an earlier version, the program tells me I am using features that require my version

 

[Beetle]

Try creating a blank database and impoting the tables only, again with any sensitive data removed, into it and save/upload that as an older file version. If I can get an idea of your table structure I may be able to give you an idea of how to proceed. If that won't work, then you can upload it as an A2007 (.accdb) file and someone else may pick up on this thread who will be able to open it.

 

[Windsor302]

Let me know if this worked

 

[Beetle]

It worked, but the sample file only has one table that looks like the following;

Is there only one table in your actual application, or did you leave some out of the sample? If this is (hopefully not) the only table in your app, then which fields are supposed to represent a completed task, a test, etc.?

 

[Windsor302]

I have many other table and fields I simply copied the more pertinent ones to this sample. The Date Completed represents the task and the MT No, Date and Accept all represent that it has been tested.

 

[Beetle]

I'm not sure if this is what you want or not, but I'm uploading a modified copy of your database. There is one query that counts the tasks completed and tested, and another query that calculates the percent tested, by worker_id. That's about all I can offer since all I have to work with is the one table you provided.

 

[Windsor302]

Thank you very much, this looks like it is exactly what I need. Can you explain how to do this? I have several different tables I will need to apply this too. I am in the midst of compiling all the data. Thanks again.

 

[Beetle]

The first query in the example file is a Totals query that uses Group By for the Worker_ID and Count for the Date_Completed field. It uses criteria for both of these columns to eliminate Nulls. Then there is a third field that uses DCount to count the number of rows where the Worker_ID matches the Worker_ID from the query and the MT_Accept field is true. These two fields are named TasksCompleted and TasksTested

The second query is based on the first and simply adds another field that divides TasksTested by TasksCompleted and displays it as a percentage.

Again, this is all based on the very limited amount of information you've provided about the structure of your database. Whether this method will be applicable in your real world database, I have no idea. You will likely need to involve more than one table in whatever queries you create.

 

[Windsor302]

I really appreciate the time you have put into this and id like to say Thanks. I tried mimicking your querys with a different table but in the last column in the 2nd query the percentages wont show up properly. I have not yet entered the data that will actually use this percentage feature I am merely making sure it can be done for now. Would you be willing to help me out when I get to the point of actually using this? And do you have an idea why it might not have worked when i tried mimicking it? Thanks again.

 

[Beetle]

in the last column in the 2nd query the percentages wont show up properly.

In query design view, right click on the small gray bar right above the field row for this column, go to Properties, then Format and set the Format to Percent.

 

[Windsor302]

Perfect! Thanks

 

[Windsor302]

hey there, so i have started entering data into this table, there is a new criteria that needs to be included in the percentage query, each MT_No/Date is assigned a type (qualifier 1, qualifier 2, original, repair ect...) the first 2 (qualifiers) do not count towards the percentages, how could i add that so the query uses this. Thanks

 

[Beetle]

Can you be more specific about what you mean by "do not count toward the percentages"?

Is it;

1) If it has qualifier 1 or 2 in the type then it task should not be counted as part of either the Completed tasks or the Tested tasks.

2) If it has qualifier 1 or 2 then it should be counted as part of the Completed tasks but not part of the Tested tasks.