Match field name to table name

[Angeldb]

I have a component database consisting of different component tables. e.g.

Table: COMP_UL
Fields: ID, PartNum, Mfr, Model, Accuracy
Table: COMP_ LS
Fields: ID, PartNum, Mfr, Model, Stability

There will be an estimated 25 tables with this similar format once me and my
fellow co-workers finish this up. I felt this schema was best based on the
uniqueness of reports we're going to run for each component type. I have not
included all the unique fields here for clarity.

I'm trying to relate these component tables together with a component list
table called "InstrumentTag" that has unique tags (reference designators) for
each component. "InstrumentTag" also has fields "ComponentTable" and
"COMP_ID". "ComponentTable" contains the name of the table and COMP_ID the ID
in that table. e.g.

TableName: InstrumentTags
Fields: TagNo, ComponentTable, COMP_ID, Mounting, DrawingNo

I want a query that will give me the following output matching
[InstrumentTag].[ComponentTable] with the table name out of my database:

Query: Master Component List
Fields: TagNo, PartNum, Mfr, Model, Mounting, DrawingNo

but without having to create a query for each component type whereby I must
build for each component table. Is there a SQL statement could be constructed
in Access to achieve this?

If not, what should I try?

Thanks in advance,
Angeldb

 

[John Vinson]

I have a component database consisting of different component tables. e.g.

Table: COMP_UL
Fields: ID, PartNum, Mfr, Model, Accuracy
Table: COMP_ LS
Fields: ID, PartNum, Mfr, Model, Stability

There will be an estimated 25 tables with this similar format once me and my
fellow co-workers finish this up. I felt this schema was best based on the
uniqueness of reports we're going to run for each component type. I have not
included all the unique fields here for clarity.

I'd suggest a different approach. You're storing data in tablenames.
That's NEVER a good idea.

If the different components have different attributes, a better
approach would be to use Subclassing, one of the few cases where one
to one relationships are appropriate:

Table: Components
ID (Primary Key), ComponentType, PartNum, Mfr, Model

This table will have a record for ALL types of components; it will be
related one-to-one on ID to component-specific tables:

Table: ULComponents
ID (Primary Key) and foreign key to Components
Accuracy
<other UL specific fields>

Each type of component would have a similar table for its specific
fields.

Alternatively, you can put the component-specific fields right in the
main table, and just leave them NULL if they're not appropriate for
this component. Both approaches - the null fields and the subclassing
one-to-one relationships - are imperfect and have their own advantages
and disadvantages.

I'm trying to relate these component tables together with a component list
table called "InstrumentTag" that has unique tags (reference designators) for
each component. "InstrumentTag" also has fields "ComponentTable" and
"COMP_ID". "ComponentTable" contains the name of the table and COMP_ID the ID
in that table. e.g.

TableName: InstrumentTags
Fields: TagNo, ComponentTable, COMP_ID, Mounting, DrawingNo

I want a query that will give me the following output matching
[InstrumentTag].[ComponentTable] with the table name out of my database:

Query: Master Component List
Fields: TagNo, PartNum, Mfr, Model, Mounting, DrawingNo

What you'll have to do with the current structure is to create and
save a UNION query stringing together all 25 tables, and join *this*
to your InstrumentTags table. The result will not be updateable (no
UNION query ever is). It's a lot simpler (and faster and more
efficient) with the subclassing (though of course you can't as easily
bring in the component-specific fields).

John W. Vinson[MVP]

 

[Angeldb]

Thanks for the advice.

So your suggestion means "Component Type" will be repeated over common
component records.

Originally, I was going to create one big table with NULL values for unlike
fields. However, this table would have over 50 values by the time I'm done.
Some are similar to each other such as Accuracy and Resolution, but mean
different things within various components. So I like your idea of
subclassing, but now I've got some work ahead of me to go back and alter the
schema, alter the queries, and alter the reports.

It would help if you could tell me how to do two things:

1. Create different reports using a single parameter query.
[Components].[ComponentType]
2. Explain how to set-up table relationships so that the subclass tables get
ID updated automatically with the correct ID from Components.

We want to eventually share this database with our department to enter
different components, but not everyone is comfortable with Access. I want to
avoid having users remember that they have to match the ID in the Component
table with the ID in the corresponding subclass table.

Thanks for the help.

I have a component database consisting of different component tables. e.g.

Table: COMP_UL
Fields: ID, PartNum, Mfr, Model, Accuracy
Table: COMP_ LS
Fields: ID, PartNum, Mfr, Model, Stability

There will be an estimated 25 tables with this similar format once me and my
fellow co-workers finish this up. I felt this schema was best based on the
uniqueness of reports we're going to run for each component type. I have not
included all the unique fields here for clarity.

I'd suggest a different approach. You're storing data in tablenames.
That's NEVER a good idea.

If the different components have different attributes, a better
approach would be to use Subclassing, one of the few cases where one
to one relationships are appropriate:

Table: Components
ID (Primary Key), ComponentType, PartNum, Mfr, Model

This table will have a record for ALL types of components; it will be
related one-to-one on ID to component-specific tables:

Table: ULComponents
ID (Primary Key) and foreign key to Components
Accuracy
<other UL specific fields>

Each type of component would have a similar table for its specific
fields.

Alternatively, you can put the component-specific fields right in the
main table, and just leave them NULL if they're not appropriate for
this component. Both approaches - the null fields and the subclassing
one-to-one relationships - are imperfect and have their own advantages
and disadvantages.

I'm trying to relate these component tables together with a component list
table called "InstrumentTag" that has unique tags (reference designators) for
each component. "InstrumentTag" also has fields "ComponentTable" and
"COMP_ID". "ComponentTable" contains the name of the table and COMP_ID the ID
in that table. e.g.

TableName: InstrumentTags
Fields: TagNo, ComponentTable, COMP_ID, Mounting, DrawingNo

I want a query that will give me the following output matching
[InstrumentTag].[ComponentTable] with the table name out of my database:

Query: Master Component List
Fields: TagNo, PartNum, Mfr, Model, Mounting, DrawingNo

What you'll have to do with the current structure is to create and
save a UNION query stringing together all 25 tables, and join *this*
to your InstrumentTags table. The result will not be updateable (no
UNION query ever is). It's a lot simpler (and faster and more
efficient) with the subclassing (though of course you can't as easily
bring in the component-specific fields).

John W. Vinson[MVP]

 

[tina]

2. Explain how to set-up table relationships so that the subclass tables
get

ID updated automatically with the correct ID from Components.

the component-specific tables are "child" tables to the "parent" table
Components. use forms for the data entry (users should never enter data
directly into tables); a main form for Components, and a subform for each
component-specific table. when a mainform and subform are properly linked by
the subform control's LinkChildFields and LinkMasterFields properties, the
primary key value from the parent table is automatically saved in the
foreign key field of the child table, so the records in the two tables are
correctly related without the user needing to do or remember anything.

note: since each component *type* will be stored in table Components, you
can actually use a single subform control in the main form, and just change
its' SourceObject property to the name of the subform that matches the
current record's component type. this is a much more efficient solution, for
a number of reasons, than trying to put *all* the subforms on the main form
at the same time.

hth

Thanks for the advice.

So your suggestion means "Component Type" will be repeated over common
component records.

Originally, I was going to create one big table with NULL values for unlike
fields. However, this table would have over 50 values by the time I'm done.
Some are similar to each other such as Accuracy and Resolution, but mean
different things within various components. So I like your idea of
subclassing, but now I've got some work ahead of me to go back and alter the
schema, alter the queries, and alter the reports.

It would help if you could tell me how to do two things:

1. Create different reports using a single parameter query.
[Components].[ComponentType]
2. Explain how to set-up table relationships so that the subclass tables get
ID updated automatically with the correct ID from Components.

We want to eventually share this database with our department to enter
different components, but not everyone is comfortable with Access. I want to
avoid having users remember that they have to match the ID in the Component
table with the ID in the corresponding subclass table.

Thanks for the help.

I have a component database consisting of different component tables. e.g.

Table: COMP_UL
Fields: ID, PartNum, Mfr, Model, Accuracy
Table: COMP_ LS
Fields: ID, PartNum, Mfr, Model, Stability

There will be an estimated 25 tables with this similar format once me and my
fellow co-workers finish this up. I felt this schema was best based on the
uniqueness of reports we're going to run for each component type. I have not
included all the unique fields here for clarity.

I'd suggest a different approach. You're storing data in tablenames.
That's NEVER a good idea.

If the different components have different attributes, a better
approach would be to use Subclassing, one of the few cases where one
to one relationships are appropriate:

Table: Components
ID (Primary Key), ComponentType, PartNum, Mfr, Model

This table will have a record for ALL types of components; it will be
related one-to-one on ID to component-specific tables:

Table: ULComponents
ID (Primary Key) and foreign key to Components
Accuracy
<other UL specific fields>

Each type of component would have a similar table for its specific
fields.

Alternatively, you can put the component-specific fields right in the
main table, and just leave them NULL if they're not appropriate for
this component. Both approaches - the null fields and the subclassing
one-to-one relationships - are imperfect and have their own advantages
and disadvantages.

I'm trying to relate these component tables together with a component list
table called "InstrumentTag" that has unique tags (reference designators) for
each component. "InstrumentTag" also has fields "ComponentTable" and
"COMP_ID". "ComponentTable" contains the name of the table and COMP_ID the ID
in that table. e.g.

TableName: InstrumentTags
Fields: TagNo, ComponentTable, COMP_ID, Mounting, DrawingNo

I want a query that will give me the following output matching
[InstrumentTag].[ComponentTable] with the table name out of my database:

Query: Master Component List
Fields: TagNo, PartNum, Mfr, Model, Mounting, DrawingNo

What you'll have to do with the current structure is to create and
save a UNION query stringing together all 25 tables, and join *this*
to your InstrumentTags table. The result will not be updateable (no
UNION query ever is). It's a lot simpler (and faster and more
efficient) with the subclassing (though of course you can't as easily
bring in the component-specific fields).

John W. Vinson[MVP]

 

[John Vinson]

note: since each component *type* will be stored in table Components, you
can actually use a single subform control in the main form, and just change
its' SourceObject property to the name of the subform that matches the
current record's component type. this is a much more efficient solution, for
a number of reasons, than trying to put *all* the subforms on the main form
at the same time.

<boing>

VERY clever, tina. Saved me from posting a less elegant answer!

John W. Vinson[MVP]

 

[tina]

well, it's a standard form solution to this type of table design, of course.
i just saw somebody - i think it was Albert - give essentially the same
solution to a multi-subform question the other day. he even gave a reference
to an example on somebody's website. so i'm sure i didn't turn on a
lightbulb for any of the "regulars" here, including you.

 

[Angeldb]

Thanks tina/John for your help.

I've created the one-to-one relationships between the Component table and
the component-specific tables as you've suggested. I've also tried the
form/subform you suggested, but after I created it, another question comes up:

Do I have to manually change the subform's SourceObject property everytime I
want to edit component-specific fields? Or is there a way to dynamically
update the subform based on the "type" from the Component main form?

 

[John Vinson]

Do I have to manually change the subform's SourceObject property everytime I
want to edit component-specific fields? Or is there a way to dynamically
update the subform based on the "type" from the Component main form?

It can and should be automated, in the Type control's AfterUpdate
event. I'd suggest using a Combo Box to choose the type. You could
base the combo on a Table with two fields, the human-meaningful type
and a textfield containing the name of the Form appropriate for that
type. You'ld use code like

Private Sub cboType_AfterUpdate()
Me!subMySubform.SourceObject = Me.cboType.Column(1)
End Sub

to reset the source object.

John W. Vinson[MVP]