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

[plog]

Right now, you've envisioned the school as the top level of your system; the 'main' table if you will. It sounds like programs might be better suited for this.

You would start with a table for programs and then link (sometimes via a junction table) to schools and employees. It might seem like a huge structure change but its really not, its just a new entity to link all your data together more logically.

Instead of trying to layout your tables in Access, it may be easier to do with Post-its. Write every object your database needs along with the key fields of that object on a post it and put it on a piece of paper, then draw lines to connect them in the manner you think they should be.

Right now I see 4 Post-Its: Programs, Schools, Employees, Schedules (for lunch and Breakfast, etc). With lines going from Programs to each of the other 3, but no lines going from the other 3 to each other. What other objects do you need to account for?

 

[Helystra]

I'm good with a major structure change. I came here knowing it might come to that.

I think the employee table and the school table i have sorted as far as what is going in those.

About the schedules table (and I'm mostly sound boarding here): Breakfast and lunch bell schedules will of course match to the "contract" program for each school. I think I should also use this table for the start and end times of the supper program and the snacks as well as summer school.

Now should I use start and end dates here or put those on the program table?

About the Program Table. Each program has either an outside sponsor (YMCA, Boys & Girls Club etc) or the SAISD pays for it. Each sponsor has a max number of students they will cover (allocation which varies by school) and then we also have to keep track of how many students are actually enrolled (enrolled). There may be more than one program sponsor at the same school. Example: Boys and Girls Club may sponsor 43 allocations for the supper program at Mission while the YMCA sponsors an additional 10 then we have 60 kids enrolled so the SAISD must pick up the tab for the additional 7 kids. It's not absolutely essential that I account for the additional 7 as long as I can account for the difference between the allocation and the enrolled. We already know that SAISD has to pay for the difference.

The programs will vary in start dates, end dates, sponsors, allocations, enrollment and times depending on the school they are associated with.

Oh I forgot, I do have Area Supervisors, Custodial Supervisors and Leadership Executive Directors to account for and those are assigned to schools. So I'll need those three additional tables (all contact information etc) to make a 1 to many relationship with the school table.
Unless you can think of a better way to handle that.

The only thing I'm not sure how to account for and where to put it is the budget codes. I hadn't yet figured out how to use them in my original database either. I would like to know if it's possible to incorporate them somehow into this structure.

I have different codes for regular and OT wages that apply to contract time, supplemental (any time over contract hours), concessions and differential pay (only for manager interns who spend hours as acting managers). And to add to the complications, the budget codes have several sections and one of them corresponds to the school where the hours were accrued and another to the program type. The budget codes look somthing like this:
240-35-xxxx(reg or OT)-xx(program)-xxx(school)-xxxx(school year)

I think that basically covers all the objects I have to work with. I'll get those 4 tables set up and then post it here for you to look at in a few hours when I'm done with it. If you have suggestions between now and then please let me know.

 

[plog]

Now should I use start and end dates here or put those on the program table?

Start and end dates should go in the program table.

How granularly are you tracking enrollment? Do you know that Jimmy Smith was enrolled in Program X, or do you just know that 17 kids were enrolled in Program X?

How many sponsors per program? If only 1, then that goes in the program table, if more than 1 then you need a junction table.

You should have 1 table for all contacts/staff. Then when you need to link them to a school, you create a junction table:

SchoolStaff
SchoolID, StaffID, RoleType
12, 8, "Principal"
4, 8, "Cook"

 

[Helystra]

I don't track the student name. Just how many total are covered by the sponsor.

The programs are per school and most of the time there is only one sponsor per program per school but there can be more than one.

So i should add the supervisors and such to the Employee's table or just put all 3 types of supervisor in one staff table separate from the employees?

 

[plog]

I would probably put every staff member, regardless of position in the Employees table. You want to put objects with the same attributes (i.e. First Name, Last Name, Phone #) in the same table, then where they differ you use a new table or a field to designate those differences.

So to designate someone a supervisor you either create an StaffType field in the Staff table I just suggested (with values like "Supervisor", "Cook", "Principal"), or as I mentioned in my immediately prior post, you assign the StaffType in the junction table when you assign a staff member to a school.

 

[Helystra]

Ok can do. I have a job titles field that I can use for that.

When I want to assign an Area Supervisor to a school...how would I limit the selection list to just those titles of Area Supervisor to select from? I have 8 Area Supervisors so I don't want to have to go through all 600 employees to find them.

 

[plog]

In that case I would put a Position field in the Staff table and have them in as "Area Supervisor". Then in your drop downs you can use a query that uses just those staff with that position.

 

[Helystra]

Ok when using the autonumber as a primary key field (example: job titles), it defaults to showing the 1st column of the selection in the job title field of the employee table. Is there a way to make it display the second column which is the actual title instead of a record number?

 

[plog]

I'm sure there is, but who cares? Tables shouldn't be viewed as a place to go to get data. Tables are there to store the data, so it shouldn't matter what the data looks like there as long as its structured correctly.

Data in the tables should be manipulated via forms not directly on the tables. So it shouldn't matter if "1" appears in that table or "Supervisor", because in Forms and reports it will say "Supervisor".

 

[Helystra]

Ok so I'll be able to change the information I view so that i get the actual title in the form not the row number then. Right?

 

[Helystra]

alright I've got the Employees, JobTitles and Shools tables ready to go with the new structure. I'm about to work on the Schedules table. Now if I'm following you correctly I should have the following fields:

School (which pulls from the school table), Desc, StartTime, EndTime, and my checkbox for BIC (breakfast in the classroom) if it's a breakfast meal.

Is that right?

 

[plog]

All that looks right, but I thought the schedule was going to be off of the Programs table. If its to be attached to the School then you have it correct. If its off of the Program then instead of School, you should have a link to the Program.

 

[Helystra]

Hmmm. Well you tell me which is better. I can have up to 20 "meal schedules" per school. And no two schools have the same schedule. I have somewhere in the neighborhood of 2 breakfasts and up to 14 lunches (all part of the contract program), then some schools have snack programs (1 meal) in the afternoon and some schools have supper programs (1 meal).

Keep in mind that the contract programs can have anywhere from 3 or 4 employees up to 20 employees that need to be assigned and the Supper program needs to have the ability to have a lead cook and an assistant cook. Now when it comes to Summer School, there can be anywhere from 1 lead cook to a lead and several assistants but they everyone is paid an entirely different pay rate during summer school.

I still have to figure out how to attach my budget codes to all this as well.

 

[Helystra]

Ok Plog. Here's the zip file of my new data tables. Let me know what you think.

from here I'll need help building the junction table to assign the employees to the programs and make sure all the data ties together the way it's supposed to.

 

[plog]

In tblSchools you have Text fields for Area Supervisor, Custodial Supervisor, LED, Principal. Those definitely shouldn't be text fields--they would be numeric fields that hold the ID from tblEmployees. They might not even be in there at all. They might go in the junction table that assigns Employees to Programs.

'Year' is a bad name for a field (in tblBudgetCodes) because its a reserved word. I'd change it to 'BudgetCodeYear' or something similar.


In tblEmployees you have numerated fields for SubArea (1-4), that should probably be another table intead of numerated fields.

Same thing with MonitorDates (1-3) in tblPrograms.

 

[Helystra]

I will check the tblSchools fields you mentioned and see that they pulling the rowID from the tblemployees. I dont' think i set up that look up relationship or anything yet cause i wasn't sure how we were going to handle them. It's a one to many type of relationship that needs to be created for each of those fields so I'm not sure if it should go on the junction table. But I may be wrong in my thinking on that.

I will change the year field name in tblbugetcodes.

The numerated fields you mentioned in the the tblemployees is an area designation. Our district is divided into 4 areas. When it comes to substitutes, the sub finder needs to be able to pull a list of subs that will work in each area or in all areas. I have them set as check boxes...if you still think they need a table of their own or have a better suggestion of how to handle that, I'm all ears.

The monitor Dates in the programs table are for the 3 separate monitors that are required for the programs by the state. There should be one each semester and then one that is fit in somewhere in the middle. We track those for auditing purposes to show the state when those monitors were performed. Again if you think it needs it's own table we could do that but I don't see the point since none of the dates will be the same.

 

[plog]

I assume a school falls into an area, but I don't see an Area field in tblSchools. In any case this calls for another junction table to designate which areas an employee will substitute at--remove those Area fields from tblEmployees and put in their own table. Again, any numerated fields should be handled in this manner.

With that said, the monitor dates in Program sound fine, I wouldn't break them out.

Lastly, try and comment your fields. In the design view each field has a description area, try and fill that in with what the data in that field is for. If its a foreign key to another table, put what table and field it links to.

 

[Helystra]

I have commented all the fields. I have also attached budget code look up fields to the tblPrograms so that the budget codes for reg wages and overtime wages are now attached to the programs.

If I'm doing this right, I should end up with multiple rows for each school in the tblprograms. I should have a contract program for each school, an asm program row for each school that's participating in the asm program, a snack program row for each school participating in that. I should have 1 concessions program row because that's not assigned to any particular school, And I should have one sub manager program row for those employees that substitute as managers and therefore incur differential pay rates/budget codes. Does that sound right to you? You mentioned yesterday that you thought the schedules were going to tie to programs rather than schools, do I need to change that given the way the programs and schools tie together?

I now have 2 new questions...

1st with regards to moving the areas to their own table. I have obtained a list of which schools belong in which area. I'm a little fuzzy on how to set this table up and relate it to both the employees and the schools. Do I make a single column table with the areas listed and then related to the schools and then also relate back to the employees? If I do that I'm still going to need multiple choice options (possibly multi value field?) because a sub can be in more than one area. A little more guidance here would be appreciated.

2nd I'm changing the areasupervisor, custodialsupervisor, LED and Principal fields to look up the title field in tblemployees. My question is about filtering. When I want to select from the Area Supervisors, I don't want a list of almost 700 employees to look through, I only want the few employees with Area Supervisor as their job title to show in the drop down list. Can you walk me through making that happen?

 

[plog]

I think you treat Areas like you treated Cities a few posts back--make them a lookup list item in the drop down of a table. That's unless you have more information to go with them than just a number (i.e. AreaDesc, AreaManager, etc). If you just have numbers and nothing more, then you just use those numbers where you need to designate an area.

At the school level, assuming a school can only be in one Area--you create a new field in tblSchools to hold which area they are in.

At the employee level, because an employee can be available to multiple areas, you will need a junction table:

EmployeeID, Area
17, 1
17, 3
17, 2
29, 2

That solves your issue of knowing which employees are available to each Area. Do not confuse this with which Area employees are working in. To determine that you would go through the Program side of things to see which programs an employee was assigned to, which school that meant they worked at and ultimately what area that school is in.

For your second question, I don't fully follow. I don't see a title field in tblEmployees, second I thought that information was going to be captured in a junction table when you assigned employees to programs. Can you provide specific examples using sample data? Follow the format I just used for posting data (see the data 2 paragraphs above).

 

[Helystra]

The "area" is only for use with Substitutes...so not all employees will have areas to deal with. Let me see if I follow along with how this should work. I make a value list field in tblSchools called "Area"...remove the area fields from tblEmployees and then later on make a junction table to bring the employees with the title of "Substitutes" on [tblemployees] together with the different Areas on [tblSchools]?


With regards to the second part of your post. There is a field in [tblEmployees] called "Title". This is a look up field that pulls from [tbljobtitles]. I have several job titles in there. There is a substitute title as well as varoious cook and manager titles. Then I have Area Supervisor, Custodial Supervisor, LED and Principal. These particular positions are not dependent on programs which is why i'm not assigning them via junction table like the cooks and managers. They are assigned directly to the school themselves and do not change throughout the school year unless someone is replaced in that same position.

The problem comes in the look up process. Now that I will have a list of almost 700 people in [tblemployees] because I'm keeping all staff in one table per your suggestion and using the position (title) to differentiate between them.

In AreaSupervisor field of [tblschools], I want to make that a lookup field that pulls from [tblemployees]. The drop down list will would look something like this:

EmpID, LastName, FirstName, MI, Title
01, Que, Suzie, A, FSCook5
02, Good,Johnny, B, Area Supervisor

What I want is for the drop down to only show those employees with the title Area Supervisor so that the user doesn't have to scroll through 700 names to get there.