Tabbed Form Question

[TeeSee]

This topic is in the dream stage at the moment so don't have any
specifics to display .....

I would like to track construction projects for our company. I am
thinking towards a main form with the project related data on it, and
several tabbed forms to cover each trade e.g. Gen Contractor,
Masonary, HVAC etc.

Is it possible and or feasible to have a single table with all these
companies Names and addresses in it and access them for display within
several tabs for the same project?
All the fields would be the same no matter what the trade and it is
conceivable that one company could belong to more than one trade.

Am I on the right track or is there a better way/method.

Thanks as always

 

[John W. Vinson]

This topic is in the dream stage at the moment so don't have any
specifics to display .....

I would like to track construction projects for our company. I am
thinking towards a main form with the project related data on it, and
several tabbed forms to cover each trade e.g. Gen Contractor,
Masonary, HVAC etc.

Is it possible and or feasible to have a single table with all these
companies Names and addresses in it and access them for display within
several tabs for the same project?
All the fields would be the same no matter what the trade and it is
conceivable that one company could belong to more than one trade.

Am I on the right track or is there a better way/method.

Thanks as always

STOP thinking about Forms until you have your Tables designed correctly. You
don't want to start framing in the walls before you have the foundation set!

You need to identify the "Entities" - real-life persons, things or events,
such as Contractors, Companies, Trades, Projects, etc. - relevant to your
application; each type of Entity should get its own table. If you have a many
to many relationship (e.g. each Trade may be carried out by several
Contractors, and each Contractor may pursue several Trades), you have a "many
to many" relationship and need another table to record who does what trades.

See some of these resources:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[TeeSee]

STOP thinking about Forms until you have your Tables designed correctly. You
don't want to start framing in the walls before you have the foundation set!

You need to identify the "Entities" - real-life persons, things or events,
such as Contractors, Companies, Trades, Projects, etc. - relevant to your
application; each type of Entity should get its own table. If you have a many
to many relationship (e.g. each Trade may be carried out by several
Contractors, and each Contractor may pursue several Trades), you have a "many
to many" relationship and need another table to record who does what trades.

See some of these resources:

Jeff Conrad's resources page:http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:http://allenbrowne.com/links.html#Tutorials

--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

John .... That was exactly the reason I was asking. Does this apply
even if the same information (contractor names and addresse) are in
several tables?

 

[John W. Vinson]

John .... That was exactly the reason I was asking. Does this apply
even if the same information (contractor names and addresse) are in
several tables?

Well, in a properly designed database, all of the instances of an "entity
class" (i.e. all contractors in the set of contractors) would all be in the
same table so the problem wouldn't arise. Good relational database design is
an art and a science, and doesn't necessarily come to perfection quickly! If
you could describe your current thinking about the actual entities, their
attributes (discrete chunks of information you need to know about them), and
your current table structures, we'd be happy to make suggestions.

 

[TeeSee]

Well, in a properly designed database, all of the instances of an "entity
class" (i.e. all contractors in the set of contractors) would all be in the
same table so the problem wouldn't arise. Good relational database designis
an art and a science, and doesn't necessarily come to perfection quickly!If
you could describe your current thinking about the actual entities, their
attributes (discrete chunks of information you need to know about them), and
your current table structures, we'd be happy to make suggestions.

John ..... Believe me I have read and studied the normalized tables
issue and I think I "get it" but I still believe that no matter who
looks at the data available the tables would be set up somewhat
differently and still somehow function. I don't know if you would
agree or not.

To keep this immediate question as simple as I can let me ask you in
this way .....

If the only difference in the data for these various contractors is
the TRADE that they represent, would you place them ALL in one table
or would you have separate tables for each trade in spite of the fact
there would be duplicate data in some or all of the tables? There
would be a field in that single table to hold data differentiating the
trades.

Thanks John and by the way thanks for those links.... I always learn
something new with every response from this group.

Best regards

 

[John W. Vinson]

John ..... Believe me I have read and studied the normalized tables
issue and I think I "get it" but I still believe that no matter who
looks at the data available the tables would be set up somewhat
differently and still somehow function. I don't know if you would
agree or not.

I'd be a damfool if I didn't agree. "There are nine and ninety ways/of
constructing tribal lays/and ever single one of them is RIGHT!" said Kipling.
Not all of the ways of constructing tables are right, but there are many ways
that do work.

To keep this immediate question as simple as I can let me ask you in
this way .....

If the only difference in the data for these various contractors is
the TRADE that they represent, would you place them ALL in one table
or would you have separate tables for each trade in spite of the fact
there would be duplicate data in some or all of the tables? There
would be a field in that single table to hold data differentiating the
trades.

I would use THREE tables:

Contractors
ContractorID <primary key>
<name, contact information, identifying information for the contractor>
<NOTHING about the trades that they follow!!>

Trades
TradeID <primary key>
Description

ContractorTrades
ContractorID <field 1 of two-field primary key>
TradeID <field 2 of two-field primary key>
<maybe other fields pertaining to THIS contractor in THIS trade, e.g. yes/no
field Licensed, date/time field DateLicensed, Comments, etc.>


If Bill's Backhoe Service does Excavating, Septic Systems, Trenching and
Pipelaying, there'd be four records for that contractor in the
ContractorTrades table; the contractor's name, phone, etc. etc. would only be
entered once in the Contractors table. You could base a Subform or a Combo Box
on a query joining ContractorTrades to Trades in order to see only those
contractors who offer a particular trade.

Thanks John and by the way thanks for those links.... I always learn
something new with every response from this group.

Glad to be of help.

 

[TeeSee]

I'd be a damfool if I didn't agree. "There are nine and ninety ways/of
constructing tribal lays/and ever single one of them is RIGHT!" said Kipling.
Not all of the ways of constructing tables are right, but there are many ways
that do work.



I would use THREE tables:

Contractors
  ContractorID <primary key>
  <name, contact information, identifying information for the contractor>
  <NOTHING about the trades that they follow!!>

Trades
  TradeID <primary key>
  Description

ContractorTrades
  ContractorID <field 1 of two-field primary key>
  TradeID <field 2 of two-field primary key>
  <maybe other fields pertaining to THIS contractor in THIS trade, e.g.yes/no
field Licensed, date/time field DateLicensed, Comments, etc.>

If Bill's Backhoe Service does Excavating, Septic Systems, Trenching and
Pipelaying, there'd be four records for that contractor in the
ContractorTrades table; the contractor's name, phone, etc. etc. would only be
entered once in the Contractors table. You could base a Subform or a Combo Box
on a query joining ContractorTrades to Trades in order to see only those
contractors who offer a particular trade.


Glad to be of help.

I guess I'd now have to admit that maybe i don't fully "get it" yet. I
will go back to the normalization docs that I have and with the
guidance you have offered with the three tables ..... hopefully "find
it" this time.

Crystal says I shd "visualize" which I see as "imagination" which I
tend to lack (among other things). Thanks again John for the
guidance.

Best regards

 

[John W. Vinson]

I guess I'd now have to admit that maybe i don't fully "get it" yet. I
will go back to the normalization docs that I have and with the
guidance you have offered with the three tables ..... hopefully "find
it" this time.

If you want to see an example, take a look at the Orders form in Northwind. A
given Order (think contractor) may contain many Products (think trades); a
given Product may be included in many Orders.