Relationships

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 datasheet view and I click on
the plus sign, an 'insert subdatasheet' 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 subdatasheet 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.

On Fri, 14 Jan 2005 19:55:02 -0800, TDR
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 datasheet view and I click on
the plus sign, an 'insert subdatasheet' 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 subdatasheet 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 "subdatasheet" 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]

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
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 datasheet view and I click on
the plus sign, an 'insert subdatasheet' 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 subdatasheet 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 "subdatasheet" 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]

On Fri, 14 Jan 2005 23:35:02 -0800, TDR
wrote:

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.

g The "Deep End School of Swimming" approach can be valuable - you
learn quick or not at all! But yes, Access has a considerably steeper
learning curve than most other Office applications.

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

You can use the Lookup tab on each such field; change the display type
from Combo Box to Textbox to show the actual content of the field. For
better performance, you should also right mouseclick each table (not
clicking on any particular field) and change the Subdatasheet property
from [Auto] to [None]. This won't interfere with form or subform
design.

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.

It's possible to interface Access with Outlook, but it requires
delving deeply into their quite different object models using VBA
code. AFAIK there aren't any "point and click" wizards to set up such
interfaces, but it *can* be done.

Nonetheless, you should build a good foundation in terms of properly
normalized and related tables in Access... and then do the link. There
will surely be a need for Access Forms as well as the linkage.

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.

There you go!

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


We'll be here. g

John W. Vinson[MVP]