Re: Relationships

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: John Vinson (jvinson_at_STOP_SPAM.WysardOfInfo.com)
Date: 01/15/05 

Date: Fri, 14 Jan 2005 23:15:24 -0700

On Fri, 14 Jan 2005 19:55:02 -0800, TDR
<TDR@discussions.microsoft.com> wrote:

>I have an 'accounts' table with the 'accountnumber' as the primary key. I
>have several other tables such as 'transfers', 'systematic plans', 'loans'
>etc. with the 'accountnumber' field set as a lookup column in each of these
>tables. When I open the 'accounts' table in data*** view and I click on
>the plus sign, an 'insert subdata***' popup appears that requires me to
>pick a table.
>
>When I click on the plus sign (I would also like to know what the name of
>the plus sign is) I would like it to list all the other tables with a plus
>sign, so when I click on, for instance, the plus sign beside 'transfers',
>another subdata*** appears with the transfer details of the accountnumber
>of which I originally clicked the plus sign. One thing is that the subtables
>such as 'transfers' will not necessarily have data for every account, as not
>every account is in the process of receiving or delivering assets.
>
>I've been able to relate the 'accounts' table to a 'client' table and the
>'client' table to a 'household' table etc. but I am stumped to how I should
>link these other tables I have mentioned above.
>
>Let me know if you require further details.

You're well within the capabilities of Access - but you have gone well
beyond the capabilities of the VERY limited "subdata***" feature.
Most serious developers turn off subdatasheets immediately in all
databases; they're a terrible drag on performance as well as being
unnecessary.

In fact, you should not use table datasheets at all, for anything
other than debugging; and most developers would recommend that you not
use the Lookup Wizard ever under any circumstances: see
http://www.mvps.org/access/lookupfields.htm for a critique.

Instead, go on to the powerful tool that Access provides for
interacting with the data in tables: the Form. You can have a Form
based on your Accounts table, with (say) three subforms on the Form,
one showing that account's Transfers, another showing its Loans, and a
third showing its Plans.

The Accounts table should have a ClientID field linking it to the
Clients table (assuming that each client can have multiple accounts
but each account has one primary client); it would not be necessary to
link the subsidiary tables to the clientID, unless an Account can have
multiple clients and individual clients within an account might have
their own set of loans or transfers.

                  John W. Vinson[MVP]

Quantcast