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 |
#11
|
|||
|
|||
At the risk of sounding like a rube, hpw is that done?
Kelvin "Duane Hookom" wrote: You should include the "linked to the tables containing the values" in your queries. -- Duane Hookom MS Access MVP -- "KLP" wrote in message ... Jack, I purchased Relational Databse Design for Mere Mortals, read it, and am now taking a 12 week Access course at community college. I changed the entire database design (wish I would have read the book first!). I am now running into a problem using queries. I have a lookup field in the primary table that is linked to the tables containing the values to be selected. When I run a query and include those fields, the data displays correctly; however, when I put them in a calculation, I get incorrect numbers; in fact, I cannot figure out where the numbers come from. The objective is to be able to change the lookup tables and run the query to see the results. I read the article at http://www.mvps.org/access/lookupfields.htm. Is there a work around for this problem? Thank you for your assistance. "Jack MacDonald" wrote: It sounds like you have a classic case/requirement for a one-to-many relationship: one loan has one or more (i.e. "many") collateral items. This type of relationship requires you to set up two or more related tables. You approach the design from a completely different perspective in a relational database such as Access than you do with a spreadsheet. Bringing spreadsheet mentality to database design is a classic mistake that beginners make (no offense intended). I highly recommend that you purchase a beginners book for Access in your local computer store and read about proper database design. It would not surprise me if your 180 fields could (should !!??) be broken into several separate tables. Or post back with a broader description of what you are storing in your 180 fields. Lots of people here are willing to help. Good luck. On Fri, 10 Dec 2004 07:57:02 -0800, KLP wrote: Yes, I am using forms. Thank you for the tips. If I understand you and Allen right, instead of having 5 fields for each collateral item, e.g., coll desc 1, coll desc 2 ... coll desc 5, (total of 30 fields for all collateral items) because I figure no loan would have more than 5 pieces of collateral, I use a subform as you described, link by loan #, use a collateral auto # id, then list the 6 collateral fields. If my understanding is correct, it would reduce a number of those 180+ fields, and relieve the concern if a loan has more than 5 pieces of collateral. Thank you, Kelvin "Jack MacDonald" wrote: Hopefully, you are entering your data into the main table using a form. Next step is to design another form for the collateral table, but exclude the primary key. Drag the second form onto the first, thus creating a subform. Populate the "link" fields of the subform using the primary key from the main table and the collateral table. Now, whenever you create a new record in the main table (using the form) and subsequently enter collateral information using the subform, Access will automatically populate the primary key of the collateral table with the correct value. Even though you say that none of your 180 fields are redundant, that seems like a *lot* of fields, and is rather suspicious. On Sun, 5 Dec 2004 16:45:03 -0800, KLP wrote: I read ACC2000: Defining Relationships Between Tables in MS Access Database. This is in response to getting the error message about too many fields. The current table has 180 fields and counting. So I would like to break them up into several individual tables. By the way, no field is redundant. I understand from the article about linking tables. I guess I am using the one-to many relationships. According to the article, you drag the primary key from one table to a similiar field in the other table, often with the same name. The primary key in the "main" table is loan#. I created a "collateral" to hold loan collateral information. So I created a primary key in the collateral table called Loan#, with the same field specs as in the main table. It seems to me I now have a redundant field as I would have to now enter the loan # twice. Am I confusing myself or what? ********************** remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security ********************** remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security |
#12
|
|||
|
|||
Select Add Table while in the query design view. Find your lookup tables and
add them to the query. Join the proper fields and add your "descriptive" field(s) to the query grid. -- Duane Hookom MS Access MVP "KLP" wrote in message ... At the risk of sounding like a rube, hpw is that done? Kelvin "Duane Hookom" wrote: You should include the "linked to the tables containing the values" in your queries. -- Duane Hookom MS Access MVP -- "KLP" wrote in message ... Jack, I purchased Relational Databse Design for Mere Mortals, read it, and am now taking a 12 week Access course at community college. I changed the entire database design (wish I would have read the book first!). I am now running into a problem using queries. I have a lookup field in the primary table that is linked to the tables containing the values to be selected. When I run a query and include those fields, the data displays correctly; however, when I put them in a calculation, I get incorrect numbers; in fact, I cannot figure out where the numbers come from. The objective is to be able to change the lookup tables and run the query to see the results. I read the article at http://www.mvps.org/access/lookupfields.htm. Is there a work around for this problem? Thank you for your assistance. "Jack MacDonald" wrote: It sounds like you have a classic case/requirement for a one-to-many relationship: one loan has one or more (i.e. "many") collateral items. This type of relationship requires you to set up two or more related tables. You approach the design from a completely different perspective in a relational database such as Access than you do with a spreadsheet. Bringing spreadsheet mentality to database design is a classic mistake that beginners make (no offense intended). I highly recommend that you purchase a beginners book for Access in your local computer store and read about proper database design. It would not surprise me if your 180 fields could (should !!??) be broken into several separate tables. Or post back with a broader description of what you are storing in your 180 fields. Lots of people here are willing to help. Good luck. On Fri, 10 Dec 2004 07:57:02 -0800, KLP wrote: Yes, I am using forms. Thank you for the tips. If I understand you and Allen right, instead of having 5 fields for each collateral item, e.g., coll desc 1, coll desc 2 ... coll desc 5, (total of 30 fields for all collateral items) because I figure no loan would have more than 5 pieces of collateral, I use a subform as you described, link by loan #, use a collateral auto # id, then list the 6 collateral fields. If my understanding is correct, it would reduce a number of those 180+ fields, and relieve the concern if a loan has more than 5 pieces of collateral. Thank you, Kelvin "Jack MacDonald" wrote: Hopefully, you are entering your data into the main table using a form. Next step is to design another form for the collateral table, but exclude the primary key. Drag the second form onto the first, thus creating a subform. Populate the "link" fields of the subform using the primary key from the main table and the collateral table. Now, whenever you create a new record in the main table (using the form) and subsequently enter collateral information using the subform, Access will automatically populate the primary key of the collateral table with the correct value. Even though you say that none of your 180 fields are redundant, that seems like a *lot* of fields, and is rather suspicious. On Sun, 5 Dec 2004 16:45:03 -0800, KLP wrote: I read ACC2000: Defining Relationships Between Tables in MS Access Database. This is in response to getting the error message about too many fields. The current table has 180 fields and counting. So I would like to break them up into several individual tables. By the way, no field is redundant. I understand from the article about linking tables. I guess I am using the one-to many relationships. According to the article, you drag the primary key from one table to a similiar field in the other table, often with the same name. The primary key in the "main" table is loan#. I created a "collateral" to hold loan collateral information. So I created a primary key in the collateral table called Loan#, with the same field specs as in the main table. It seems to me I now have a redundant field as I would have to now enter the loan # twice. Am I confusing myself or what? ********************** remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security ********************** remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security |
#13
|
|||
|
|||
On Sun, 6 Mar 2005 19:41:02 -0800, KLP
wrote: At the risk of sounding like a rube, hpw is that done? Kelvin "Duane Hookom" wrote: You should include the "linked to the tables containing the values" in your queries. Create a new Query. Add your main table to the query window. Add the Lookup Table to the query window. Select the "lookup field" from the joined lookup table. Access' Lookup Field is *horrible* for just this reason. It conceals the actual content of your table. What's stored in the table - and what gets used in any calculation - is a meaningless ID number. What you *SEE* on the screen (and reasonably may assume is actually *there*) is the value from the related lookup table. You can't get at that looked-up value by simply including your table; you need to include the lookup table into the query as well (and you can do so perfectly as well even if no Lookup field exists). John W. Vinson[MVP] |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
two relations between same two tables | Christopher Glaeser | Setting Up & Running Reports | 4 | November 25th, 2004 01:46 AM |
Relationship design problem with multiple tables | Don | New Users | 3 | November 24th, 2004 07:27 PM |
Help with Junction Tables and Subforms | Maureen Smith | New Users | 11 | September 23rd, 2004 02:39 PM |
Mutliple Tables lookup? | Westley | Database Design | 4 | June 15th, 2004 01:07 AM |
searching for "join tables" and "join word tables" | Uncle Bill | Tables | 1 | June 11th, 2004 09:33 PM |