We have an existing database started by another programmer that we need finished. The database is a dispatch database meant to capture employee job detail information as it is completed in the field. The current database was built to our client's original specifications which have since changed. It currently has dispatchers who can login to the database, pull up an employee, and enter job information for that employee. There are three levels of access - employee, dispatcher, and admin. The employee gets a message that employees cannot enter the database when attempting to enter it which is what we want. The dispatcher has permission to do virtually everything except view the backend database (all changes are done via forms). Administrators have permission to do everything, including view the backend database.
The changes that the client wants are this. Basically their employees will not be calling in individually to report details of their job/task. Instead, one employee will call in to report these details for the whole group (4-5 employees). They want the dispatcher to get the details of the job/task, select the employees assigned to it, and then enter in details from a separate service ticket and invoice number for auditing purposes.
With that in mind, maybe we move this to where a dispatcher is not clicking a button to add a record for an employee but they are adding a record for the task and then assigning employees which would also create records for those employees all at once. I’m open to suggestions on how to do that. Here is the information that would be needed for that form:
1. Job Description (Text – will be 1-3 sentences long)
2. Work Type
3. Lease (will be a drop down with names of leases – refer this to a separate table with the sample values of Bradley, Davis, Connor, and Williams)
4. Well (will be a drop down with names of wells – refer this to a separate table with the sample values of 12T, 14B, 18C, 20D)
5. Team Name (will be a drop down with names of teams – refer this to a separate table with the sample values you already have for this field)
6. Start Time (Date and Time value – Dispatchers will need the ability to enter this manually)
7. End Time (Date and Time value – Dispatchers will need the ability to enter this manually)
8. Time Worked (auto-calculate this field based on start and end time)
9. Service Ticket (Text value – dispatcher will enter this)
10. Invoice number (Text value – dispatcher will enter this)
11. Time Billed (Numerical value – dispatcher will enter this)
12. Status (PENDING or COMPLETE)
13. Manager Signoff (will be a drop down with names of Managers – refer this to a separate table with some sample values which will be a user id and a name)
14. Place to add up to 5 employees to this job. They can select these employees via a dropdown that shows Employee Username and Name.
15. Logged in dispatcher’s name and ID as well as transaction times should be automatically added to these records.
My thought is that values 1 through 12 are saved into a Jobs table where each record has a transaction ID. When employees are added, records for those employees are added into a separate table with values for 1 through 8 plus the transaction ID for the Jobs record.
There should be a place in the dispatcher’s GUI where all PENDING records for the Jobs show up. The dispatcher can change values there or add end times and will have the ability to put these records in COMPLETE status. Once COMPLETE, the records no longer show up in that area.
*** To bid on this project, please make sure that you have advanced knowledge of MS Access 2007 and have a proven history of work (and references) in this area. You must be willing to start immediately (as in tonight or early tomorrow) and hopefully be able to turn this around in 24 hours or by this weekend. Ideally you would provide us with an hourly rate, the estimated time it would take to complete the work, and total estimated cost. ***