Re: Calculated Value will not store in Table

From: tonyaims (anonymous_at_discussions.microsoft.com)
Date: 09/14/04 

Date: Mon, 13 Sep 2004 19:11:09 -0700

Albert - Thanks a lot. Very helpful information.
I will be away on a business trip until the end of the
week so you get a break - at least from my questions.

Tony :-)

>-----Original Message-----
>"tonyaims" <anonymous@discussions.microsoft.com> wrote
in message
>news:1a4d01c499f1$28aa1620$a401280a@phx.gbl...
>> Albert,
>>
>> Thanks for the suggestions. What is a "left" join?
>>
>> Tony
>
>Ah...gee, you had to ask..huh!!!
>
>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 have
to use left joins
>for lookup values when you drop in many tables (can't
use standard joins in
>this case).
>
>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....
>
>Now, the concept of a left join is NOT very important
for you combo box
>lookups, except for the fact that it don't work unless
you use left joins!!
>
>However, for forms, and sub-forms, and related tables,
left joins are quite
>important. Keeping in mind that related tables, and your
above problem are
>different problems, there read the following as to why
you want to be aware
>of left joins:
>
>--------------------------------------
>
>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.attcanada.net/%
7ekallal.msn/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.
>
>
>.
>