Relationship question

[Guest]

I have a database that records information on contacts and company
information. I wanted to add to this database a table to record all our
insurances. The table would include a combo box's -SQL statements to pull
the company name from the company table and the agent and carrier names from
the contact table.

My question is, do I have to establish a relationship even though there is a
relationship to two different tables and there really doesn't need to be a
joined field? Then again if the names change, will they update in this new
table if they are not joined?

If I really should have a relationship...please advise how to set up a
relationship with two tables.

 

[Arvin Meyer [MVP]]

Properly designed tables do not include combo boxes. See:

http://www.mvps.org/access/lookupfields.htm

Yes you need a relationship between tables. Add a CompanyID field to the
contact table so that you have a connection.

 

[John W. Vinson]

I have a database that records information on contacts and company
information. I wanted to add to this database a table to record all our
insurances. The table would include a combo box's -SQL statements to pull
the company name from the company table and the agent and carrier names from
the contact table.

No, the table would NOT contain eitehr SQL statements nor would it contain
combo boxes, unless you let Microsoft's misdesigned, misleading, and obnoxious
Lookup Wizard mess it up for you.

See http://www.mvps.org/access/lookupfields.htm for a critique.

My question is, do I have to establish a relationship even though there is a
relationship to two different tables and there really doesn't need to be a
joined field? Then again if the names change, will they update in this new
table if they are not joined?

If I really should have a relationship...please advise how to set up a
relationship with two tables.

The lookup wizard will define the relationship and create indexes on it (even
if there already ARE relationships and indexes).

Don't use tables for any interaction with the data. Use your tables for data
storage and only for data storage (well, open a table datasheet if you need to
do so for debugging and design); combo boxes go on forms, not in tables. Data
storage and data presentation are two different tasks with two different sets
of requirements!

John W. Vinson [MVP]

 

[Guest]

I was talking about the table display control option under lookup "combo box"
under the table, not the actual combo box set up under forms...sorry, I
should have explained that.

 

[Arvin Meyer [MVP]]

That's exactly what John and I were talking about. NEVER use that. Just
because Microsoft made it available, does not mean that it is wise to use
it. Users should NEVER be allowed to work directly in tables, so how data is
displayed in the table is moot. A table's purpose is to store data and, if
possible, maintain its integrity. Allowing users to touch a table
jeopardizes data integrity and is never allowed in any professionally
designed application. Again, read about the evils of using combos and
lookups in tables.

http://www.mvps.org/access/lookupfields.htm
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com