I need help with Table Relationships please.

[TC]

I am looking for assistance with what seems very basic, but I cannot seem to
understand, relationships between multiple tables. I always end up making a
DB with one huge table and running all queries / reports from one table. I
doubt this is the best way! I always assign a "primary key" to one item per
table but in the relationships field I have no idea which should be left,
right, one, many, referential integrity, whatever. Somewhere in the training
classes (Access 2000,L1 and L2) the instructors glossed over these topics and
I am lost. In my forms I keep getting a message that my input fields "control
can't be edited, it is bound to unknown field "fieldname".

Please help, I am sure this is very basic for many of the experts here, and
I appreciate any assistance. TC

 

[Duane Hookom]

TC,
I am not sure your issue with your forms is related to your table
relationships. Is there any other information that you can share about your
tables, relationships, form record source,...?

 

[Jerry Whittle]

I suggest getting some more relational database training or reading "Database
Design for Mere Mortals" by Hernandez before proceding any further.

 

[Sprinks]

TC,

The key to building a useful and manageable relational database is Database
Normalization. While there are specific criteria and levels of
normalization, the most important aspect is to break down your tables
sufficiently so that each describes one "thing"--e.g., Customers, Orders,
OrderDetails. The following links can help you get going.

Relationships arise naturally out of these things, for example, a Customer
can place many orders, and an order may contain many items.

Some new users get hung up on how data is *stored* in tables, and how it is
*displayed* on a form. For example, for an Order Entry form, they want to
see the customer name, address, etc., so they may think they need these
fields in their Orders table. All you really need is the primary key from
the Customer table (called a foreign key in the Orders table). This foreign
key "unlocks" or gives "Access" to all of the other fields in the Customer
table via a query.

Re: the technical difference between the types of Joins, an INNER JOIN
returns records from two related tables where the data matches in the
corresponding related fields. Outer joins permit data to be missing in one
of the tables. For example, in a query that asks for records of all orders
placed by a customer, you might wish to display the full customer information
regardless of whether they'd placed an order yet. Left or Right refers to
which table you wish to return in this case.

Hope that helps.
Sprinks

http://www.mvps.org/access/
(A definite must!)

Microsoft Knowledge Base for searching:
http://search.support.microsoft.com/search/?adv=1


Getting Started:

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

Glossary of database terms:
http://www.dhdursoassociates.com/database-glossary-3.html

Where to find information about designing a database in Microsoft Access:
http://support.microsoft.com/?id=289533

ACC: Database Normalization Basics
http://support.microsoft.com/?id=100139
http://support.microsoft.com/?id=209534
http://support.microsoft.com/?id=283878

Database Normalization Tips by Luke Chung
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

Support WebCast: Database Normalization Basics
http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc060600/wcblurb060600.asp

Database Normalization:
http://burks.bton.ac.uk/burks/foldoc/35/28.htm

5 Rules of Database Normalization:
http://www.datamodel.org/NormalizationRules.html

"Understanding Relational Database Design" Document Available in Download
Center:
http://support.microsoft.com/?id=283698
http://support.microsoft.com/?id=164172

ACC2000: "Understanding Relational Database Design"
http://support.microsoft.com/?id=234208

Fundamentals of Relational Database Design:
http://support.microsoft.com/?id=129519

Database Deisgn Principles:
http://msdn.microsoft.com/library/en-us/dndbdes/html/ch04DDP.asp