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

[plog]

I make a value list field in tblSchools called "Area"

Yes

...remove the area fields from tblEmployees

Yes.

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]?

Not exactly. Schools don't figure into this junction table, just employees and areas. This is the format of that junction table:

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


As for your Area Supervisors, walk me through the data population method. How's this data getting loaded initially? My thinking is you already know who each school's Area Supervisors are, you're just going to load it into this finished structure. Or am I wrong--this data does not exist in a format that can be imported into your database and every piece of data is going to be rekeyed once you have it set up?

Also, moving forward, are Area Supervisors (or any other position ) going to move around a lot? My thinking is that a drop down isn't going to save you much.

 

[Helystra]

The row source code for the look up field is as such:

SELECT [tblEmployees].[RowID], [tblEmployees].[LastName], [tblEmployees].[FirstName], [tblEmployees].[MI], [tblEmployees].[Title] FROM tblEmployees ORDER BY [LastName], [FirstName];

Is there a way to add a FILTER BY [Title]"Area Supervisor" or something?

 

[Helystra]

I need to be able to edit almost everything in this database after it's in. Yes the area supervisor (and other supervisory positions) could change later. As could employee assignments to the different programs. Anything and everythign can change at a moments notice around here.

Initially speaking, I know who the supervisors are...and I'll be populating it myself before go live. But once the database goes live in it's new structure, we will need to be able to change those assignments at any time.

Right now I have 6 area supervisors. We are supposed to have 8. So at some point this school year 2 more will be added and the assignments will change. As I said these supervisory positions are directly tied to the school itself and not the programs.

 

[plog]

In the Employees table I would add a new field to distinguish between Administrative staff and Food staff. Then in your query you could limit the drop downs to just those people who are Administrative staff.

 

[Helystra]

I'm sorry. I'm feeling rather stupid at the moment and I'm not trying to be obtuse but I'm just not getting the logic behind the junction table for the areas. It seems like an overly cumbersome way of dealing with something that I just need to have a simple yes or no checkbox for. I only need to know if the sub can work in this area or that area. The junction table seems like it would end up being a user entry nightmare to deal with for that simple task. making a separate row entry for each area that the sub works in. And then how do you easily change their area selections if they move or their availability changes?

 

[Helystra]

So for the administrative staff i need to make a query that only returns those staff members and then use that for my look up field? I'm confused.

 

[plog]

You would set the source of the lookup to a query that returns just admin staff.

 

[Helystra]

Sorry, I apparently left the house without my brain this morning. I guess i was confused cause I wasn't sure how that would work using a query to look up from and populate a table field. But I think I understand it now.

I'm still in a fog about the area thing, though. I think it's a mental block I have because of not really understanding how junction tables work and how they get populated. either that or we have a miscommunication about how the areas work for my school district. Area1 Area2 Area3 and Area4 are just names for the regions. The names could be replaced with North, South, East and West for the purposes of building the structure. So while they have numbers in the name, I'm not sure they should be considered numerated fields.

 

[plog]

Numerated field is just a term I use for a specific flavor of a structure issue. Anytime you try and store data in a field or table name its essentially the same issue. Instead of numbers you are storing directions, in either case, its data that should be in a field not a field name.

 

[Helystra]

Ok I've eaten and had a nap and I think I found my brain still sleeping in my bed when I got home. LOL!!!

I think I understand the concept of having to use the junction table to assign the subs to the areas, however I still have no idea how junction tables work. The wiki link you provided earlier in the thread to junction tables didn't pull anything up. Can you explain how they work, how they are populated and how I would have them maintained and updated by the users?

 

[plog]

Junction tables define many to many relationships. Let's use something not related to what we are working on--Library book borrowing. We have lots of books and lots of people, we want to know what specific books people checked out and when. Obviously we have tables for Patrons and Books:

Patrons
PatronID, PatronFirstName, PatronLastName, PatronJoinDate
33, Steve, Holmes, 4/13/2001
37, Amy, Smith, 7/7/2007

Books
BookID, Title, PublishDate
442, Cloud Atlas, 4/1/2004
19, Catcher In The Rye, 7/12/1954
119, Cosmos, 8/16/1983

The wrong way to store data, and the way analogous to the way you were doing it, would be to put the BookID in the Patron table, by just adding a new row for every book they checked out:

PatronID, PatronFirstName, PatronLastName, PatronJoinDate, Book1, Book1CheckOut, Book2, Book2CheckOut...

Instead the right way is a junction table to capture the many to many relationship between books and patrons:

CheckOuts
BookID, PatronID, CheckOutDate
442, 33, 9/2/2010
19, 33, 10/11/2011
119, 37, 4/2/2013

The way they get populated is through a form/subform system. They would go to the Patron form and on that form would be a sub-form based on Checkouts where they select Book names from a drop down and enter a CheckOut Date.

 

[eirman]

An excellent, and well explained analogy plog.

 

[Helystra]

Thank you. I think I get it now. Let me fix the areas and the supervisor issues and I'll repost the structure.

 

[Helystra]

When I'm making the junction table for the subs and their area, can I still make an option for "all areas" or will I need to force the use to enter separate records for all 4 areas?

 

[Helystra]

Also, when making queries for my look up fields (administrative staff vs kitchen staff) will that cause a problem once I split the database with the query not being in the same place as the tables? Or does that not matter?

 

[plog]

You don't actually make a query for that. You set the Row Source attribute of the field in the table or control in the form to the SQL of the query that will give you what you want.

 

[Helystra]

I'm afraid you've lost me again. If I do a straight look up (which returns all the employees in tblemployees, the row source code looks like this:

SELECT [tblEmployees].[RowID], [tblEmployees].[LastName], [tblEmployees].[FirstName], [tblEmployees].[MI], [tblEmployees].[Title] FROM tblEmployees ORDER BY [LastName], [FirstName];

Are you saying that I just need to change the SELECT [tblEmployees].[RowID] to SELECT [tblEmployees].[StaffType=Administrative] or something similar?

 

[plog]

Yes. You actually add a WHERE clause:

SELECT RowID, LastName & ", " & FirstName & " " & MI 
FROM tblEmployees 
WHERE StaffType="Administrative"
ORDER BY LastName, FirstName;

 

[Helystra]

Ok I think I've done this right but I want you to check me. In the AreaSupervisor field on tblSchools, my Row Source code now looks like this:

SELECT [tblEmployees].[RowID], [tblEmployees].[LastName], [tblEmployees].[FirstName], [tblEmployees].[MI], [tblEmployees].[Title] FROM tblEmployees WHERE Title="Area Supervisor" ORDER BY [LastName], [FirstName];

 

[plog]

It looks good from here, but you should be able to verify its working. Make sure you have a person in tblEmployees that has "Area Supervisor" for their title, then open tblSchools and click the drop down on AreaSupervisor field. If there's data in the drop down, you've done it correctly.