Unanswered: Design of queries to produce productivity reports
I'm a manager in a large medical records department and want to use Access to track my employees' productivity as well as quality errors. I'm using Access 2003 primarily (laptop actually has Access 2007 or 2010, but I'm saving all files in 2003 format). We've been doing this in Excel for a long time, and I'm trying to find a more robust and professional-looking solution. I did take some Access '03 classes about 2-1/2 years ago and still have the books, but basically my knowledge level is fairly low and I'm continually frustrated as I want Access to handle data just like Excel does.
What I have so far:
1 - One with employee list and information (using numeric employeeID as primary key)
2 - One with dated records and several columns containing various productivity measures (I originally made date primary key, with the idea that each employee would have his/her own table for data. But once I figured out how to make my query ask for a date range and employeeID I thought it would be better to have all employees in 1 table, so I changed the primary key to an autonumber).
3 - A very simple table with a list of productivity standards and what the numeric standard is.
I also have a series of 3 queries to:
1 - Pull data from productivity table. I used [Enter EmployeeID:] as well as Between [Enter Begin Date:] And [Enter End Date:] in the criteria fields so that the query will ask the user (me) for those criteria before running.
I also added calculated fields that take each of the productivity measures and perform a calculation to see how much time is accounted for by a given activity using productivity standards. (I.e., if the standard for paper shuffling is 1000 pages per hour, then the data of 500 pages accounts for 0.5 hrs of work).
2 - The second query is based on the first and takes all the productivity totals from the calculated fields of query 1, adds them together and divides by the number of hours worked to find the productivity percentage for that day.
3 - The third query (based on the previous two) takes all the daily percentages and averages them.
So - the queries: All the calculating I indicate above was done in Excel with simple formulas. For instance, the work done in all the calculated fields in query1 was done in Excel with a single SUMPRODUCT expression. I just couldn't figure out how to do that in Access as I didn't know how to refer to the values in previous columns in the query. Hence, I have a calculated field for each value I'm looking for. It's seems rather inelegant, and I'm sure there's a better way, but I do get the result. Problem is ...
Now I'm just stumped. I have to use all 3 queries to build the report. Everything I've tried through the wizard looks awful. For instance, I only want the overall average to appear once, but on the report it appears next to every entry. I'm lost on the whole 'grouping' thing.
What I really want to accomplish:
1. I would like a form that would ask for employeeID and date range, and will then run a query to pull the requested information much as query1 does.
2. I would also like a form for data entry that would allow me to use a drop-down list of the employees' names. I'd ideally like the form not to allow any records to be changed or deleted, only new records added (if I need to change or delete records, I'll go directly to the table). Actually, I think I've got this part figured out. It just needs to look a little better.
3. Reports. I thought the report would be the easy part after I finally got all the calculating done. I finally got Access to produce the data I need, but if I can't get a good-looking report out of it I may as well go back to Excel.
I know that's a lot of info, and I certainly don't expect all the answers to come rolling in. I'm the kind of person who can tinker and probably eventually come up with something that works. However, I feel that I need to understand what I'm doing in case I ever need to go back in and make changes/redesigns. Maybe we could start with the following specific questions:
1 - How can I use one item in a record to refer to another? In other words, I'd like my form/query to ideally ask for the employee NAME. But since the employeeID (number) is the primary key and unique, I'm using that. I'd like a drop-down list with employee names (which I think I have figured out). But I'd like the form/query to use the employeeID associated with the picked name in order to find the data. Same goes for reports: Since employee ID is the criteria, it's putting a number at the top of the report. Whereas a name would be more meaningful.
2 - Is there anything innately "wrong" with the way I'm using 3 queries to get the result I want? Will it cause me problems down the road? Or is it just plain sloppy?
3 - You may notice that I did not use the 3rd table (containing the productivity standards) at all. My idea was to have a table containing the standards and to pull those numeric standards into the formulas in the calculated fields of query1 to perform the calculations. That way, if/when the standards change I could just update the table containing them and all the other queries could work from there and wouldn't need to be updated. But I couldn't figure it out, so I just have ten different calculated fields in query1 which would need to be individually updated should productivity standards change. This seems related to question #1 above. You see - I'm still trapped in Excel-land. I'm always wanting to "refer" to something.
I hope this hasn't been information overload. Really, any help at all is of course appreciated.