Relatioships and like/unlike data

[Guest]

I am creating a database to keep track of electrical circuits and one table
has information about specific panels and another has circuit information to
a specific circuit in a panel, My primary key in the panel table is the
nomenclature for the panel, in the circuit table the field for the circuit
number comprises of the nomenclature with the number of the circuit added to
it so panel F2UPB20A would have circuit F2UPB200A9 with 09 being the circuit
in the panel. Is there a way to relate F2UPB20A to F2UPB20A09 directly or
will I have to create a child field in the circuit table to place F2UPB20A.
Hope this makes sense and thank you in advance.

 

[Tom van Stiphout]

On Fri, 26 Oct 2007 00:08:01 -0700, troubledinaccess

No.
The latter.

-Tom.

 

[John W. Vinson]

I am creating a database to keep track of electrical circuits and one table
has information about specific panels and another has circuit information to
a specific circuit in a panel, My primary key in the panel table is the
nomenclature for the panel, in the circuit table the field for the circuit
number comprises of the nomenclature with the number of the circuit added to
it so panel F2UPB20A would have circuit F2UPB200A9 with 09 being the circuit
in the panel. Is there a way to relate F2UPB20A to F2UPB20A09 directly or
will I have to create a child field in the circuit table to place F2UPB20A.
Hope this makes sense and thank you in advance.

Well, you're paying the price of violating the principle of atomicity. You're
putting two distinct pieces of information in the same field - NOT a good
idea!

Note that you can (and in this case, I'd say should) use two (or ten, for that
matter) fields in a primary key. If you have Panel F2UPB20A in the Panel
field, and circuit "09" in the Circuit field, you can easily join them, and
you can also concatenate them for display purposes.

That said... you actually can define a join on a LIKE:

SELECT Panels.*, Circuits.*
FROM Panels
INNER JOIN Circuits
ON Circuits.IDfield LIKE Panels.IDField & "*"

John W. Vinson [MVP]