Re: Relationships

From: TDR (TDR_at_discussions.microsoft.com)
Date: 01/15/05 

Date: Fri, 14 Jan 2005 23:35:02 -0800

Thanks John,

This is my first experience with building a database, I didn't spend much
time getting to know Access rather I jumped right in.

I will heed your advice and invest some time on creating forms. And I will
change my lookup fields to, umm, non-lookup fields.

I can partly envision where I am going with this project, I will want to
show and update the Access data through Outlook, plus I have been intrigued
by Infopath and the ability to populate forms with data and update a database
by completing forms.

The reality is I don't have the knowledge to do a project like this well but
I have committed to trying. Anyways, my first goal is to have all the data I
will want to show through Outlook setup in Access, have a system in place for
updating/deleting/changing the Access database then I can learn about linking
it to Outlook.

Thanks again, I appreciate the help you and the other MVP's provide, I'm
sure I'll be back for more!

TDR

"John Vinson" wrote:

> 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]
>