Relationships between tables

[Susie]

I have made several tables. I need to link them together. Then with I go into
make a Query to gather the information I can link the following

Table 1
Unit # Primary Key
Type
State

Table 2
Invoice Primary Key
Date
Unit #
Type
Contract
Zone


Table3

Contract
Zones
Type
Bill amount
Driver Pay
Driver Bonus

(1)I try to link Table one Unit # to Table 2 Unit, (2)Table two contract
with table 3 contract, table 2 zone to table 3 zones. I get this part but
when I connect the table (3)1 type to table2 type and table 3 type. In the
query when only the first two are link i can see all my records. but if I
link number 3 it will not show all the records it lose some. I do not know
how to solve this.

 

[pietlinden]

I have made several tables. I need to link them together. Then with I go into
make a Query to gather the information I can link the following

Table 1
Unit # Primary Key
Type
State

Table 2
Invoice Primary Key
Date
Unit #
Type
Contract
Zone

Table3

Contract
Zones
Type
Bill amount
Driver Pay
Driver Bonus

(1)I try to link Table one Unit # to Table 2 Unit, (2)Table two contract
with table 3 contract, table 2 zone to table 3 zones. I get this part but
when I connect the table (3)1 type to table2 type and table 3 type. In the
query when only the first two are link i can see all my records. but if I 
link number 3 it will not show all the records it lose some. I do not know
how to solve this.

If the field types are not the same on the fields you're joining, you
will have problems. Autonumber fields are Long Integer, so if your
field types are incompatible, then you cannot join them.

Also, the nature of joins is to eliminate records where the field
values on both sides of the join are not the same. Think of that join
as just another WHERE clause. So, if TableA.PrimaryKey <>
TableB.ForeignKey then the records on both sides will be eliminated
from the result set.

 

[Murray]

Have you tried right clicking on the link in the relationship manager and
change its type?

 

[Dale Fye]

Susie,

What I believe Murry is getting at is that the normal join that Access
creates when you join two tables is an INNER JOIN. With this type of join,
you will only see the records where there are exact matches in both tables.
When you right click on the Join line between the two tables, and select
Join Properties you will get the Join Properties dialog box.

Depending on which table you started you join in, you will see options for
1) Only include rows where the joined fields from both tables are equal
(INNER JOIN)
2) Include All records from table1 and only those from table2 where the
joined fields are equal (LEFT JOIN)
3) Include All records from table2 and only those from table1 where the
joined fields are equal (RIGHT JOIN)

These last two options will return NULL values for the records from the
second table listed, if that table does not have a matching value for all of
the values in the first table.

My guess is that you have records in Table2 that don't have matches for all
of the fields you are trying to link to in table3. I would start out by
linking on just the Contract field, using the INNER JOIN and see whether you
get the number of records you expected. If not, change that link to a LEFT
JOIN (you will probably have to change the INNER JOIN between table1 and
table2 to a LEFT JOIN as well). At this point, you can add the Contract
field from table3 to the query fields, and give it a IS NULL criteria. If
that returns records, then it means you have contracts in Table2 that are
not in Table3. If you get zero results, then it means your Contract fields
match, and you can move onto the next field. Repeat this until you find out
where the problem is in your data.

HTH
Dale

 

[Susie]

on a form i am trying to do a look up from the vehicle list that contains the
following fields. Equipment #, Type, base state. I would like to look up the
equipment # and store that number in invoice table equipment #. I go it to do
that now I am trying to tell it put the type in a text box and also to store
it in the type box in the invoice table. it will show me the type in the text
box but it will not store it in the invoice table.