How to create a daily record of changes in a table from a main table?

[ayupchap]

Hi there,

So if anybody can help me with this I would be really thankful. I am creating a database for a school whereby it logs all bus riders and their locations times etc. Now that bits finealong with all the reports etc but this is the real issue I have.

I want to automate kids that will not be riding the bus on certain days because they are ill or getting picked up whatever the reason so when reports are printed it leaves them out but also keep a table of results of who didn't ride the bus when. What is the best way to design a system to allow this. I understand that I would need some sort of entry screen to select students not riding but how do I keep a record of all these dates they do not ride for reporting purposes and also not harm the original database and allow a report to be run showing who is and is not riding that day for checking everyone who should be is on the bus and those who shouldn't are not.

If keeping it super simple just for try out purposes lets say these are the fields and their table:

STUDENT BUS
Student name
Route Name
Bus Stop
Riding bus?

Hope someone can help. I was guessing I would have the main table STUDENT BUS and then maybe need another called BUS LOG or something that populated this from as Form and the report run from a query set to calculate the report for that day.

PLEASE HELP!!!

Thanks

[June7]

Analysis for a negative (what is NOT there) is tricky.

First, this requires a dataset (a bus roster) of all students assigned to the bus - this should be a junction table.

Then need a dataset of all student/bus assignments for every date. This requires a dataset of all possible dates - can be generated by a query of dates in the actual rider log. Cartesian join of student/bus table to date query.

Then join that query to the actual rider log. Where the date from rider log is null, student was not on bus.

The alternative is a record in the rider log for every student even if they did not ride.

[ayupchap]

Wow that is more tricky than I thought.

Is there no way to have a table with all students assigned to particular buses as you said, then have another table with a date to and date from and then use a data entry form to enter these. Then a query which will ready the current date to print a report of those that are getting the bus and mark those that are not? Thanks so much for your help so far, just want to try and keep this simple as possible but if that's the only way then fair enough.

[June7]

Anything is possible, but getting there might not be easy, as already explained by the steps to return info where records don't exist.

Again, identifying those who are NOT getting the bus is the hard part.

The conventional approach is to create record only when something happened, not when it didn't.

However, the simplest, especially for a novice, may be a record for every student on every date that indicates whether or not student rode and give a reason if didn't.

[Dal Jeanis]

First, Clearly define the purpose of the database, in a single sentence.

Next, decide on the business requirements and business rules. What does the driver do, what does the database entry person do, what information needs to come out of the database and given to whom, and so on. If you write those down first, in plain language, it will be easier to verify that your database can support the desired processes.

PROCESS NOTES

I would suggest that, if you are trying to verify ridership, that you would want the daily log sheet for each bus to have all the names for every stop, with a notation of which ones were NOT expected to be on board. Also, if there is a chance that some students will take an earlier or later bus, then you will need to put their names on the sheet for each bus that they MIGHT take. It's up to you whether to make it one integrated sheet with all the names, or one sheet of the expected ridership and a second ship of everyone who is "optional". It depends on what the driver is supposed to do when someone no-shows.

I expect you will have a table of all the student information and a second table of the bus stop and time that each student is expected to take.
If I were designing it, I would probably include a flag on the StudentPickup for whether a particular record is their expected vs optional stop . That way, you have an easy way to represent that student X usually takes the 7:40 bus at stop XYZ, but she is allowed to take the 6:40 bus at XYZ or the 7:35 Bus at WXY if she shows up there).

Your exception table would have the same information, along with a date or day of the week code.

For reporting, you could run a routine that could copies all the "standard" records over to a work table, then updates them with the information from the exceptions table.

That's just a back-of-the-envelope design. It might be possible to do teh reporting just with queries, rather than making the temp table for the day or week, but I thought the temp table was conceptually simpler than a gnarly query. Your mileage may vary.

TO CREATE THE BEST DESIGN

First, hop over to Roger's Access library at http://www.rogersaccesslibrary.com/forum/topic238.html and do his quick tutorial on database design. Do at least two of the practice examples.

Then pretend for a moment that you didn't have a database yet, and do an entity relationship diagram for your database application, the way it should be. make sure the design supports all the business functions that you have identified.

Your new design will have pieces that are almost exactly how your current one works. it will also have other aspects that will tell you how your database needs to be massaged.

[ayupchap]

Thanks for all your help with this. I will certainly head over to that site, I have made relational databases before and have some experience with this but its been a while and also I have never had to create something like this before.

My one line definition would be: A Database that holds all student ridership's and prints out a list of who will be on the bus and who will not be.

The driver doesn't mind who is on the bus or which stops to go to or not go to as he/she will just do their route regardless.
The data enter only has to record any students who normally ride that will not be so the register is correct at time of boarding the buses, making sure the head count is correct and who should be there is and who isn't isn't.

All that needs to be made is a bus list of students for each day broken down by route of who is on the bus and a note saying NO BUS next to those that are not for that day. Also for the records the dates someone isn't riding can be logged into the database and be accessed again at another time as proof.

I'm going to look at this site you mentioned, thanks

[ayupchap]

I also know how to normalize data for databases mostly and draw up an ERD. I really am just having trouble with a simple way to record who is not going to ride the bus home on certain dates, keep a record of this and allow a bus register report stating both riders and those that will not on that day.

[Dal Jeanis]

Cool, that makes it easy.

So, your entities will likely be Student, Bus, Stop, Route, StudentStop (junction), StudentStopException (junction)

WORK FLOW

One thing - there are two types of data entry here - the first one is entering the data that will allow the application to calculate what SHOULD happen, the second is entering the data that shows what DID happen.

If I were you, I would pay more attention to what the driver will actually do. Is the driver going to ID the students who board, and check them off? How does she handle arrival of a student who is not on the list? If the driver is just counting heads, then you're not actually going to be able to prove anything.

I would also pay attention to the question of whether there are multiple times that the same route will be driven each morning. (That was the case when I was a kid.) If so, then you need to accommodate an efficient way to "flip" a student from one time to the other, and a simple way for the driver to identify kids that flipped. (If the same bus will drive the route both times, then two checkboxes next to each name, with the expected one marked ((( ))) and the alternate one marked ( ) would allow a data entry person to visually pick out the exceptions without much work. You would use ( ) for both times if the person was in the exception list for that day.

Then, I would review what will occur when the driver's route sheet is turned in later. How do the exceptions - the unchecked persons, or the checked persons that had been marked as NO BUS, or the hand written names of students who "just showed up". That part of the workflow is where the rubber meets the road, so to speak, and accommodating this step is the second most important part of the design.

PROCESS AND DATABASE DESIGN

I would think that each day you will print out the following Days route sheets.

The first step would be to create a temporary table of the student's normal route times.

The second step would be to override the student's normal time with a notation if the student is in the exception table for that day.
I would think that the stopexception records should have a beginning and ending date, studentID and dayofweek indicator. I'd probably go with five indicators, one per day of the week. That way you could use "WHERE EXISTS" for the particular day being generated to determine your matches, and which records to update.

[ayupchap]

Great so I have the following tables made:
Student, AM_Bus, PM_Bus, Teacher_Assingment

Student has the usual student details including stop info etc. AM and PM bus tables list the route and all the stops and times, they are separate as they have the same codes but obviously need to be differentiated from the AM and PM easily and as the codes are not unique I wanted to separate them, also students will sometimes get a different bus home from the one they went in on. Teacher assignment is a small table which shows which teacher is in charge of getting and checking what students onto each bus.

I guess the database at the moment prints reports etc for what SHOULD happen it just can't allow for a change to someone not going on the bus home so this has been done manually with a red pen each day. Its the day sheet that I don't know how to make so I can get the report to list those not getting the bus with those that still are and also keep a record of this in the database for keeping a log of this.

I have so far made a new table called NO_Bus which allows for a start and end date of when people will not get the bus and can link that obviously but its the making it work from there that's now my issue.

Thanks so much for your help, it really helps me go through and break down this system.

[Dal Jeanis]

OK. Let us know if you need any more help.

[ayupchap]

Actually making good progress but still not there. Do you know of anyway to combine 2 queries into one where one query exists and added to it is the new query additions. I have a no bus list working now and want to merge it onto the normal bus register that gets printed so a box comes up saying NO BUS next to the name of a student. Any ideas?

[June7]

Try a query that joins the two, join type 'Include all records from BusRegister ...'

[ayupchap]

That's it!!! Thanks so much now One more thing and I'm home and dry. In a query how do I add text that says NO BUS if todays date is between The No bus date start field and the no bus date end field? If I can add this then I can make my report and everything is good in the world!!!

[June7]

Create field with IIf() expression (or expression in textbox on report).

IIf([datefield] Between [start field] And [End field], "On Bus", "Not On Bus")

[ayupchap]

This has totally fixed my problem. Thanks so much, everything is working great!!!