If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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] |
#3
|
|||
|
|||
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] |
#4
|
|||
|
|||
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] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Deleting Relationships... | Kojones | General Discussion | 3 | December 17th, 2004 11:46 PM |
Relationships getting redefined? | raylitalo | General Discussion | 3 | December 15th, 2004 08:31 PM |
Q: Can't Delete relationships! | MarkD | General Discussion | 2 | September 24th, 2004 11:13 PM |
Disappearing relationships | Dan | Database Design | 2 | August 6th, 2004 06:43 AM |
Not seeing all relationships in layout window | jettabug | General Discussion | 3 | June 18th, 2004 05:42 PM |