Is Access right for what I need?

[Branchard]

Hi Rpeare -



I have looked over the attachment you posted for me. I am blown away actually It looks amazing! And I'm totally inspired to keep at this and learn how to do all this amazing stuff with Access I really want to crack it now!

I do have one question or concern about one small part of it. I see that you put in a last row for 5/31/13 - which in my work world would actually be counted for the June report and not the May report. But just for a moment I'm going to pretend like I need it on this report. The issue I have is with the B2Med & B2Life columns. For an employee to accurately track their own work they can't turn in a B2Med and a B2Life on the same day because they wouldn't be able to determine which one would be sampled and the B2Med gets 3 credits and the B2Life gets 2 credits. So for our benefit, we don't hand those in on the same day if at all possible.

We actually don't know which task we turn in will be sampled at all. The only way for us to know this is at the end of the month we could ask for a reconciliation report from the Quality department - but only if we feel there is a discrepancy. This report would show us which task was sampled for each day. They don't usually just send us the reports of their own free will We have to ask for them - and it seems like it takes an act of congress to get them! What the Quality department sends us each month is just a basic little chart like I had at the bottom of the spreadsheet. It is that simple little chart and they take those numbers to determine what our quality score is for the month.

Again - this database creation (if it can be done accurately) is strictly for the employees benefit of tracking our work and knowing exactly what credits we should get based on the number of tasks we turn in and the number of samples that should be pulled from those tasks. The Quality department uses their own database to pull their samples but several of the employees (including myself) have found it to be inaccurate. That is why I created the spreadsheet to track my work and know what i should be getting versus what they were sending me in a monthly report. As of right now, there really isn't anything being done to correct their database and the issues. But I'm not going to stand by and let them give me inaccurate scores because my job and bonuses depend on my quality score.

So my hope is that I can have an accurate database to track the work - and that it be simple enough that even a novice can be shown how to enter their daily work and then be able to run some kind of report from it that shows them how many credits they should get for the number of samples taken of that work.

I guess ultimately I would like a report to show them how many tasks they submitted for each category, how many samples were taken for each category, and how many credits that should give them for each category (credits are only given on the work sampled - not the number of tasks turned in). Then I would like it to show them their quality score - which is determined by taking the total number of credits - subtract the the number of errors - then dividing by the total number of credits.

It needs to be a simple interface for others to use because we have a lot of technology challenged people on our team! hahaha... I'm by no means advanced but I do grasp things a little quicker than most of them.

And yes, I think it's just a matter of know the formulas to plug in here and there.
Would it make it easier if they had to enter 0 for the tasks that they don't have any work for - on a daily basis?

[Branchard]

Maybe I should add that when I looked at the database and did a date range, I used 4/30/13 to 6/1/13 - even though I should have stopped with 5/30/13. But that is why I saw the 5/31/13 line and came up with the questions about the B2Med and B2Life issue with the credits and not turning it in on the same day.

[Branchard]

Rpeare - What is the "PK" field?

[rpeare]

PK is the primary key field. All tables should have a unique identifier. It helps you pick out one record among many if you need to perform VBA/SQL code on it.

For instance, let's say that you have a list of people

LastName  FirstName
Smith     John
Smith     John
Smith     John

When I say I'm referring to john smith... which one is it? If you have table with a normalized structure and a PK you would have

PK LastName  FirstName
1  Smith     John
2  Smith     John
3  Smith     John

Then for any activity for the second john smith all of your tables with person activity would have that PK so it's easy to pick out which john smith we're talking about.


one more thing as I stated before, I would try to get away from 'accurate' or 'inaccurate' for the quality department's numbers because I don't think you know exactly how they are working their formula. Unless you completely understand their process and are copying the process as it *should* be I can almost guarantee you they are using a true sampling process where they choose random records from within the month and base your score on that rather than an average of all scores the way you are doing it.

[Branchard]

Ok. The primary key makes a lot of sense now. I am still going over the file you sent and trying to understand parts and stuff, so just bear with me a bit But so far it looks awesome. I just have to figure out how to "use" it. If I wanted someone else on my team to use this, is there a way to have a simple interface form for them to enter their info in? And then what about coming up with a report that shows them what the need to know without seeing all the other stuff they don't need to see?

As far as the Quality department here in our division, this is how things have been explained to me - because I've asked a LOT of questions about the database they use to grade the work. There is no need to even worry about the Stoploss category or the NP categories because those are checked at 100% for the month. The RC category is 25% for the month - so while I don't know exactly which ones they will sample - I can know how many should be sampled and how many credits that would give me. And the same goes with the BI category at 50%.

The B2/BC/BN category used to be sampled at 50% per month but for some reason when they created their database they changed it to 50% per day. Again, I wouldn't know exactly what group they are checking (without asking them for a report) but I can calculate how many samples should be taken daily from what I turn in.

They take all the credits you are given for your sampled work and they subtract the # of errors and then divide by the total number checked to give you a monthly score.

The reconciliation reports I have been given tell you every piece of work sampled for the month - and which day it was reviewed for. This is how I discovered that it wasn't sampling correctly all the time. During the month of May, the BC/B2/BN category sampled correctly almost every single day for how many pieces of work I turned in. But there were a couple of days that it didn't sample 50% for the day. And on one day it didn't sample anything at all - and I turned in work. I found these same type of discrepancies for Feb, March, and April. And they say they are looking into the problems. But the things I brought to their attention they say are inaccurate and they are working to correct the issues and see why the database didn't pull those things.

Last month I turned in 26 GS tasks - which would fall under the Stoploss - and should be checked at 100%. But only 4 of them were pulled by their database to be checked. So that is a big issue!

And I've also told management that if I am wrong about my discrepancies then please explain the logic of the database to me so that I can amend my spreadsheet and the way I calculate things. But so far I am being told that my calculations are in fact correct. And in my opinion ... the database they are using ... I don't think it's setup correctly with their rules and stuff because the lady in the Quality department that deals with the I.S. people that maintain it - she doesn't know what she is talking about. So I don't think she is relaying information to them correctly on how the rules need to be setup for the sampling procedures. But that's my opinion

[Branchard]

If someone were to have multiple entries for the same day, does Access have the ability to lump them all into 1 entry? in order to formulate the correct # of samples that should be taken?

The tables seem easy enough to understand. I think it's going to be everything else that I'm not comfortable with right now. I've got some learning to do! lol

[rpeare]

The database can be used by multiple people the way I sent it to you. All you have to do is choose a new person/date range and all the records will be associated with that person. If you're in an office of say more than 2 - 5 concurrent users it would probably be better to either make smaller teams of people use the same database or give everyone their own copy (if you want a centralized location for the data, you probably want to split it into front end/back end and distribute front ends separately). As far as showing all the records for a single day as a single record, yes you can do that. It's called a summary or aggregate query. On your query toolbar there should be a sigma (looks like a capital E) that is your summary function. Be aware that you can't use a summary query for data entry though. The form I sent you is based on a table, if you base it on a summary query you'd have to significantly change the design to pick a day (as well as a person) to edit.

[Branchard]

so as it is right now, i could add my name to the employee ID table, right?
and then i could input my numbers with my id...
then i go to the first one under forms and pick my name and put in a date range to see my work... is that correct?

i'm still learning with all this... so don't you go far! lol

[rpeare]

The form is meant for data entry
You don't have to do the data entry on the table at all, the form can handle it. Every time you add a new record the form will order them by date from oldest to most recent (top to bottom).

I didn't make a form for adding a new employee though so you WOULD have to enter your name in the employee table. Then just open the form select yourself, put in a date range (nothing will come up because there's no data for you) then you just start typing in your data.