Help!!! Please, I have been pulling my hair out for a week now. I and building a database that tracks productivity. Basically I have a table of Employees and a table with fields that list the items we are tracking. To make it simple, the Productivity table has fields of Date, received, and completed. I need to be able to determine a pending number for each employee on a daily basis. Example: susie has 5 issues, she received 2, and completed 4. she will end the day and start the next day with 3. Sounds simple, but I cannot figure it out. Of course, I am rookie at access. Any assistance is greatly appreciated.
i.e., have an employee form then make another form for issues. now, put the issues form into the employee form (this makes issues a subform). access ought to prompt you automatically which fields are the parent/child fields. in this instance it ought to be whatever you have called your employee key (i expect EmployeeID).
so whenever you scroll through the employees you can see all the issues that are associated with them.
now, to see ONLY those that are still pending, go to the recordsource field for the subform, click on the elipsis ("...") and you should have a query there (access will prompt you for one if you don't, and you should tell it to change to a query).
in your query, ask access to filter out any records that are not complete. i assume you have a yes/no type for the "complete" field? if so, i think "-1" is true and "0" is false. make the 'criteria' of the 'complete' field: "0" (without the quotes).
check your query first, then close it and access will update the record source for the subform.
now when you scroll through your employee records, your 'issues' subform will only display those pending
Maybe conceptualize the productivity table as recording productivity events of two types, assigned and completed. Assigned has a value of 1 and completed has a value of -1. Consider a table structure like ...
Code:
[B]tProductivityEvent[/B]
ProductivityEventID (PK)
IssueID (FK)
EmployeeID (FK)
Date
Value
So for a particular employee for a particular issue you create a ProductivityEvent on a given date--and a given time even--and that record has a Value of 1 (Assigned). When the employee completes the task you create another ProductivityEvent for that issue for that employee with the completion date/time and record a Value of -1 (Completed). Now, to calculate an unfinished task count you sum the Value field for the associated EmployeeID or IssueID using SQL like ....
Code:
SELECT Sum(Value) As TasksInProgress FROM tProductivityEvent
WHERE Date < Now()
AND EmployeeID = 7
This returns the number of 'In Progress' tasks for employee 7 up to any given date/time; now, tomorrow, last week, whenever. You can also determine 'In Progress' task counts for issues in the way. And each ProductivityEvent records a distinct date/time so you can calculate how long it takes employees to complete tasks, or how long tasks take for a particular issue.
Makes sense?
Cheers,
or instead of the value field and adding many events to one procedure, you can have one record for each 'issue', a start date field and and end date field. so all you have to do to determine if an issue has not been completed, you just filter for any end dates that have no value yet. this can be more meaningful than a boolean "completed" (editL because you can determine how long ago it was completed).
also, "Date" is a reserved word and shouldn't be used on its own to name anything. if you want, you can use StartDate or EndDate, but "Date" will confuse both you and access down the track...
Maybe conceptualize the productivity table as recording productivity events of two types, assigned and completed. Assigned has a value of 1 and completed has a value of -1. Consider a table structure like ...
Code:
[B]tProductivityEvent[/B]
ProductivityEventID (PK)
IssueID (FK)
EmployeeID (FK)
Date
Value
So for a particular employee for a particular issue you create a ProductivityEvent on a given date--and a given time even--and that record has a Value of 1 (Assigned). When the employee completes the task you create another ProductivityEvent for that issue for that employee with the completion date/time and record a Value of -1 (Completed). Now, to calculate an unfinished task count you sum the Value field for the associated EmployeeID or IssueID using SQL like ....
Code:
SELECT Sum(Value) As TasksInProgress FROM tProductivityEvent
WHERE Date < Now()
AND EmployeeID = 7
This returns the number of 'In Progress' tasks for employee 7 up to any given date/time; now, tomorrow, last week, whenever. You can also determine 'In Progress' task counts for issues in the way. And each ProductivityEvent records a distinct date/time so you can calculate how long it takes employees to complete tasks, or how long tasks take for a particular issue.
Makes sense?
Cheers,
Thanks for the suggestion. I will try this. However, I failed to mention that the issues carry categories, so my main table actually had more fields than what I originally said. I have "written", "verbals", and CBR's as issue types and then categories of Normal and Furniture. I think I might be able to create a form and use check boxes instead of entering the number in the written, verbal or cbr field. What do you think?
Thanks for the suggestion. I will try this. However, I failed to mention that the issues carry categories, so my main table actually had more fields than what I originally said. I have "written", "verbals", and CBR's as issue types and then categories of Normal and Furniture. I think I might be able to create a form and use check boxes instead of entering the number in the written, verbal or cbr field. What do you think?
to explain 2: once you do (1), then (2) becomes easier and more managable. you can group by categories and sum the results similar to your excel spreadsheet - crosstab query ought to do it easy, but you must do (1) before you can do what you want easily with (2).
in your case, with knowing only what has been presented, i would suggest this table structure:
form this setup, you can do a lot more than inflexible checkboxes and de-normalised data.
edit: tblCategories is where you would put the values "Verbals" and "Presidentials" don't know what "CRLS" or "FBC S" is, so i can't make any recommendations as to how or where they would fit into any structure.
Thanks Wiklendt, you have been absolutely wonderful. This does make sense and it is easier on my part. However, I am moving this from an excel spreadsheet to access to simplify the process for the people that enter the data. Today, on our spreadsheet, they just tab to the field and enter the number received, completed and how many completed that were furniture issues (FBCS). In access, I am having trouble creating a form that allows them to use one sheet. I have created forms in every way possible, but cannot seem to find a way to enter the date only once, and then enter the received, completed and FBCS completed without having to open several different forms. Is there a way to create a form that has all the associates names on the form and a place to enter the number they received and completed? Also, enter the date only once. So basically, one form per day. Again, you have been wonderful and appreciate any assistance you provide.
How is 'pending' different than 'received' for a particular employee? Is this a significant distinction? What if Sally gets all her work done--everything is completed for her--and Joe still has 5 issues pending. Can you move Joe's pendings to Sally's received? If so, then the fact that a particular issue is pending for a particular employee is not very 'hard' data and might not merit a significant distinction in your system.
But you can define an 'Issue' as having as many properties as you want, but it's received or completed status does not impact these other properties. Say you have an issue about a 1) chair with a color of 2) red and the seat is 3) foam and it was delivered to the shop by 4) Bob in his 5) green 6) Ford. Each numbered thing, above, is data you might store about an issue. None of these data points have any impact on whether the issue is 'completed'. So you can construct a record that tracks all the data you want ....
tIssue 'provides a complete definition of an issue
IssueID (PK)
FurnitureTypeID (FK) 'chair
DeliveredByEmployeeID (FK) 'Bob
IssueTypeID (FK) 'verbal, crl
VehicleID (FK) 'Ford
Color
tIssueStatus 'tracks dated events that might occur for the issue
IssueStatusID (PK)
EmployeeID (FK) 'if different employees might handle different parts of the process
IssueID (FK)
Date
Status
Gotta split. Lemme know if this is useful, etc...
Cheers,
i agree with lagbolt. in addition, excel is not access, and access is not excel. you won't be able to do things identically between the two. you'll have to come up with a new 'system' - people can be trained, they must atune to the times and move with progress.
my suggestion is to set up a main and subform as described above. get employees (or managers) to enter who received what issues, who complete issues etc. then, make a query which draws all that info together and presents it in a similar way you how you have your excel sheet at the moment (or even a better way!) at any point in the day you can call that query to show you the data. it doesn't have to be a form, and you won't have to double-enter any data - the data will already be there, you just have to make the one query which will be called each time you want the issues counted.
if you want to be able to just enter the date once, you can have access do this automatically. there are various ways, but you'll need to define where you are going to use "today's" date, if indeed that is the date you want to use, or if you want to set a particular date, and use that globally within the database - different methods will use different code/setup.
one question, which i don't think you've clarified - are you storing various bits of information about issues at all, or are you ONLY wanting to count issues receiving, and issues completed? this changes things too - not to mention, are you going to want to be able to track issue details in future? you're best to set that all up now while you have the motivation...
i agree with lagbolt. in addition, excel is not access, and access is not excel. you won't be able to do things identically between the two. you'll have to come up with a new 'system' - people can be trained, they must atune to the times and move with progress.
my suggestion is to set up a main and subform as described above. get employees (or managers) to enter who received what issues, who complete issues etc. then, make a query which draws all that info together and presents it in a similar way you how you have your excel sheet at the moment (or even a better way!) at any point in the day you can call that query to show you the data. it doesn't have to be a form, and you won't have to double-enter any data - the data will already be there, you just have to make the one query which will be called each time you want the issues counted.
if you want to be able to just enter the date once, you can have access do this automatically. there are various ways, but you'll need to define where you are going to use "today's" date, if indeed that is the date you want to use, or if you want to set a particular date, and use that globally within the database - different methods will use different code/setup.
one question, which i don't think you've clarified - are you storing various bits of information about issues at all, or are you ONLY wanting to count issues receiving, and issues completed? this changes things too - not to mention, are you going to want to be able to track issue details in future? you're best to set that all up now while you have the motivation...
Thanks for your responses and your assistance. This database will basically just count the issues received via written letter (true Pres), phone calls (verbal) and email (CRL). We also track the number of furniture(FBCS) issues completed as the standard for those issues is more forgiving. I think what I have is going to work, except for the form. I would like it to be more simple for the Auditors who enter the data. Currently, I have to enter the date for every associate, and I would like to just enter it once. I have tried as you suggested and insert a form into the main form, but have not been successful. I got one to work, but still had to enter the date for every associate.
This database is not live yet. I made a replica so that I could post it on this forum. That is the only reason.
there's no need to post replica's on the forum. instead, compress the file (zip) and post that. replica's have an actual real-world use (which i'm not familiar with) BUT it would be easier for us to help if you posted just the normal version - mainly because your replica is read only (and in your form i can't even view the design), so i can only post back with suggestions and gu-estimates on how to make something work rather than showing you in your own database.
in your form, to make something come up with today's date, put "=Date()" in the "default value" property of the control (a control to a form is what a field is to a table).
Thanks for the info on that Replica thing. I deleted that post so it is no longer on the forum. I have attached a copy of my database. I changed the names of the employees though. I also entered "=Date()" into the "Default Value" of the form and it works. Thanks. I worked on a report today, but will probably change it. Trying to total the numbers at the bottom, but having problems. Book states I need to group the items 1st. Will keep trying. Any suggestions are greatly appreciated.
(unfortunately, your new attachment is still a replica...)
FYI: using a parameter [Enter Beginning Date] and [Enter End Date] in your query is not the preferred method (i know the books show you to do this, but let me show you a more robust method):
create a form ("frmDatePicker") with two unbound dropdowns.
name the first combo as cmbDateStart and the second as cmbDateEnd.
now for the control source in each, click on the elipsis "..." and make the source of your dropdown the field where you store your start date, the same in the second dropdown (it's ok if there is only one field. in that case, use that one as the source to both your dropdowns)
now, in your query, instead of [enter start date], type in
Code:
>= Forms!frmDatePicker.cmbDateStart
(which means "return all records with a start date that is greater than or equal to the date chosen in the control called "cmbDateStart" on the form called "frmDatePicker")
and for the [enter end date]:
Code:
<= Forms!frmDatePicker.cmbDateEnd
for a parameter where you want to return only one date, leave off the "<=" and ">=" bits.
if you have just one field to find a start and end date, then you would use the "Between" operand:
Code:
Between forms!frmDatePicker.cmbDateStart And forms!frmDatePicker.cmdDateEnd
it also helps to ensure that your controls (on the form and the report) as set to "short date" format, for easy reading, unless you really need the "time" details also.
this form-based date method is better because the user may not know what dates are available to choose from, and it's frustrating to choose dates and discover no records are returned because the dates you chose are two years prior to data collection.
the other frustration the form-based date method prevents, is the user entering the date with the wrong format or wrong "directionality", then having access error out and the user having to start all over again (e.g., "2009-12-09" or "12/09/2009" instead of "12/09/2009") - having the combo boxes with the dates 'pre-entered', as it were, makes life easier for user and developer alike (easier for developer to test that everything works).
Good morning. I found where to remove the replica on the tables, but having problems removing it from the forms. I even deleted the forms and rebuilt them and they still show as replica. Do you know how to remove it from the form?
You have repetition with an employee AND a supervisor table because a supervisor is a type of employee. I would add a SupervisorID field to the employee table which links the current record to the record of the supervisor. If the field is null, the Employee doesn't have a supervisor.
Also, for each record in your Issues table you have three types of issues which have exactly the same structure. In programming and data structures you always want to avoid repetition, avoid repetition, avoid repetition. The Issue table would be much more efficient if it has a structure more like ...
Code:
[B]tIssue[/B]
IssueID (PK)
EmployeeID (FK)
IssueTypeID (FK) [COLOR="Green"]' specifies whether the issue is TP, Verbal or CRL[/COLOR]
IssueDate
Received
Completed
BCSCompleted
And if you use date/time types for the Received, Completed and BCSCompleted fields, then you can determine outstanding issues by counting Nulls, for instance...
Code:
SELECT COUNT(*) As PendingIssueCountByEmployee
FROM tIssue
WHERE EmployeeID = <some_ee_id>
AND Completed IS NULL
AND BCSCompleted IS NULL
You have repetition with an employee AND a supervisor table because a supervisor is a type of employee. I would add a SupervisorID field to the employee table which links the current record to the record of the supervisor. If the field is null, the Employee doesn't have a supervisor.
Also, for each record in your Issues table you have three types of issues which have exactly the same structure. In programming and data structures you always want to avoid repetition, avoid repetition, avoid repetition. The Issue table would be much more efficient if it has a structure more like ...
Code:
[B]tIssue[/B]
IssueID (PK)
EmployeeID (FK)
IssueTypeID (FK) [COLOR=Green]' specifies whether the issue is TP, Verbal or CRL[/COLOR]
IssueDate
Received
Completed
BCSCompleted
And if you use date/time types for the Received, Completed and BCSCompleted fields, then you can determine outstanding issues by counting Nulls, for instance...
Code:
SELECT COUNT(*) As PendingIssueCountByEmployee
FROM tIssue
WHERE EmployeeID = <some_ee_id>
AND Completed IS NULL
AND BCSCompleted IS NULL
