Heeellllllppppppp!!!!!

[Robert]

I am a truck driver (Contract Operator). I am trying to build a DB to store
(and possibly export to Excel for CPA) information on my trips (i.e., Ld#'s,
Origins, Destinations, Tolls, Fuel (Purchased, Used, Cost), Dates, Addresses
of Points of Origin and Destination (these sometimes are different for the
same customer in the same city or may be in different cities but same
customer name), Fuel Surcharges, Mileage (or percentage) pay, etc., etc.,
etc. Is Access appropriate for me or should I look around for something else.
I think it can be done, but I keep getting confused trying to get this going
because of the interrelationships with all these factors and more as i go
along.

 

[John W. Vinson]

I am a truck driver (Contract Operator). I am trying to build a DB to store
(and possibly export to Excel for CPA) information on my trips (i.e., Ld#'s,
Origins, Destinations, Tolls, Fuel (Purchased, Used, Cost), Dates, Addresses
of Points of Origin and Destination (these sometimes are different for the
same customer in the same city or may be in different cities but same
customer name), Fuel Surcharges, Mileage (or percentage) pay, etc., etc.,
etc. Is Access appropriate for me or should I look around for something else.
I think it can be done, but I keep getting confused trying to get this going
because of the interrelationships with all these factors and more as i go
along.

Access would indeed be quite appropriate. You know your business model better
than we do, of course, so it would not be a good idea for me to suggest
specific table structures; but in a nutshell, you need to identify specific
Entities (real-life people, things or events) of importance, and for each
entity the Attributes (discrete atomic chunks of information about those
entities). Each type of Entity will have a table, and each Attribute will be
stored in a field in that table. You then need to identify the relationships
between your entities.

Just as a couple of examples - again bearing in mind that I don't drive a
truck and don't know the business! - some of your entity-attribute sets might
be:

Trips
TripID <primary key>
StartDate
EndDate
StartMilage
EndMilage

Fueling
FuelingID <primary key>
FuelDate <date and time of fillup>
Amount
UnitCost (ouch!!! these days)
Location

Customers
CustomerID
LastName
FirstName
<other contact information>

etc.

For some tutorials and tips see

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

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

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

 

[Jerry Whittle]

The first thing I always ask before starting on a new project is if there is
already a program out there which will do the job. Something advertised in
the back of trade journals or magazines for example. It the long run they are
often cheaper and faster to buy than creating your own.

Having said that, as a former aircraft mechanic I also know the value of
learning a new, possibly more lucurative, trade. There's no better way to
learn than to have a project that interests you personally.

 

[Robert]

Jerry, thx for the reply, the problems is, is that there are numerous
programs out there that each do a little of what I want(need). Some
more...some only a little. Entering the same information multiple times in
multiple places(programs) takes away from my time, and creates a higher
probability for error and stress. Hence my thought that a centralized DB
would alleviate these potential problem(s). Plus, since Access can export
it's data in multiple formats, then an accting program (etc.) would be able
to import the data thus simplfying everything down. But there are so many
variables to this industry that when I think I've got my head wrapped around
this idea (work-in-progress) some new (or forgotten) item pops up, like an
inventory for repairs or maintenance schedule. Originally had it in an Excel
workbook but it won't pull properly (or maybe I'm just not smart enough,
yet), plus I was wanting to add contacts, etc. to minimize paper/disk usage.

 

[John W. Vinson]

But there are so many
variables to this industry that when I think I've got my head wrapped around
this idea (work-in-progress) some new (or forgotten) item pops up, like an
inventory for repairs or maintenance schedule.

One of the plusses to using Access (or some other relational database) is that
its design is intrinsically modular. If you have a system with eight tables,
and realize that you need a ninth... just add it. You'll need to have a proper
relational design to start with, and will need to make some adjustments to the
user interface; but it's very routine to have a modular application, and add
new modules as needed.

 

[Robert]

One of the plusses to using Access (or some other relational database) is that
its design is intrinsically modular. If you have a system with eight tables,
and realize that you need a ninth... just add it. You'll need to have a proper
relational design to start with, and will need to make some adjustments to the
user interface; but it's very routine to have a modular application, and add
new modules as needed.

Thx John,
I used your idea. It seems like I'm going to have a whole lot of tables, but
it seems easier this way as I will be able to manipulate the data better. It
also seems that i will be able to modify to suit as needs change. Is there a
max limit on tables?

 

[John W. Vinson]

Thx John,
I used your idea. It seems like I'm going to have a whole lot of tables, but
it seems easier this way as I will be able to manipulate the data better. It
also seems that i will be able to modify to suit as needs change. Is there a
max limit on tables?

Way up in the thousands, if I recall. More than you will ever need.

As a rule of thumb, each distinct "kind" of entity should have its own table.
However, if you find yourself with more than one table of identical structure
holding basically the same kind of data - just with different categories - you
should most likely collapse these tables into one, with an additional category
field.