Full Version: Form Design
UtterAccess Forums > Microsoft® Access > Access Forms
LegendofJPizzle
Hey Folks,
I'm trying to convert an Excess spreadsheet (attached PDF example) to an Access database that would allow reporting the data to be an easier task as there are another reports associated with the data.
I've set-up my tables and normalized as much as I think it could be... Now, I'm wondering what's the best way to set-up the form(s) for data entry. The users would prefer the configuration similar to the Excess spreadsheet.
What I'm trying is one main form with the Route, Date, Type of Day, Directions (both ways), and Timechecks (for each Direction). Then I'd have two subforms in datasheet view, one for each direction. But I got a feeling I may be doing this incorrectly...
Also, I noticed that I can't populate the main forms' entries into each record for the subforms.
Any suggestions?
Thanks!
jurotek
Hi,
Quote:>>A value of 0 is given to a trip that is 1" or more early to 6" or more late<<
Why is 0 assigned to On Time when ACT is earlier then SCH. Wouldn't this be favorable and therefore considered
On Time? Doesn't assigning 0 in situation like this affects negatively performance and in reality departure was earlier then scheduled and therefore considered favorable?
Just curious.
LegendofJPizzle
Hi Jurotek,
forgot to explain that this spreadsheet is for bus scheduling. According to the agency's standards, if a bus leaves a timepoint (station) before the scheduled time or 6+ minutes after, it is considered not on time. The reason for the earlier departure is that if a bus is due at a station at 3:15 PM but has arrived and left that station at 3:10 PM instead, passengers who are arriving at that station after 3:10 PM have missed the 3:15 PM departure.
In any case, I'm having trouble setting up the data entry interface where it is similar to the spreadsheet so long-time users do not have to adjust to whole new way of entering their information. I'm having some issues on going about it the proper way. Right now, I'm trying to design the entry form with a main form and two sub-forms but I'm getting stuck linking the controls to the fields...
I'll figure out what I'm doing wrong and if I'm still stuck, I'll find the right question to ask =)
BenPurser
This is REALLY off the cuff here, and in addition I don't know a lot about your sitch that I would want to know, but:
ren't your routes standardized? So for route 23A, isn't it always going to stop in the same places all the time, unless/until a new stop is added or an existing one terminated? By like token, aren't the directions going to be the same? These may change with type of day (ie, weekend service may not be the same as weekday service), but for any given type of day, aren't the routes going to be pretty much determined? And, by "Directions," do you mean a listing of the stops?
If this is true (and goodness knows this AIN'T my bag, baby and I am likely--even hopefully--gonna get a lot of comments here from others), it would seem to me that you need a table structure to hold your route "templates."
This would consist of:
tblRoutes
RouteID--autonum, PK
RouteName--text, ie Route 23A
Maybe something else but I don't know enough
tblRouteDir
RouteDirID--auto, PK
RouteID--FK from tblRoutes
RouteEffDate--the day on which these directions/times for this route became effective--to track changes
DayType--should be done from a lookup table listing all time types, so would be FK here)
tblRouteDirDetails
RouteDirDetailsID--auto, PK
RouteDirID--FK from tblRouteDir
Stop (note, this should probably be done from a lookup table holding ALL stops everywhere so you'd use a number, but could be done with text fields)
Outgoing/Incoming flag (boolean)
SchedTime
What this does is gives you the complete picture of what your routes are and what the time requirements are for each stop for each route for each type of day.
Then you could have as your data tables:
tblRoutePerformance
RoutePerfID--auto, PK
RouteID--FK from tblRoutes
RouteDate--day for which you're recording performance
DayType
Then, based on the route and route type, you dump a template of entries into another table showing all the stops, which also has a field to record the actual time of departure from the stop. You would select the stop data from the route details with the latest effective date:
tblRoutePerfDetails
RoutePerfDetailsID--auto, PK
RoutePerfID--FK to tblRoutePerformance
RouteDirDetailsID--FK to tblRouteDirDetails
ActualTime
Again, I haven't really sussed this out too much, but I think that would work, or is close, anyhow. As for users complaining that they want it to look like excel, I presume there's a REASON the decision's been made to move to access, which is perhaps better reporting, etc. Failing that, find a large stick, and apply liberally....
HTH
Ben
LegendofJPizzle
Hi Ben,
Thanks for the quick reply, unfortunatley I was already out of the office so I couldn't back to you till this morning...
Oreally appreciate you giving your insight on the table structures. I was already thinking about maybe adding the stops into the db as another section would already have that data available and I could connect to it. I'll have to see if that's really needed or not but it would definitely add a feature that would be welcome to the users.
With the route performance, this would be a calculated field so would I want to store that? Unless I'm wrong in my research and understanding, storing calculated values isn't really preferred? Otherwise, I could see the point in doing so but I was just wondering about that.
In any case, I'm just having more trouble setting up a data entry interface than the table structures. By the way, what you gave is very similar to what I already have sans the route performance and stops.
HAs the users were very used to using Excel to input their data, I'm trying to see if I can mimic the look at least so the feel won't be too different. But yes, the reporting is the main benefit in switching to Access. Basically, it started out with 80 different (routes) files having to be manually summarized into a monthly summary spreadsheet. For now, I had just combined all these routes (as tabs) into one Excel file then had the summary spreadsheet linked to their data on that file. Of course, its huge and bulky, and unforunately the main reporter isn't as keen to Excel... so I'm thinking Access would help ease this assignment.
In any case, I think I'm on the right start, expect a post in the Forms sections soon as I get closer to what I'm hoping for =)
Thanks again!
BenPurser
Can you zip & post even an empty copy of the database you have so far? That will help me in analyzing the structure....
en
LegendofJPizzle
Sure thing, here it is. Thanks, Ben =)
BenPurser
For some reason it doesn't look like the attachment made it up...it has to be under 500K.
Try downloading it and see if you don't get a "couldn't find it" error page, check the file size on the zipped file, compact & repair the db, and if necessary delete some data prior to re-posting it.
Ben
LegendofJPizzle
Odd... sorry about that.
BenPurser
Looking at your structure, I think you have some issues. In theory, all your stops and their scheduled times are set for periods of time (until the route or schedule changes). However, you're having your users input all sorts of info over and again (route, day, day type) which in theory should be entered once in a form, then have the real data entry (the actual times and any kill time) entered in a subform--note that this implies separate tables for that info as well.
think before you go any farther, you need to do some reading on normalization, which is a big word for database design. Getting the design right is 99.9% of the battle, and it will save you and your users a heck of a lot of time & energy. What you've done here is more or less re-create your Excel spreadsheet, which I know was a goal of yours, but in this case it's pretty inefficient.
I've added some links below on normalization...read through them, then read through my post again when I did a kind of off-the-cuff table structure, and start breaking some of this out into separate data tables and more lookup tables.
HTH
Ben
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.