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

Helystra

Registered User.
Local time
Today, 16:49
Joined
Aug 20, 2013
Messages
64
A few months ago, I undertook what I thought would be a fairly easy task to create an Access database for one of the largest school districts in Texas. Knowing absolutely nothing about Access when I started, but having my best friend Google at the ready, I got started. I learned pretty quickly that this was not going to be as simple as I thought and the database grew and grew to include tracking of more and more types of records to the point that it's now basically become "the system" that is used by the entire Food Services department to track over 600+ employees and all of their information, almost 100 schools and various meal programs including inspection dates, program sponsorships and enrollment. It's now in use and so far it's going well. But it's become a piece of software rather than just a simple database and i've reached a point where simple google searches aren't really giving me what I need to know.

Now that it's in use and doing it's job...I'm being asked to add new things to it and create other reports and such which I'm more than ok with and I absolutely love to learn new things, so that's not an issue. But the more I learn about access and how to do it right, the more I realize that I may need to basically start over and restructure it better since I sort of stumbled on the structure I have through the hunt, peck and pray method of database building.

My database currently has 12 tables 18 queries 15 forms and 21 reports and I imagine it needs at least 10 more reports and several more queries. I also suspect I may need to break down my employees table and possibly my schools table into several different tables.

I have questions about everything from how to move information from an active employee table to an inactive employee table or even if that's the best way to do things... how to deal with employees who are contracted at one school but may work an afterschool program at a different school. I also need to know if there is a better way to update and distribute the 6-8 front ends that i end up having to build every time I change something. I'm hoping to get some input as to the best way to work on the data tables within an already in use database without corrupting the data and having as little downtime for the department as I can get away with. And for the love of everything holy, is there a way to set up a template report design so that I don't have to build the damn reports from scratch every time i create a new one? This is but a small sample of the many questions I have.

I'm thinking that I may need to start out in the tables and structures forum to make sure I have the foundation of the database as solid, stable and optimized as I can get it. I was hoping that one of you may be able to take the newbie by the hand and guide me through this very overwhelming process of getting the information I need.
 
Please tell me your not a school district where every kid gets an ipad. Because this sounds like a school district that needs to better evaluate its IT spending. Not to denigrate you, but this doesn't sound like the way this should be handled. This sounds like a project that got a few outside estimates from actual programming shops, had their jaws drop when they saw the totals and then said to themselves, 'This isn't that hard, I've played with Access, let's just have Helystra do it.'

One more soapbox paragraph then to your questions: I don't think Access is the right solution for this. You're distributing 6-8 front ends? That's too many. Either this thing should move to a web interface or you've got some redundancies in your front ends that should be eliminated. And lastly, what happens when you leave? I'm guessing that there's not much documentation to operate this thing from a user's standpoint, nor how the system works from a developers standpoint. May god have mercy on your replacement.

Now, to your issues. Forget reports, forms and everything else but tables. You need to get your structure correct, or applying band-aids will become a full-time job. You mentioned breaking employees and school tables out into several tables--why? Also, you mentioned moving records from an active table to an inactive table--don't do that. You simply create a field (Inactive) and set it to true for any inactive records.

If you want to post your structure we can help further.
 
Thanks for the reply. I'm not going to address all of the irrelevant issues with my school district and how they operate and why we are doing it the way we are. None of that is germane to why I'm here. Access is what I have to work with and so Access is what I'm using.

Re: Breaking the tables up
I put all the employee related fields on one table and school related fields on another originally thinking that was better because each piece of information is tied directly to the employee ID or the campus. All of the other tables are basically look up tables for the Employees or Schools.

I had read somewhere that it would be more efficient to break up the tables since I have several chunks of information to store for the employees and the schools. For example: Contact information, Job assignments (payroll information etc), Certifications, Uniform Sizes, Improvement Plans and then i have a whole other set of information to track for substitutes. And for the Schools I have the locations and related info like principals, addresses and phone numbers but then I also have their bell schedules, various meal programs and their inspection and review dates to keep track of as well.

Re: inactive records.

I do have a status field for employees where we can select active, inactive, retired, terminated etc. And I would love to be able to just use that but how do I then make those that aren't "active" not show up in drop down look up lists when selecting employees for assignments to after school meal programs etc later on?

I have tried to post a copy of my back end db here. I've stripped out all the data so it's just the bones. I hope I've done it right.
 

Attachments

It says its an unrecognizable format--I can't open it. Can you try again?

As for your drop downs, you would set their Row Source to a query that excludes inactives.
 
Ok trying again. It's an accdb file 1.41 mb so if it doesn't work this time I have no idea why. I'm using the right file type and it's under the max size allowed.
 

Attachments

re: exclusion query

So if i make a query that excludes the inactive records, then I would set my look up fields to that query instead of to the employees table? Is that right?
 
re: exclusion query

So if i make a query that excludes the inactive records, then I would set my look up fields to that query instead of to the employees table? Is that right?

Try using the query as the row source for a combo.

If you can't upload the file because your post count is <10 just zip it and upload
 
Now since my DB is split, I'd want to use that query as the row source for a combo within the form itself and not in the ASM table directly as I have it set now (which is the after school program where i have to assign an employee to a different school), yes?
 
So if i make a query that excludes the inactive records, then I would set my look up fields to that query instead of to the employees table? Is that right?

Correct. But like I said (and is reconfirmed after looking at your data) forms and reports are a ways down the road. You really need to properly structure your tables. You've got a lot of issues.

For now, I'm just talking in general. Numerated field names (FieldName1, FieldName2, FieldName3) are a sign you need a new table with a 1 - many relationship. You have a lot of numerated field names. In just the School Information table you have [Health Inspection X], [Kitchen Evaluation X], [Lunch X Desc], where X represents a number. All that data should be in a new table, where, if the X is signifcant it goes in its own field like so:

Lunches
School #, LunchNum, StartTime, EndTime, Desc

Where LunchNum would hold the number, if its signficant. In fact, that table should probably include Breakfast information that is similiar in structure.

Also, get rid of non-alphanumeric characters (underscores are fine) in field and table names. This means remove spaces and number signs. It will make writing queries and code easier later on. ([School #] -> [SchoolNum])

I would also assign autonumber primary keys to tables instead of text fields. While I'm sure the current president won't ever have a school named for him in Texas, its quite possible that Oak Field High School gets named for the prior one. My point is, names change and I wouldn't rely on something that could change to make relationships in my database. Use autonumber primary keys.

Work on those issues and post back what you come up with.
 
Alright thanks Plog. That's what I thought might need to happen when I mentioned breaking up the tables. I just wasn't sure how it needed to work. I appreciate your input.

Before I set to work on this change, will i worry about relationships at this point between the lunch tables and such back to the school and employee tables or do that later on with queries?

I also have several "look up fields" in those tables where I didn't bother to make a separate table and just set the list itself inside the field...should i make those tables of their own too in order to be consistant?

I will work on this and then repost the new structure.
 
Relationships go hand in hand with setting up the tables, so its something that you should worry about, but setting up the tables correctly kind of takes care of that.

One of the keys to relationships is having a unique primary key autonumber on your fields. Then the relationship gets established when you have that unique primary key as a foreign key in another table.

You shouldn't have lookup tables--and by that I mean a table with only 1 field. If the data is so important that it needs to be grouped together you'd have more fields to go with it. Take School Types for example, it sounds like something you should have a table for, but you don't really do anything with it. There's no description for each type, or who's in charge of each type.
 
Ok good point. So then I should just set the list in the field itself for those types of things.

The only reason i added them as separate tables was so that I or someone else in operations could easily add to the lists later on if needed, like with the Cities. Most employees are all in one city but some live out in the suburbs or further. I wanted the drop down lists so the data entry would be uniform. But i can do that with just lists inside the fields.
 
Yes, in design view of the table, go to the Lookup Tab of the field you want to limit. Change the Display Control to Combo Box, Row Source Type to Value List, and then put the values you want to use in the Row Source field--seperate your values with a semi-colon (Houst;Dallas;Austin). Last, make sure Limit To List is No, that way users can still type in any city they want.

Now, if you wanted to capture who the mayor of that city was, its 2010 census population, etc., then you would create a table for cities and put it in there and you would have the added benefit of being able to use that table as a source for your drop downs. But as it is now, just use the Table option.
 
Great Thanks.

Now with regards to employees and their assigned schools. I know I can use a relationship to bring the school into the employee table but when it comes to the after school program is that going to cause an issue down the road if I have to assign an employee to a different school for that program. I have nightmares of circular errors on that.
 
First, let's talk about just schools and teachers. You may establish that relationship in a junction table (http://en.wikipedia.org/wiki/Junction_table) it sets many to many relationships. My thinking is that a teacher can move from one school to another and instead of storing just the current school you store an archive of where that teacher was.

Below is an example:

TeacherID, SchoolID, TenureStart, TenureEnd
312, 6, 9/1/2011, 5/31/2012
312, 18, 6/1/2102,
314, 15, 9/1/2008,
127, 4, 4/1/2011, 4/30/2011

The above assumes you have a unique primary key ID in the school table and the teacher table. From the data you can see that Teacher #312 moved schools in
2012. For any teacher with a blank TenureEnd field, it means they are currently at that school. You would build a query using that to determine where everyone's at currently.

You would do something similar for summer programs.
 
Something like that might work for the summer schools but I don't think it's going to work with the situation I have for contract assignments and after school programs. Let me explain.

This is for the food service department so the employees that I'm dealing with are the kitchen managers and cooks. They are contracted for their daily work and assigned to a certain school. This is their "home school". However, we also have a separate program that runs afterschool where we provide supper meals to at risk children. Now Jane Doe who may be the kitchen manager for Nelson elementary may work at Mission elementary for the after school program.

Because this data is used to provide reports and such for payroll and the budget codes for that payroll are dependent upon which school the employee incurred those hours, I have to be able to pull up their contract assignment and their afterschool program assignments as well.

Yes, yes it is a pain in the arse. I'm well aware. LOL
 
It might work with two separate junction tables though. What do you think? One for the contract assignments and one for the afterschool assignments? I would just use the individual junction tables to run the different reports. I don't really need the date information on it. If i could just join the employee with the school for each type of assignment.
 
Last edited:
Actually, you may have another level/table: Programs. It would capture school, period, type (contract, afterschool, etc) and other data related to the program. Then you would have a junction table to assign employees to programs.
 
That is bloody brilliant Plog. I like it...a lot.

Can you explain a little further about how to implement that?
 

Users who are viewing this thread

Back
Top Bottom