Employee Schedule

[steed281]

I want to migrate my current excel calendar into my Access Database.

The database already has an Employee Table which contains all the Employees in my department.

tblEmployees (Employee Number(PK))(Employee Name)(Title Title(linked to tblJobTitles))(E-Mail Address)(Work Cell)(Personal Number)(Hire Date)(Termination Date)

I have a query that filters the Employees by position. One of the queries filters the employees by Field Staff.

qryEmployees From tblEmployees(Termination Date Criteria = IS NULL)
qryTechs from qryEmployees (Job Title Criteria = "Trainee" or "Installer" or "Lead Technician" or "Senior Technician")

I want the calendar FORM to auto populate the names of all Current Field Staff on the left as seen in the spreadsheet. I want the dates and day to auto populate.

I want the tasks cells to be a Combo Box that choses from an existing Project Table and I want it to allow multiple selections so I can assign multiple projects to a technician on any given day if required.

tblProjects (Sales Order Number (PK)(Project Name)...........

I don't want to have to run a report to view the information as a calendar. I realize I may have to do subforms to split the weeks.

The first hurdle I am having it creating the table I need to store assigned tasks. I know I will need multiple relation tables. Any advice to start would be appreciated.



Thank you.

[burrina]

Is this something you have already built, the Calendar form I mean? They can be quite code intensive to setup. If I understand you correctly.
If not then please explain a little more. You can quite easily import your excel data into Access, but from there you will need to design your other tables and link correctly.

[steed281]

I do not have the form built yet. I started on the form but all I have so far is a basic form with two combo boxes selecting month and year. My goal is to have the Month and Year be selectable via combo (drop-down) and have the details change for that month. The form will most likely have to pull data from multiple queries. I think I will a query for each technician with the combo box filtering the query by the month and year chosen.

[burrina]

So, you have built a search form? If so then I guess you know to add the criteria in your qry?

[steed281]

Yes. That is where I am stuck right now. I can't get the criteria to work correctly with the dropdowns.

[burrina]

Typically, once you have your search form in place as desired you also edit the query the combo is based on and enter the criteria.
Example: cbocustomer as a combo box on your form. In your qry (Record source for the combo) you enter under Customer (Criteria) Forms!yourformname!cbocustomer

HTH