well, i have a database that stores test data for material certifications.
different cert types require different *sets of tests*. i made a table that
lists all the cert types, called tblCertTypes. then i made a table that
lists all the available tests, called tblTests, with a Yes/No field that
designates whether the test value is numeric or text, because some test
values will need to be treated as Number data type, some as Text data type.
then i made a linking table, called tblCertTypeTests that lists all the
specific tests required for each cert type.
then i made a table to hold finished test records, called tblLabTests, which
stores the cert type, the test date, and the name of the lab technician who
performed the tests. and a child table called tblLabTestDetails to hold the
individual test values for the *set of tests* for the test record stored in
tblLabTests. tblLabTestDetails has a primary key field, a foreign key field
linking it to tblLabTests, another foreign key field linking it to tblTests,
and a text field to store the test value.
consider a similar setup for your situation, where tblCertTypes corresponds
to tblFacilityTypes, and tblTests corresponds to a table that lists all the
information that will be needed for all the facilitiy types
(tblRequirements), as "passenger number allowed", "weight allowed to carry",
"radius load allowed", "serial number", "activiation pressure", "boiler
license", etc. and tblCertTypeTests corresponds to a table
(tblFacilityTypeRequirements) that lists all the specific information
required for each specific facility type. now you have the setup to make a
child table (T_FacilitiesRequirements), related back to your T_Facilities
table, in which you can store all the information for each facility record
in T_Facilities.
expanding facilities is made easy. no new tables, queries, forms, etc. just
add a new record to tblFacilityTypes, add any new records needed to
tblRequirements, add new records in tblFacilityTypeRequirements to identify
the specific information required for the new facility type. then add your
new facility to T_Facilities, and add its' details to
T_FacilitiesRequirements.
the form level is much easier also: one main form, bound to T_Facilities,
with one subform, bound to T_FacilitiesRequirements.
hth
Hello -
Thanks for your reply.. I will try and explain the table design.
I will give an example of the facility so you can understand why I design
it
the way I did.
The facility we are talking about are for example - Elavators (Type1),
Lifting Machines (Like cranes) (Type2), Steam Boilers (Type3), etc.
Each Type includes identical information fields, such as [description],
[manufacturer], [Location] in the customer area, [FacilityType], etc. this
information is in one table - T_Facilities. For each record I also keep
the
"Facility Type" field.
For Each Facility Type, there are fields that are unique for this
facility.
For an example -
Type1-Elevator (table name "T_1Elevator")- [Passenger Number allowed],
[Weight allowed to carry], [passenger or luggage elevator], etc.
Type2 - Lifting Machine (table name "T_2Lifting") - [Radius Load allowed],
Serial Number, Safe load, etc.
Type3 - Steam Boiler (table name "T_3SteaBoiler") - Pressure allowed,
Activation Pressure, Boiler Licenses, etc.
The Field that connect the T_Facility with the T_xFacilityType is the
Primary key of the Facility itself, and it connected with one-to-one
relationship.
This is the reason why I keep separated tables for the different
facilities
and one that includes the basic data.
I can tell you that if a new facility will be needed - its correct I will
need to define a new set of table, queries, forms and so on, and it is
taking
into consideration.
The [Type] field in the table T_Facility is enables me to define what kind
of subform I need to show in order to fill in the unique values for each
facility.
I addition, I open another form in order to fill more unique info for the
facility, therefore I don't see any other option rather that define one
major
table and a uniqe info tables for the different facility types. I can tell
you that there is no parallel information in the facilities tables, and
they
all includes 7 or more fields that are unique for this specific facility.
This is the explanation. I hope I made clear the purpose of the table
design. And I hope I convince you that this is the best way. Please do
tell
me if you think of a better way to do this (after you know all the above).
Given this information - can you think of a better way to show information
on a sub form, instead of show/hide the sub forms according to the
facility
type.
Thank you, I hope I didn't tire you with all this information.
Alu.
--
Alu_GK
:
On Sun, 26 Oct 2008 08:47:00 -0700, Alu_GK
<
[email protected]>
wrote:
I'm running a db for a Facilities report.
I have 8 kind of facilities.
I've created 1 table that includes the fields that are identical in
those
facilities - "T_Facility" (such as PK, facility name, facility
location,
facility manufacturer, etc..), and separated the rest of the fields
into 8
different tables, each table relate to a certain facility.
Ummm... sorry. That's not a correct design. What will you do when they
open
two more facilities? Two new tables, all new queries, all new code!?
You do not store data (the identity of a facility) in a table name.
Instead
you store the facility's identity *as data in a field*. Rather than
multiplying the number of tables, you add new *RECORDS* to your table.
In the table "T_Facility" I have a field the identify the type of the
facility (e.g. Facility1, Facility2, Facility3.) by a Facility Group
Number.
I've created a form that has 8 (!!) subforms, and on the load event of
the
main form the relevant form is shown (subform_facility1.visible=true)
and the
other are visible=false.
I need an advise how to make it better. This method load all the 8
forms
even though they are not all shown.
One form, one subform - filtered by the identity of the facility.
I tried to change the source object of a sub form, in an on open event,
according to the facility group in the main form, but it's not working.
A Subform will actually be populated BEFORE the mainform opens, so the
mainform's Open event is too late.
I've create an event on open, that uses
"select case FacilityNum
Case 1
Me.subForm.Sourceobject = Facility1
Me. subForm.LinkChildFields = nFacilityIx
Me. subForm.LinkMasterFields = nFacilityIx
Case 2
Sourceobject = Facility2
Me. subForm.LinkChildFields = nFacilityIx
Me. subForm.LinkMasterFields = nFacilityIx
Case 3
. etc.
Case else
End select
The Facility1, Facility2, are the facility Group / Type (8 Facilities
as I
mentioned)
The nFacilityIx is the unique identifier of each facility (endless
items).
The field nFacilityIx are both in the main and in the sub-forms, so it
can
be used for all the subforms as child and master field.
The code that I've write is not working properly, and I will be happy
to
receive any help on that.
Thank
Stop, step back, and rethink your table design is my best advice. Any
time you
have eight identical or basically-identical tables you're on the wrong
track!
