Need advice on setting up a Data Input form, please?

[Ed from AZ]

We have vehicles of different types which run various routes during
the day. We have about 15 different routes, but that number can be
added to at any time. We want to track which vehicles have run which
routes and how many circuits they did on each route, with data being
input on a daily basis.

I have a table of vehicle types and table of vehicle IDs by type, with
cascading ListBoxes so I choose the vehicle type and then choose an ID
from the available choices. I also have a TextBox to input the date.

The question now is about handling the inputting of routes and number
of circuits. Currently I have one data table with fields for:
Autonumber, veh type, veh ID, date, and one field for each route. I'm
wondering if this is best, or if I should have a separate table for
each route.

Rather than have one blank for each route, I'd like to have the data
input form contain maybe seven pairs of one ListBox drop-down with a
list of routes and a TextBox for number of circuits.

A couple of thoughts/questions:

-- Having one data table vice separate tables for each route seems to
me easier to add a route. I just need to add a new field to the data
table, rather than create a new table. Is this correct thinking?

-- I'd like to have some kind of error checking to allow a route to be
chosen only once on the Input form. Elegant, I think, would be to
somehow remove any previously selected routed from the choices
available in the remaining ListBoxes. Adequate, though, would be to
capture each selection into a string, and then check the new selection
against the string (If InStr > 0 Then "You already picked this
one!"). What would be best for a newbie to attempt?

-- Can someone point me to a sample for creating the statements
necessary to gather the data from the form and write it into the data
table? In my mind, I need to iterate through the pairs of route/
circuit data, pick up the chosen route as the field name, and grab the
number as the data to write in that field. Is that the hard way or
the easy way?

Ed

 

[BruceM]

Haven't you figured out by now that you will be called out on your
inappropriate solicitations of work in a free newsgroup? Soon enough the OP
will probably have links about your history.

Aside from that, use of tblDailyVehicleTrip is arguably unnecessary. If
each trip is recorded as a separate record in the junction table
tblVehicleTrip the date can be stored as well as the time. After that it is
a matter of grouping on the date or by some such means organizing the
information as needed.

 

[Ed from AZ]

Wow, Steve! Thanks so much for such a detailed reply!! I envy those
who can see a structure like this at a glance. Of course, I presume
you also have quite a bit of experience doing this stuff!

If I base the input of the number of circuits per route on a subform
like you have outlined, won't I see every route available on the input
form? That's what I was trying to avoid. Not all vehicles travel all
routes, and I don't want to give my users that much opportunity to
enter anumber in the wrong field. At least by having to select from a
drop-down, there's possibly greater odds that the correct route would
be selected.

Ed

 

[John... Visio MVP]

I provide help with Access, Excel and Word applications for a reasonable
fee. If you need help with this, contact me. My fee would be very modest.

Steve

These newsgroups are provided by Microsoft for FREE peer to peer support.
Stevie is a known troll who likes to harrass posters for work. His help is
not worth the time or money.

As to a so called expert in Word and Excel, stevie has posted some very
basic questions to the Word Newuser group that indicates he has no skills in
the area.
1) how do you convert from portrait to landscape
2) How do you moved cells in a table
3) Offering to convert a mailing list to an Access database. The poster
never asked for this and just wanted a mailing list


John... Visio MVP

 

[BruceM]

I still don't see the point of TblDailyVehicleTrip. Steve has presented a
sketch of one way to do this. However, it is not clear (at least not to me)
whether you mean to start with a route and select a vehicle, or start with a
vehicle and select a route, or start with a day and input the route and
vehicle. This third option is what Steve seems to have suggested. I think
one of the first two options would provide the most efficient structure.
You can store the date along with the time (you will be doing that anyhow if
you enter the time by means of the Now() function into a Date/Time field).
In terms of the interface you could go to either a Vehicle or Route form
that has a subform based on tblVehicleTrip. In that case tblVehicleTrip
would be something like:

tblVehicleTrip
VehicleTripID
VehicleID
RouteID
VehicleTripTime

If the form is based on tblVehicle, the subform based on tblVehicleTrip
would inculde a combo box bound to RouteID, with a tblRoute as its Row
Source. If the form is based on tblRoute the combo box will be bound to
VehicleID, with tblVehicle as its Row Source.

The form based on tblVehicle could be a query including tblVehicleType, if
you wish. However, I repeat that I do not see the need for a table
(tblDailyVehicleTrip) that exists solely to store the date. That data is
already in the junction table (TripTime).

Wow, Steve! Thanks so much for such a detailed reply!! I envy those
who can see a structure like this at a glance. Of course, I presume
you also have quite a bit of experience doing this stuff!

If I base the input of the number of circuits per route on a subform
like you have outlined, won't I see every route available on the input
form? That's what I was trying to avoid. Not all vehicles travel all
routes, and I don't want to give my users that much opportunity to
enter anumber in the wrong field. At least by having to select from a
drop-down, there's possibly greater odds that the correct route would
be selected.

Ed

 

[strive4peace]

Hi Ed,

before you think about forms, you need to get your data structure set
up. Each table is, essentially, a noun or event that you can visualize.
the fields for each table describe that noun. There are other types
of tables, such as cross-reference tables that serve to link tables
together

You will have a table for Vehicles and a table for Routes. The Routes
table may also have a related table with Route details.

If certain vehicles are limited to the routes they cover, then you can
use a cross-reference table to link Vehicles and Routes -- and this
table could be used to limit the routes that a user would pick from.

for help on setting up your tables and relationships, read the
Normalization and Relationships sections of this document:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace



Warm Regards,
Crystal
remote programming and training



*
have an awesome day
*

 

[Ken Sheridan]

Ed:

Firstly, take note of John and Bruce's comments re Steve, who is well known
here for attempting to solicit work.

Both your current table design, with one column per route, and your
alternative, with one table per route, are flawed. The former amounts to
what is known as 'encoding data as column headings'; the later similarly
encodes data, but as table names. A fundamental principle of the database
relational model is that data is stored as values at column positions in rows
in tables, and in no other way.

What you need are a set of related tables, each of which models an entity
type, and each of which has columns which represent the attributes specific
to the entity type. Your main entity types are VehicleTypes, Vehicles and
Routes. The first two are related in a one-to-many relationship type, the
latter two in a many-to-many relationship type. A many-to-many relationship
type does not exist directly between tow tables, but is modelled by an
additional table which resolves the many-to-many relationship type into two
one-to-many relationship types.

The additional table, Trips say, references the Vehicles and Routes tables
by having two foreign key columns, VehicleID and Route which reference the
primary keys of the two referenced tables. Even though this table is
modelling a relationship type, it is also modelling an entity type as an
relationship type is really just a special kind of entity type. Consequently
it will also have other columns representing attributes of each trip, e.g.
TripDate and NumberOfCircuits. The primary key of this table, if I
understand you correctly, would be a composite one of VehicleID, Route and
TripDate, i.e. there can be only one row in Trips for each
vehicle/route/date, but the vehicle can undertake multiple circuits of the
route per day as indicated by the value in the NumberOfCircuits column.

On that basis, diagrammatically the model would look like this:

VehicleTypes----<Vehicles----<Trips>----Routes

where the < and > signs indicate the 'many' end of each one-to-many
relationship type.

However, you also have a constraint that not all vehicles travel all routes,
so you need to extend the model to take account of this by realting each
vehicle to the routes it cab travel. Again this is a many-to-many
relationship type, so you need a table, VehicleRoutes say, with columns
VehicleID and Route. The model now becomes:

VehicleTypes----<Vehicles----<Trips>----VehicleRoutes>----Routes

The relationship between Trips and VehicleRoutes is now on two columns
VehicleID and Route. This allows the constraint to be enforced as a row can
only now be entered in Trips where both columns match in VehicleRoutes.

You'll notice that I've suggested a Route foreign key column rather than
RouteID. Consequently the Routes table would have a Route primary key. This
would be the text description of the route, a so-called 'natural' key rather
than a numeric 'surrogate' key. This is to cater for your 'elegant' solution
to limiting the choice to only those routes not yet selected for the
vehicle/date. A numeric surrogate key would rule this out unless you resort
to the use of a 'hybrid' control, i.e. a text box superimposed on a combo box
(not a list box BTW, that's something different) to look like a single
control. When using a natural key like this its

For data entry into the Trips table you can use a self-contained continuous
view form bound to the table, or a continuous view subform within a single
view form based on the Vehicles table. I'd suggest the latter, linking the
subform to the parent form on VehicleID. If you want to limit the subform to
a particular day at any one time then put an unbound combo box, cboDates, in
the parent form. For its RowSource you could draw on a Calendar table, i.e.
a table which list all dates over a range (an easy way to create one is to
serially fill down a column in Excel and import it into Access as a table),
or you can show all dates 30 days before and after the current date for
instance by putting the following in the form's Open event procedure:

Dim n As Integer

Me.cboDates.RowSourceType = "Value List"

For n = -30 To 30
Me.cboDates.AddItem DateAdd("d", n, VBA.Date)
Next n

Me.cboDates = VBA.Date

This would also default the control to the current date.

You'd then include this n the link between the main parent form and the
trips subform by seetting the latter's LinkMasterFields property to:

VehicleID;cboDates

and its LinkChildFields property to:

VehicleID;TripDate

In the subform you don't need to include any controls bound to the VehicleID
or TripDate columns as the values will be automatically entered into the
underlying fields by the linking mechanism. For NumberOfCircuits you'd have
a text box and for Rouite a combo box with a RowSource of:

PARAMETERS
Form!VehicleID LONG,
Form!TripDate DATETIME;
SELECT DISTINCT Route
FROM VehicleRoutes
WHERE VehicleID = Form!VehicleID
AND NOT EXISTS
(SELECT * FROM Trips
WHERE Trips.Route = VehicleRoutes.Route
AND Trips.VehicleID = VehicleRoutes.VehicleID
AND Trips.TripDate = Form!TripDate);

Note that you can use the Form property here to refer to the subform rather
than having to explicitly reference it.

To get the combo box to show only those routes not yet selected for the
vehicle/date you need to requery it in the subform's Current event procedure
with:

Me.Route.Requery

assuming that the combo box has the same name as the field to which its bound.

For tracking vehicles/routes its just a question of joining the Vehicles and
Trips tables in a query, grouping on VehicleID and Route, restricting the
results to a date range (which could be one or more days) and summing the
NumberOfCircuits value, e.g.

PARAMETERS
[Enter start date:] DATETIME,
[Enter end date:] DATETIME;
SELECT VehicleID, Route
SUM(NumberOfCircuits) AS CircuitsCount
FROM Vehicles INNER JOIN Trips
ON Trips.VehicleID = Vehicles.VehicleID
WHERE TripDate BETWEEN [Enter start date:]
AND [Enter end date:]
GROUP BY VehicleID, Route;

Note that its always best to declare date/time parameters as otherwise a
parameter value entered in short date format could be interpreted as an
arithmetical expression rather than a date, and give the wrong results.

You can of course include other fields from the Vehicles table in the
query's SELECT clause, e.g. the vehicles registration number, provided you
also include them in the GROUP BY clause.

Ken Sheridan
Stafford, England

 

[BruceM]

My point was to take one away. TblDailyVehicleTrip is redundant.

 

[Ed from AZ]

First, thanks to all who have replied with Access help. I am trying
to pick my way through here and I am grateful for all help.

Next, thanks for the "troll" warnings. Unfortunately for Steve, my
company isn't going to pay for this and neither am I. About the only
I'd pay for in the near future is a good on-line course in Access!!

Another question, if I may. Please understand that I am not bucking
against any advice - rather I'm trying to comprehend what's going on
in Access. I've used Excel for years, but this little project really
needs a database, especially since several people are going to be
using it over the network. I do get that data structure needs to come
first and the tables are created around that, not vice versa. I'm
just trying to understand what good data structure is at the "For
Dummies" level!

What if I have a table holding my data that simply has fields for
VehType, VehSN, Date, Route, and Circuits? I have tables for types,
SNs and routes, and can add new items to these as required. Would
that be simpler data table to work with?

Ed

 

[Gina Whipp]

Steve,

Why should he PAY for ANYTHING in a FREE support group????

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index.htm

 

[Gina Whipp]

Ed,

Crystal has a EXCELLENT FREE tutorial on Access that might just what the
'Doctor' ordered...

Access Basics - FREE
8-part FREE tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index.htm

 

[strive4peace]

Hi Ed,

do not use DATE as a fieldname or controlname, it is a reserved word

Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html

~~~
So that we can better understand, what are you calling a Route? A set
path through an area? Visits to specific places?

for Circuits, correlating with your first post, I assume you mean
NumCircuits? It is important to be descriptive (yet concise) with your
fieldnames

do you not need to store any details about each circuit?

If you have a table for SNs, then that table could have an autonumber
field called SnID. The related table could store a Long Integer SnID
that correlates to the vehicle.

The VehType table would also have an autonumber field, maybe called
VehTypID. Since a vehicle type describes a vehicle and not a route, I
am assuming that the SNs table lists your vehicles and that is where the
field would go.

When you enter route information, you could make a combobox to get the
SnID that also shows the vehicle type.

the RowSource for a combo can come from anywhere -- as long as the bound
column (the first one if you do not change it) is what you want to
store, so you could do something like this:

combo properties:

Name --> SnID
ControlSource --> SnID

RowSource:
SELECT SnID, VehicleDesc, VehType
FROM Vehicles INNER JOIN VehTypes
ON Vehicles.VehTypID = VehTypes.VehTypID

ColumnCount --> 3
ColumnWidths --> 0;2;2
ListWidth --> 4 (sum of column widths + 0.2" for scrollbar)

then, when a choice is picked, VehicleDesc will show up since the first
column width is zero.

if you want the vehicle type to show up as well, you can make a textbox
on the form with this as its ControlSource:

=SnID.column(2)

the reason you do not use column 3 is that column indexing starts at 0,
not 1


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[John... Visio MVP]

Ed,

Let's go back to your response to my reply to your original post....
"Wow, Steve! Thanks so much for such a detailed reply!! I envy those
who can see a structure like this at a glance. Of course, I presume
you also have quite a bit of experience doing this stuff!"

Why did you let Visio John sway you? Did you notice that nowhere in the
thread did he offer anything to help you? Do you think that his conduct
stands up to the conduct to be expected of an MVP? Do you think I would
have wasted my time writing my response if I wasn't sincerely wanting to
help you? You are back on the wrong track because you let Visio John steer
you off track!! Visio John doesn't care about you!!!

Steve

BTW, would you have paid $100 to get this all working for you?

Stevie, stevie, stevie you definitely are off your medication.

You are quoting a newbie. Any answer would be looked upon as wisdom. It is
also obvious that you have been able to bluff them with your limited
knowledge into believing you are an expert. Anyone who has been around the
Access, Excel or Word newsgroups know that you are far from an expert. What
about all those amateur questions you posted to the Word New User group in
the past few weeks. Is it really true you can not figure out how to convert
a form from portraitto landscape?

As to not offering help, did you miss the golden nugget I gave where I
warned that dealing with you would be a waste of time and money. That was
far more valuale than any contribution you have made to these newsgroups in
years. I am not ashamed of my conduct, keeping these newsgroup safe from con
artists IS a valuable service.

As to "Do you think I would have wasted my time writing my response if I
wasn't sincerely wanting to help you?" The answer is yes, you are never
sincere about helping posters, your sole motivation is to help yourself.

So how did I steer the poster off track? I just warned the poster about your
behaviour. Oh wait, I forgot, yours are the only true and right answers.
None of the other posters know what they are doing.

I definitely care about the users who post here, even you, except that I
wish you would stay on your medication.

John... Visio MVP and Sanitation engineer

 

[BruceM]

One big difference between Excel and a relational database is how you think
about data storage. A table contains information about a single entity. In
a listing of people you could say that name, date of birth, etc. are
attributes of a person. In some cases you could regard address in the same
way, but if you are keeping a listing of, say, church members, you probably
want to list households separately from individuals living at the address.
If the family moves you only need to update one record, the address record.
You have been given a link to a very good tutorial that will explain a lot
about database structure. Here is another that deals specifically with what
is called normalization:
http://www.datamodel.org/NormalizationRules.html#five

Some of it gets pretty esoteric, but in general third normal form (3NF) is a
reasonable design target.

To the question at hand, Date (as mentioned, a reserved word) is not an
attribute of a vehicle (unless it is purchase date or some such), nor is
Route or Circuit, as I understand them. Looking just at Vehicle and
VehicleType tables, one type can be a characteristic of several vehicles,
but presumably each vehicle is of a single type. Here are the basic tables:

tblVehicle
VehicleID (autonumber primary key)
TypeID (related to TypeID from VehicleType table)
(alternatively to TypeID, TypeDescription)
Model
Make
ModelYear
Color
etc.

tblVehicleType
TypeID (autonumber primary key)
TypeDescription

Best choice probably is to create a relationship (one to many) between the
two TypeID fields (one type > many vehicles), and store TypeID. You could
just store the Description, if there are no other fields related to
tblVehicleType. However, what was once a microbus is now a minivan, etc.,
so storing the actual description is best left to limited situations. Also,
even if there is only a description now you may want to add another field
some day (load capacity, or whatever). It is better in that case if you are
storing the ID, as any new fields, modified descriptions, etc. can easily be
brought along.

In the case of Vehicle and Route, each Vehicle can travel several Routes,
and each Route may be traveled by several Vehicles. This is a many to many
relationship, which needs a third table (a junction table, among other names
used) to resolve since it can't be created directly between two tables.
This is where a VehicleRoute table comes in.

Others have described this, so I won't go further into it here.

 

[BruceM]

Now you sound desperate. The OP has specifically stated you will not be
getting paid, yet you make an unsolicited low-ball bid. The OP, along with
many others, must wonder why somebody with the expertise you claim has to
resort to your methods for attempting to find work.

 

[gilles TENA]

Your tables should look like:

TblVehicleType
VehicleTypeID
VehicleType

TblVehicle
VehicleID
VehicleName
VehicleTypeID
<Othe Vehicle Fields You Need>

TblRoute
RouteID
RouteDesc

TblDailyVehicleTrip
DailyVehicleTripID
VehicleID
VehicleTripDate

TblVehicleTrip
VehicleTripID
DailyVehicleTripID
RouteID
VehicleTripTime

1. You need a query named QryFrmDailyVehicleTrip that includes the
tables TblVehicle, TblVehicleType and TblDailyVehicleTrip.
2. You need a query named QrySFrmVehicleTrip that ibcludes the tables
TblVehicleTrip and TblRoute.

For data input use a form/subform. Base the main form on
QryFrmDailyVehicleTrip and base the subform on QrySFrmVehicleTrip. Set the
LinkMaster and LinkChild properties to dailyVehicleTripID. This
form/subform will allow you to enter and view all the trips made by a
selected vehicle on a selected date.

A query based on TblVehicleTrip can give you what routes were travelled by
a selected vehicle on a given day or given time period and a similar query
can give you how many trips were made by a selected vehicle on a selected
date or over a given period.

I provide help with Access, Excel and Word applications for a reasonable
fee. If you need help with this, contact me. My fee would be very modest.

Steve
(e-mail address removed)

 

[Ed from AZ]

Hi, Steve. I will give you an individual reply, because I believe a
person deserves the respect of "personal" contact (if such there be
over the Internet!!).

Ed,

Let's go back to your response to my reply to your original post....
"Wow, Steve!  Thanks so much for such a detailed reply!!  I envy those
who can see a structure like this at a glance.  Of course, I presume
you also have quite a bit of experience doing this stuff!"

And I stand by that. Nothing I have read or said has taken me away
from that initial impression. Then again, as John said, "You are
quoting a newbie. Any answer would be looked upon as wisdom." You did
see an entire structure where I am floundering. I have no doubt that
had I constructed what you gave me, it would have worked much better
than what I had. Then again, is it what will really do the job I need
it to do? I don't know. Other have asked me questions to draw out of
me the significant details that I didn't know I needed to give or
consider. That impresses me also.

Why did you let Visio John sway you? Did you notice that nowhere in the
thread did he offer anything to help you? Do you think that his conduct
stands up to the conduct to be expected of an MVP?

I listened to him as I listened to you. He is an MVP, which does mean
something to me, having been around the MS newsgroups for some years
in Word, Excel, and other areas. As he knows you better than I do, I
wouls tend to listen to him. Especially when he is echoed by others.
I would sincerely hope that an MVP not only dispenses nuggets of
truth, but also points out nuggets of a different type.

Do you think I would have
wasted my time writing my response if I wasn't sincerely wanting to help
you?

I don't know what you would do. I just got here. Others know what
you would do, apparently because it's what you habitually do.

You are back on the wrong track because you let Visio John steer you
off track!! Visio John doesn't care about you!!!

Steve

I don't think _anyone_ here can "care about me" - no one here knows me
very well yet. I am not a regular Access user (though I've dabbled
from time to time and really do intend on learning this stuff some day
- really!!), so I don't expect anyone to know me or what I need beyond
the few words I have written. In fact, I am suspect of people who
passionately "care" when they don't know me - I have to wonder what
they _really_ care about.

BTW, would you have paid $100 to get this all working for you?

No. Never. As I said, I have been around these newsgroups - mostly
in Word and Excel groups - for several years. I have had MVPs and
others stay with me for over a week, often writing whole macros for me
until I learned what was going on enough to pick up the ball and run
with it. I've never been charged a dime! Some have an offer of paid
services in their signatures, but they've never made a blatant
solicitation of paid services directly to me. Because of their
efforts, I have reaped cash awards and major brownie points at work.
But some of my best rewards have been when *I* could go into the
newbie forums and answer questions for others.

Bottom line, Steve - I have no doubt that you know much more than I do
and that your help was indeed good. But that request for payment
coupled with the warnings from others that you have a history of this
has given me pause in dealing with you. It's not just about the
information - it's also about who I am dealing with. I wish you the
best in your endeavors, but I think I will pass by your posts for now.

Ed

 

[John... Visio MVP]

I don't think _anyone_ here can "care about me" - no one here knows me
very well yet. I am not a regular Access user (though I've dabbled
from time to time and really do intend on learning this stuff some day
- really!!), so I don't expect anyone to know me or what I need beyond
the few words I have written. In fact, I am suspect of people who
passionately "care" when they don't know me - I have to wonder what
they _really_ care about.

Ed


After several decades in the online community, I think I can give you some
insight in to that. The regulars who post here and in other newsgroups are
passionate about the product and like to help. The simple fact that you
posted a question is all they need to care. It shows that you are interested
in something they are passionate about. As to their motivation, four
possibilities are:
1) They were helped in the past and now they are returning the favour.
2) They are London Times crossword puzzle fans and are always looking for
that next challenge.
3) They like to put their skills to the test.
4) They enjoy showing off.

John... Visio MVP

 

[John W. Vinson]

1) They were helped in the past and now they are returning the favour.
2) They are London Times crossword puzzle fans and are always looking for
that next challenge.
3) They like to put their skills to the test.
4) They enjoy showing off.

Guilty as charged, your honor!

Plus I've learned far more about Access from figuring out the answers to
questions I'd never have thought to ask then from any other source.