Need help relating tables

[Lucieann]

I have five tables I need to relate, but I cannot figure out the best way to do so.

Table 1 - Customer Info (Acct #, Name, Address, Etc.)
Table 2 - Acct Notes (Acct #, Line #, Line Description)
Table 3 - Collateral Info (Acct #, Line# of Description, Description)
Table 4 - Tracked Item (Acct #, Category, Sub#, Description, Expiration Date, etc.)
Table 5 - Item Notes (Acct #, Line# of Description, Description)

I guess I don't understand enough about relationships. If you relate two tables together, does the next related table feed off the result of the join between the first two tables????

I'm very confused at this point. It seems like no matter how I relate them, I don't get the results I need.

 

[willir]

Lucieann

You need to review a couple of things...

- Normalization. Search this forum for tons of stuff
- Types of relationships
- Primary and Foreign keys

For now, I will focus on the latter two.

There are three types of basic relationships in a relational database...
- One-to-one, not used often
- One-to-many. Used often, example would be the detail lines on an invoice. The parent record, the "one" side is the header record - customer, invoice date, ship to, terms etc. The child is the "many" side - each invoice item.
- Many-to-many. Kids at high school. A student will have several teachers. Teachers will have many students.

Each record in the database should be unique. A primary key is usually used to identify the unique record. Your example of the account number for a customer is a great example. For certain situations, it makes sense to combine more than one field to create the primary key, BUT this approach can cause problems in various situations. Specifically, best time to use two fields for the primary key is on table that combines various foreign keys to create a type of "profile".

Foreign key is the primary key located on the child table. In the invoice example, the primary key for the invoice would be the invoice number. The invoice number would then be located on each invoice detail item.

But do you see the problem with the given example? The invoice table uses the account number as the primary key. What is the primary key on the invoice detail records, the child records -- not the invoice number since each record should be unique and the invoice number is used for each line item. You can use the invoice number + line number for the primary key. Or you can use an autonumber.

A typical, and simple Inovice table design would include...

tblInvoice
InvNo - primary key
InvDate - date
InvTerms
etc

tblInvoiceDetail
InvDetailID - primary key
InvNo - foreign key pointing to tblInvoice
ProductCode
Quantity
etc

I hvae to go, but this should give you the basics.

Richard

 

[lespaul]

Ok, open the relationship window, add all the tables, then start adding relationships.

Click on the CustomerInfo Acct# and drag to Acct# in the AcctNotes table.

Click on the AcctNotes Acct# and drag to CollateralInfo Acct#, now in the relationship window in the "grid" find the Line# field from the AcctNotes and the Line# from the CollateralInfo. Now this table is joined on both the Acct# & Line#.

Now you should be able to set up your other relationships (either with one joining field or two depending) and get the results you need.

HTH




Leslie

 

[Lucieann]

How would I use invoice # + line # for the primary key?

 

[willir]

Lucieann

Open the table in design mode.

Make sure no other primary key exists.

Select InvoiceNo by clicking in the box left the name. This should high-lite the entire line.

Then hold down CTRL, and right click in the box to the left of the LineNo field.

Select "primary key" from the popup menu.


Alternatively, with the table design open, from the "View" menu, select "Indexes". In the first line, enter "PrimaryKey" under the Index Name column. In the second column, under field name, select InvoiceNo. For sort order, select ascending. For index properties, set Primary key to "yes" -- this will change Unique to "yes" and Ignore nulls to "no". Then in the second row, select LineNo for the second field name. Since the second row, LineNo has no index name, it will use name from the first row.

You may find it easier to use autonumber.


A comment regarding the names of your fields. You will save your self problems in the future by not using "#", and not having spaces in the field name. For a better explanation, follow this link...

http://www.tek-tips.com/faqs.cfm?spid=700&sfid=2190

Richard