I thought you were supposed to define the master link field to the child
link field in the table definitions.
A horse is a horse is a horse.....
A table is a table is a talbe..
the ONLY thing you define in the table is fields, primary key, and some
indexs...nothing more, nothig less..
In datasheet view I thought you were supposed to put the links but I don't
know which one to put in case ID or partytypeID.\
Ouch...don't know where you got that idea. The way relatonal databases have
worked for the last 20 some years is that you deinf ehte table, PK, and
perahps some indexes. If you need addotnal ralted talbes, hten yu create
those tables. At this point, to "relate" the tables, you then go
tools->Relatonships
You add in your talbes, and then simply draw the join lines.......
You do NOT define relationships in table design mode, or worse, as you
stated datasheet mode....
Once you defined the relationships, then you simply build forms. Those forms
are USUALLY simply based directly on the table. If you have a related
table, then you can create a sub-form to display this related data. This
"sub-form" is where you set the master link, and master child relationships.
(they are properties in the property sheet for the sub-form control).
Remember, you do want to pay attention to the correct kind of join to use.
In my experience, about 80% (perhaps more) joins should be left joins in an
application.
A left join means that a query will return the "parent" records when the
child table HAS NO correspond record.
So, if we have Customers, and Invoices tables, a left join would give us:
CustomerName InvoiceNumber
AppleBee
Donought Shop 1234
Doughnut Shop 1344
Note how AppleBee does NOT yet have a invoice number in the invoices
table..but the query still returns the record. You also have to use left
joins
for lookup values when you drop in many tables (can't use standard joins in
this case).
Note, when I say lookup values...I don't mean the built-in lookup values.
You WANT TO AVOID those at ALL COSTS.
Please read the folwling..and pay CAREFUL attention to #2 in the list, and
note how we ALL STRONGLY warn against using lookup fields in the table
design mode....
So, with a left join, the corresponding child record DOES NOT have to exist.
Just think of "left" side can exist...but the right side does NOT have to !
A middle join, or so called inner join is the standard join, and BOTH tables
have to have a value for the join. The above would produce:
CustomerName InvoiceNumber
Dounought Shop 1234
Doughutn Ship 1344
So, in the above inner join, our customer name of Applebee does not show,
since that customer does NOT yet have a invoice record in the invoice table.
To make a left join, you drop in the tables (in the query builder, or the
relationship designer), and draw the join line to the appropriate filed
between each table. You then double click on the join line. You then click
on the join type button
You get three options:
Only include rows where the joined fields from both tables are equal
(this standard default inner join)
Include ALL records from "Customers" and only those records from
"Invoices" where the joined fields are equal
(this is our left join. So, our main table Customers will be returned in
this query, REGARDLESS if the child records (invoices in this example)
exist, or not!. This is left join
Include ALL records from "Invoices" and only those records from
"Customers" where the joined fields are equal
This sis obviously a right join....
For forms, and sub-forms, and related tables, left joins are quite
important.
If you look at the following screen shot, you can see that most relations
ships are this left join, and RI is enforced.
http://www.members.shaw.ca/AlbertKallal/Articles/PickSql/Appendex2.html
tblBgroup (booking group) for example may, or may not have payments made
(tblPayments). Thus, you can add a booking group, and NOT have to add child
records. However, full RI is enforced, and you can see the side ways 8
"omega" sign AND THE ARROW HEAD. The simple lookup fields are simply just a
arrow drawn, and no "1", or omega sign exists (tblPayments to tblHowpaid for
example is a simple lookup).
The tables that MUST have a child records can also clearly be seen. If you
go from the tblBgroup to the its parent table, you will see table
tblBooking. You can easily see that there is a 1 to many here also, but NO
ARROW head exists. Thus, when I create a booking, my designs will ALWAYS
ASSUME that a child records in tblBgroup (booking group) will exist (ie: I
must code, and assume that when I add a tblBookin records, my code also
assumes that a tblBGroup will also have to be added).
So, the ER diagram can convey a lot about your designs. Down the road, I can
now look at that diagram, and when writing code, I will know if the design
can, and does assume if child records are required. If you look at that
table, it is VERY RARE that I require the child record. That application has
about 60 tables, and I think only 1 or 2 in the whole thing is NOT a left
join. Hence, you most certainly should set the relation in the window for
future reference, and also it will help you when you create a query, or a
report.
