Hi Wally
If you have never done a database before, the basic steps are:
1) Create the tables to hold the data, and the relationships between them.
2) Create the forms to enter/find/change/delete the data, and the event
procedures to ensure everything works right.
3) Create the reports so you can print out the data. Most reports need a
query that gets data from multiple tables.
4) Test the database works correctly, and document how it works.
The first step is where everyone goes wrong on their first database. The
essential concept is to create separate tables where one thing can have many
things related to it. For example, one vehicle can break down many times.
Therefore you need a Vehicle table and a Breakdown table, like this:
Vehicle table has fields:
VehicleID AutoNumber primary key
Rego Text registration plate
Brand Text Ford/GM, etc.
AcqDate Date/Time Date your company acquired this veh.
...
Breakdown table has fields:
BreakdownID AutoNum primary key
VehicleID Number which veh. (from table above) broke
down.
BreakdownDate Date/Time when this breakdown occurred.
ReturnToService Date/Time when the repair was completed.
ReportedToID Number Staff member this was reported to.
BreakdownDescrip Memo description of the problem.
The ReportedTo person will be one of the staff, so it makes sense to add
another table of staff, and use a combo box on your form. This guarantees
that your data is correct (i.e. you don't just misspell someone's name.)
Therfore you need another table of Staff with fields:
StaffID AutoNum primary key
Surname Text
FirstName Text
and so on. (Notice that we split Surname and Firstname into 2 fields: that's
another basic rule: only store ONE thing in one field, i.e. create a
different field for every different thing.)
You will also have repeating data in the Brand field, so again this becomes
another lookup table of brands, and you will use a combo box on your form.
Similarly, you probably want another field in the Vehicle table for
VehicleType (trailer, pickup, prime mover, crane, ...), so this will also be
a lookup table containing the possible types for the drop-down list.
One breakdown might need many actions to fix it. That means another
one-to-many relation, so another table for Action:
ActionID AutoNum primary key
BreakdownID Number which breakdown this action was for.
ActionDate DateTime needed if repairs take several days.
ActionByID Number who performed this action
If the actions are performed by staff, then the ActionByID will relate to
the StaffID of the Staff table. If they are performed by external
businesses, you will need another table of those businesses.
I cannot emphasise enough the importance of mapping out these relationships
on paper before you start. For a basic example of where you are going, open
the Northwind sample database that comes with Access, and choose
Relationships on the Tools menu. Examine how one customer can have many
orders, one order can have many line items, one product can turn up in many
line items, and so on.
Once you have a structure that copes with *everything* you need, build the
tables, and then create the relations (Tools | Relationships.) Always check
the box for Relational Integrity when you create a relation.
BTW, there are probably lots of other tables too if you need to track the
parts for an action (table of parts, junction table of ActionParts), labour
(many labour items for an action, by different staff/businesses, at
different rates), costs, the staff normally associated with a vehicle and
therefore the person(s) who lost time during the breakdown, the job(s) the
vehicle would have been performaing, and therefore the increased costs of
those jobs, and so on.
If you do go on to the later steps and create forms/reports without getting
the data structure right, you will have to redo those forms, queries, and
reports, so I can't emphasise enough the importance of getting the structure
right.
Don't forget to think outside the box about what things are not covered by
your structure. For example, does it cope with wrecks as well as breakdowns?
Does it need to cope with lost time due to other causes (e.g. driver
illness?)
Hope that's a help to get you started.
