Bring Up Records Within a Form

[J. Trucking]

Hello All,

I am building a maintenance database for heavy equipment. What I have
is a form which shows the information about a particular piece of
equipment - for example a dump truck. I have broken the form into
three tabs: "General Information", "Technical Information", and
"Maintenance History". General Information houses make, model, year,
etc. - but most importantly, the unit number. Technical information
houses tire size, serial number, filter numbers, etc. What happens is
that once the unit is entered into the database, it is not changed.
But, as maintenance/repairs are performed on that unit, I would like
to show that on the "Maintenance History" tab. The problem I am
having with this, is that the maintenance items come from other
tables.

Service/Repairs, Tire Replacement, and Third Party Work are all stored
in different tables. What I would like to do is have a query (or
something along that line) search those three tables for the unit
number listed on the "General Information" tab, and display all
results pertaining to the unit number, in a list/table on the
"Maintenance History" tab. I'm not sure how you format that (if I
need three different subforms on the Maintenance History tab) or if
you can just have one table with a summary of all the records in the
three tables. If possible, I would like to be able to double click an
individual maintenance item to bring up the details of the work.

Well thanks for taking the time to read this long winded speech. Any
advice/help on where I should go from here would be greatly
appreciated. My apologies in advance if this subject has already been
addressed in this forum. I did search for awhile but couldnt find
anything.

Thanks Again,

John

 

[Ken Sheridan]

John:

Whether you can return all the maintenance information as a single result
table of a query depends on how closely they match each other in structure.
If they do then you can use a series of UNION ALL operations. Lets say each
of the tables has columns [Unit Number] (this is essential of course to
establish the relationship with the main table), [Maintenance Date] and
[Maintenance Task] (it doesn't in fact matter if the column names differ from
table to table as long as the data types are the same) the query would go
something like this:

SELECT "Service/Repairs", [Unit Number],
[Maintenance Date], [Maintenance Task]
FROM [Service/Repairs]
UNION ALL
SELECT "Tire Replacement", [Unit Number],
[Maintenance Date], [Maintenance Task]
FROM [Tire Replacement]
UNION ALL
SELECT "Third Party Work", [Unit Number],
[Maintenance Date], [Maintenance Task]
FROM [Third Party Work]
ORDER BY [Maintenance Date] DESC;

You might not need to include the constants "Service/Repairs", "Tire
Replacement" and "Third Party Work" if the Maintenance Task column
sufficiently identifies which is which.

You can then create a form in continuous form or datasheet view based on
this query (you don't need to show the Unit Number on the form) and embed it
on the Maintenance History tab of your form as a subform. The
LinkMasterFields and LinkChildFields properties of the subform control
(that's the control on the tab which houses the subform will both be [Unit
Number]. When a particular piece of equipment is selected on the General
Information tab the subform will show just the data for that piece of
equipment.

If the structures of the tables differ so that you can't use a union query
you'll need to create three separate forms and embed each in the main form on
the maintenance History tab as separate subforms. They'd again be linked on
the Unit Number columns in the same way, but this time you'd see the data
from each separately rather than as a single result set (which you might even
prefer as it would visually categorize the maintenance data and also allow
you to enter new maintenance data for the equipment, which a single subform
based on union query would not, union queries not being updatable).

If the structures of the tables are the same you could combine them all into
a single base table [Maintenance History] and include a column [Maintenance
Type] with values "Service/Repairs", "Tire Replacement" etc to distinguish
the categories. This would in fact be a better design as it doesn’t 'encode
data as table names', but as values at column positions in the table, which a
fundamental principle of the database relational model states to be the only
way data should be stored; what's known in the jargon as "the information
principle".

Ken Sheridan
Stafford, England