Using Win-8.5 advanced.

I have a senior snag trying to get my arms around a project for tracking
maintenance for a fleet of boats (or cars, etc.)

I have the Tables below
Vessel Info
...Vessel Name, Model, manufacturer, Vessel_ID
Mechanical Components
...Type (Engine, Transmission, Generator, etc.) Vessel Name,
Component_ID
Parts (for Mechanical Components)
...Type (Oil filter, Air FIlter, Lube oil, etc.) Vessel Name, Part_ID

I link Vessel Info to Mechanical Components via Vessel Name.
I link Parts to Mechanical compenents via Part_ID

In the portal on Mechanical Components, I can create new part records OK

I want to go to a Parts record and show which Vessels and which Mechanical
Componets use this part (via a portal).

Curently, each Parts record shows the Vessel & Mechanical Components which
uses it, but i'd like a summary (so to speak) via a portal.

Any ideas.



--
-------------------------------------------------
Captain Guy
s/v Island Time (Beneteau 352#277)
AICW 845.5
386-689-5088

In article <HlBei.6555$tb6.2968 (AT) newsread3 (DOT) news.pas.earthlink.net>
"Guy"<sailor (AT) guym (DOT) com> wrote:

What if the vessel name is changed (new owner's wife doesn't like the
boat being named for the previous owner's girlfriend)? Wouldn't
something that doesn't change (Vessel_ID, maybe?) be a better choice?
I'm not getting what you're asking. If the related information is
showing up properly, what's preventing you from setting up a portal?
Matt

In article <HlBei.6555$tb6.2968 (AT) newsread3 (DOT) news.pas.earthlink.net>,
"Guy" <sailor (AT) guym (DOT) com> wrote:

Several thoughts:

First:

This is really important:

It is a bad idea to use an editable field as a key field in a primary
relationship in a database. So I advise you not to use Vessel Name for
this purpose (or the engine ID, etc). Instead, define a primary key
field kpVesselID, with value filled automatically by serial number when
you create a new record, and prohibit modification of the value by the
user. That will assure the relationships are not broken if the name of
the vessel (or any other editable field gets changed.

You should apply the same practice to all the other tables and primary
relationships of the database. Even such things as engine serial numbers
are theoretically alterable (to correct a data entry error, for
example), and there is no guarantee that different engines from
different manufacturers will not have duplicate serial numbers. So, let
the database take care of making sure all the records in each table are
uniquely and unalterably identified by automatically-generated serial
numbers that will not be duplicated (within any one table) and will not
be altered by users.

Second:

You could construct many-to many relationships between vessels,
components, parts, etc. so that one vessel can have many components of
the same kind, one kind of component can be installed in many vessels,
one component can have many parts of the same kind, one kind of part can
be installed in many different kinds of components, etc. To set up this
kind of relational structure, you need intermediate Join tables. One
join table would connect vessels to components, and another join table
would connect components to parts, thus:

Vessel::kpVesselID = Vessel_Component::kfVesselID
Component::kpComponentID = Vessel_Component::kfComponentID

Component::kpComponentID = Component_Part::kfComponentID
Part::kpPartID = Component_Part::kfPartID

Where:
Vessel is the Vessel table
Component is the Component table
Vessel_Component is the Join table that joins vessels to components
Part is the Part table
Compnent_Part is the Join table that joins components to parts

The notation
Vessel::kpVesselID signifies a field that is part of the Vessel table,
in this case the kpVesselID field, which is the primary key field for
Vessel, which is populated by unique, unalterable serial numbers
assigned by the database.

Vessel_Component::kfComponentID is a field that belongs to the
Vessel_Component join table, in this case the foreign key field for
Vessel ID to join the Vessel to the Join table

And so on.

After you set this up, you can define layouts that show any desired
combination of data from related tables, including portals to show
multiple related records. It is easy to set up such a layout for Parts
that shows all the Components that have that part, and another that
shows all the Vessels that have that part, or conversely a layout in
Vessel that shows all the components of a vessel and another that shows
all the parts of a vessel.

BY the way, the Join tables could have a fields for quantity of each
part assigned to a components, and for quantity of each kind of
component assigned to a vessel.

Or, if you want to track individual components or parts by their own
manufaturer's individual serial numbers, you could have a field in the
Join table for manufacturer serial number (make it a text field, as
manufacturer serial number often contain letters and other non-numeric
characters), and then assign each to a different Join record.

Third:

You may already have a lot of records in the database, and naturally
don't want to go back and re-enter them in order to convert over to this
many-to-many scheme with relationships based on automatically generated
serial numbers. There are ways to help with this challenge, by use of
the scripts and the Replace Field Content step, and by means of doing
imports.

As a first step, you would define the primary key fields as serial
numbers, then do a Replace Field Contents for that field with serial
numbers, with the box checked to update the next serial value. be sure
you have done Show All Records before you do the Replace Field Contents,
so that all the record in each table will get their assigned serial
number.

Then define the Join table, with appropriate serial number primary key
fields, and foreign key fields to hold the related key values.

Then import appropriate fields of the main tables into the Join tables,
and then do cleanup as needed.

You can thnok through the logic of this to get all the details, and then
script it, and run through it to make sure it works and you end up with
a functional system.

I strongly recommend that you save a copy of the database as it is
before doing any modifications, then save copies at suitable points in
the process as you go along, so that you can recover from any errors you
may make in the process.

If you do not have may records in your database, it would be easier and
safer to enter the data fresh with the new setup.

--
For email, change <fake> to <earthlink>
Bill Collins

Thanks Bill... see below
"Bill" <bbcollins (AT) fake (DOT) net> wrote

YES, I do not use editable fields for the keys, but for ease of dicussion
and prototyping I use vessel name... another reason not to use name is there
may be a few un-named vessels (eg a dozen kayaks) that may have a unique
stock number (kayak #1, #2, etc.) and a non-editable key is of course
necessary.


GOOD IDEA...
NO, I do not have any records other than prototyping records... this is part
of a much larger app I'm developing for the market.

YES, I do not use editable fields for the keys, but for ease of dicussion
and prototyping I use vessel name... another reason not to use name is there
may be a few un-named vessels (eg a dozen kayaks) that will have a unique
stock number and a non-editable key is of course necessary.

I'll add quantity, manuf serial numbers (unlikely for consumables like spark
plugs, filters)... I have another table for "Ship's Inventory" for
electronics, etc. that have serial numbers, etc.

I also need to include maintanance ticklers and P.O.'s based on parts, life
cycle, etc.

The other components include Names of customers, suppliers and crew...
reservations of vessels and tour reservations... crew pay and
licensing/certifications. etc.

On Jun 21, 7:52 pm, "Guy" <sai... (AT) guym (DOT) com> wrote:
I wouldn't say it eased the discussion