Access

[Benay Bishop]

We have a contract for park and ride service on which we have to record how many people get on at each stop, whether or not they are one of six categories, and there are three instances (zone one, zone two or pass) for each stop, which leaves us with 18 fields per stop. We have to record on which route the bus was when they picked up at the stop as well. We have run out of fields in our tables. We have to track each route. These are tracked by date, individual route (B1E) and major route (Route 1).

What is the best way to deal with this and if I make a relational table, what would be the best field to use for making the table. I haven't worked with the program in a couple of years, so I'm rusty. On top of that, I was never a pro at it anyway.

Any help you can give me would be appreciated.

Please let me know if you need clarification on anything.


Submitted via EggHeadCafe - Software Developer Portal of Choice
A Short RunDll32 Primer For Developers
http://www.eggheadcafe.com/tutorial...d-ea18a319d28e/a-short-rundll32-primer-f.aspx

 

[Daryl S]

Benay -

Your table should have a field for the individual route, the date, the stop,
the category, the instance, and the number of passengers. I can't tell from
your description if the individual route would indidate the major route, but
if that cannot be table driven (e.g. each individual route is given a unique
value, and can only have on major route value associated with it), then you
may need the major route in the table as well. Of course you should have a
unique AutoNumber field. You should also have a unique index on the
IndRoute, RouteDate, Stop, RouteCategory, and Instance. The data might look
something like this:

AutoID IndRoute RouteDate Stop RteCategory Instance Passengers
1 BE1 1/21/10 Stop1 Categ1 Zone1 4
2 BE1 1/21/10 Stop1 Categ1 Pass 3
3 BE1 1/21/10 Stop2 Categ1 Zone1 5
4 BE1 1/21/10 Stop2 Categ2 Zone1 1
5 BE1 1/21/10 Stop2 Categ2 Zone2 2

 

[John W. Vinson]

We have a contract for park and ride service on which we have to record how many people get on at each stop,

whether or not they are one of six categories, and there are three instances
(zone one, zone two or pass) for each stop, which leaves us with 18 fields per
stop. We have to record on which route the bus was when they picked up at the
stop as well. We have run out of fields in our tables. We have to track each
route. These are tracked by date, individual route (B1E) and major route
(Route 1).

What is the best way to deal with this and if I make a relational table, what would be the best field to use for

making the table. I haven't worked with the program in a couple of years, so
I'm rusty. On top of that, I was never a pro at it anyway.

Any help you can give me would be appreciated.

You'll certainly need SEVERAL tables, not just one. Every type of "Entity" -
real-life person, thing, or event - of importance to your application should
have its own table. I'd expect that you would have at least tables for:
Routes; Zones; Categories; Stops (which might be a location if you have
defined bus stops, or an event if by a "stop" you mean "someone called for a
bus or waved down a bus"); probably others.

If you're "running out of fields" you're making a very common beginner's
mistake. Tables should be tall and thin, not wide and flat!

Take a look at some of the resources here. Crystal's video and her and/or
Allen's tutorials would be a good start:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials