So many questions I'm not sure where to begin (1 Viewer)

[Helystra]

I'm getting a data type mismatch error.

 

[plog]

Is [Title] a text or numeric field?

 

[Helystra]

Its numeric. Here this may be easier, I'll just attach my tables here.

 

[plog]

When its numeric you can't use quote marks in your criteria. Remove them from the 10 underneath Title.

Also, I thought an employee could hold different positions with different programs? Can an FS Cook 5 in one program be an Asst Manager in a different program? If so, time for a junction table and the elimination of Title from tblEmployees.

If not, you should remove the StaffType field from tblEmployees and attach it in tblJobTitles. Actually, you probably don't need it at all because this my fix for your desire to limit the drop downs of the Admin staff in your tblSchools.

 

[Helystra]

Now that you mention it. A junction table may be the best way to go. I hadn't thought about this before but each school is "budgeted" for a specific number of each type of position. Example: Mission can have 1 manager, 1 assistant manager, 1 7 hour cook, 1 6.5 hour cook. 2 6 hour cooks and 3 5 hour cooks. I hadn't up to this point thought of using the junction table but it would great for tracking the vacancies in those positions at each school. Lets say Mission has a vacancy for one of their 6 hour cooks. If I wasn't using the junction table, I wouldn't know that. But if I set up the junction table with all the various positions within the district and then assign the employees to them, I can easily assign an employee named "vacant" to those positions that are not filled.

Great Idea.

Now the employees can't hold more than one position for their contract but for the ASM program and the Summer school program I have 1 lead cook and the rest are assistant cooks. I would just need to set up a Lead Cook and an Assistant cook in the tbljobtitles and then I could also set up all the Lead and Assistant cook positions in the junction table and then assign the employees to them as well. Solves that problem.

Now the only question I have left, may come a little later but I will also need to handle different pay rates for the employees that are assigned positions in the Summer School program.

 

[Helystra]

Well perhaps not the only question I have left. How will this new junction table work with the junction table we were planning to assign employees to programs or do I just integrate of it together in one junction table?

 

[Helystra]

Good Morning Plog. I hope you had a good weekend.

This morning I need to work on the structure for schedules, programs, employee assignments and schools. We had started talking about possibly doing away with the title field in Employees and instead using another junction table to assign the titles to the schools and/or the programs themselves and assigning employees that way. So here is the breakdown of how they relate to each other in practicality, maybe you can help me figure out how best to set it up then.

The kitchen staff job titles dictate the number of hours an Employee is contracted to work. An FSCook5 is contracted for 5 hours a day (x days a year) which affects the payroll reports that I'm going to be running later on with this database.

Each school has a budgeted number of job assignments as mentioned previously. One school has "slots" for each type of kitchen staff (see tblJobTitles) and each school has a Custodial Supervisor, LED and Principal that is assigned to the school directly regardless of the program. The Area Supervisor is also assigned to the schools directly but the except there is that there is only one Supervisor for the entire ASM (supper) program. The subs of course are not assigned to any particular school. This is all under the "contract" program. The schools also each have their own unique Schedule for the "contract" program.

Not all schools engage in the Snack, Supper and Summer school programs. Snacks use the same kitchen staff assignments as the contract program. The ASM (supper) program uses different kitchen staff and the titles are different. They have a lead cook and assistant cooks. Summer school also has lead cooks and assistant cooks but they have the added complication of having a different pay rate since it's outside the contract school year.

So how do you think is best to proceed?

 

[plog]

It sounds like you have 2 sets of employees--Admin and Kitchen. Admin staff are directly assigned to schools and Kitchen staff are directly assigned to programs. So while I would have one main table called Employees to put them all in, I would keep the EmployeeType field (Admin or Kitchen) to help differentiate the two because they will be assigned in different tables.

Admin could be directly assigned in the school table, or a junction table off of the school table. Kitchen staff will be assigned in a junction table off of the program table.

 

[Helystra]

Ok in doing that, I think I can remove the Area Supervisor, LED, Custodial Supervisor and the Principal titles from tblJobTitles and use them as the staff type designation along with kitchen staff. I'll leave the Substitute job title because they can be assigned to ASM, Concession and Summer School programs just not contract programs.

Let me get that sorted and I'll check back with you when it's done.

 

[Helystra]

I've got that part sorted out and now have my various supervisory fields looking up properly without errors to the staff type field in tblEmployees. I've detached the tblJobTitles from Employees and done away with the Title field in tbl Employees. I'm now ready to start working on how to junction all this together.

I'm trying to figure out the best way to handle the "contract" program and it's schedules along with it's various job slots and kitchen staff assignments.

I have several ideas but I'm not sure which is the best way to go.

One possibility is to have in tblprograms, 1 row (record) for each program type per school. For example Mission Academy will have 1 row for contract, 1 row for Snack, 1 row for ASM, 1 row for summer school and then any additional rows for secondary sponsors within a program.

Then make a table maybe called tblContractAssignments where i have something like
SchoolName, JobTitle, EmployeeID, ProgramType Where the school name pulls from tblSchools, JobTitle pulls from tblJobTitle and EmployeeID of course comes from tblEmployees and the ProgramType pulls from tblPrograms the "contract" entry for that school.

And maybe using the program type from tblPrograms in the tblschedules to hook that together.

suggestions?

 

[plog]

I don't think you have tblContractAssignments. You have tblProgramAssignments. In that table you would assign all employees to programs. It would have these fields:

ProgramID, JobTitle, EmployeeID

That's all you need. Because you have ProgramID you know which Program it links to and from that you can get ProgramType and School.

 

[Helystra]

Ok and how to fit the schedules into that. Do I do that like i said and have programID linked in to the Schedules table in the same way that I'm doing it with the program assignments?

 

[plog]

Schedules for what? Work schedules? Program schedules? It really depends at what level these schedules are for. Can you provide sample data?

 

[Helystra]

I have tblSchedules...the meal/bell schedules. Start time and end time for each meal period. 2 breakfasts and up to 12 lunches for the "contract" program. Then if there is a snack program or ASM program at the school I'll have one record for each of those. Then the summer school ones as well for all the summer school programs.

No two schools/programs have the same schedule.

 

[plog]

Then that sounds like its a program schedule. I would make it a junction table off of that, similar to tblProgramAssigments I mentioned prior.

Whenever you come across a new table you need, first determine what it logicaly relates to. From here on out, it will probably relate to programs.

 

[Helystra]

So when I'm setting up tblSchedules do I just use a School Field which pulls from tbl Schools and then use a junction table to assign them to the programs?

 

[plog]

Again, it depends on the level of the schedule. Does it directly relate to schools or programs? That will determine if you use a foreign key to schools or a foreign key to programs.

Schools are tied to programs in the program table, so that relationship should never be made again (directly or indirectly).

 

[Helystra]

Ok then I will use the foreign key to the programs table in my schedules. That should do what I need it to do. It will assign the program to the schedules or vice versa depending on how you look at it.

I have a niggling issue that I can't seem to figure out.

In tblPrograms I have a field named School. This pulls

RowID, School name, Campus number

from tblSchool up in the drop down box to select the appropriate school that corresponds with the Program as per our design. It then displays the school name in the field itself but "stores" the rowID from tblSchools.

Now, when creating the look up field (ProgramID) in tblSchedules I have three columns showing in the drop down for users to select the correct row from tblPrograms:

School, ProgramType, Sponsor

Problem is the School column displays the RowID from tblSchool since thats the foreign key that's used in tblProgams for the School field there.

Without being able to display the School Name (the second column of the School field look up in tblPrograms) instead of the rowID, no use is going to be able to assign the right stuff because they have no idea what the RowID # is. Is there some way I can get it to display the school name itself rather than the row ID from the School Table?

I assume i need to add some piece of code to the source row so that I can see the right colum from that field over on tblPrograms but I don't know what that code is.

It may be easier for you to see what I'm talking about if i attach the tables so here you go.

 

[plog]

Yes, but why? You keep trying to make your tables into input forms and that's not what they are for. Your tables are the foundation of your database, they aren't the upstairs guest bedroom where grandma is going to stay. Don't worry about wallpapering the cement in the basement, just make sure the walls down there are properly set up.

To control the input/edit/deletion of data you will create forms on top of these tables, that's where you will focus making an easy to user user-interface, not at the table level. With that said, I'm sure you can make open the Row Source in Design View and bring in the school table to get the name.

 

[Helystra]

My users are even more technologically inept than I am and there is no way they could handle assigning all this stuff to start off with, so that task will fall on me with maintenance and changes being all that's needed afterwards. To do that I need to be able to import the schedules and assign them before i put the database into live production where the data will be maintained by the users in forms. I'm not about to use a form with all the extra keystrokes or clicks to manually enter assignments for the records of 100 schools, over 600 employees, 700 job slots, 2000 schedules and 400 or so programs, when I can simply import the data and then go down the table and select from the drop down in the field to assign them properly. That's ridiculously burdensome to do it through a form one at a time.