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
|
|||
|
|||
Lookup Table useage
I have several tables that all have a common field which I would like to use
the same lookup table to be the source of this value because they all stand for a type of metal that is being used. Each main record will have a long integer field, and will be related to the lookup table's pk value. When I went to make relationships on the Relationships window, each time I made a new relationship (1-to-many) another copy of the lookup table popped onto the window, delimited by a _1, _2, etc. Furthermore, when I create queries later on, and I want to find out what metal that a pk val of, for example, "3" corresponds to, how will I be able to add the lookup table metal description to the query? I suspect that I need to use an alias, am I correct? Thanks a ton! -- Message posted via http://www.accessmonster.com |
#2
|
|||
|
|||
Lookup Table useage
How is it that you have "several tables that all have a common field"? That
sounds like having multiple tables that each have a "LastName" field ... but the tables all contain persons... To show what metal goes with "3", add the lookup table to the query, join on that field, and include the "looked up description/name" field in your query for display. You don't need to show the "3". Regards Jeff Boyce Microsoft Office/Access MVP "ionic-fire via AccessMonster.com" u35684@uwe wrote in message news:980c4fa976f7f@uwe... I have several tables that all have a common field which I would like to use the same lookup table to be the source of this value because they all stand for a type of metal that is being used. Each main record will have a long integer field, and will be related to the lookup table's pk value. When I went to make relationships on the Relationships window, each time I made a new relationship (1-to-many) another copy of the lookup table popped onto the window, delimited by a _1, _2, etc. Furthermore, when I create queries later on, and I want to find out what metal that a pk val of, for example, "3" corresponds to, how will I be able to add the lookup table metal description to the query? I suspect that I need to use an alias, am I correct? Thanks a ton! -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
Lookup Table useage
On Tue, 23 Jun 2009 21:37:17 GMT, "ionic-fire via AccessMonster.com"
u35684@uwe wrote: I have several tables that all have a common field which I would like to use the same lookup table to be the source of this value because they all stand for a type of metal that is being used. Each main record will have a long integer field, and will be related to the lookup table's pk value. When I went to make relationships on the Relationships window, each time I made a new relationship (1-to-many) another copy of the lookup table popped onto the window, delimited by a _1, _2, etc. Furthermore, when I create queries later on, and I want to find out what metal that a pk val of, for example, "3" corresponds to, how will I be able to add the lookup table metal description to the query? I suspect that I need to use an alias, am I correct? Thanks a ton! First off... NEVER use Lookup Fields in your tables. Not even oncet. http://www.mvps.org/access/lookupfields.htm has a critique. Secondly, never use Tables for data editing or data entry. That's not their purpose. Using a Form with combo boxes (based on your lookup field) is fine. That's how you should interact with data. You'll get the number-suffixed copies of the lookup table in the relationships window if you have the same two table related more than once. Relating TableA to LookupTable and TableB to LookupTable shouldn't cause this effect - could you explain what your tables are and how they are related? -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Lookup Table useage
I think I was too vague. I am using forms to enter/modify all of my data.
I have several tables that hold data about different equipment; each equipment has unique characteristics that are tracked using each table. However, one unique element is the MOC (material of construction) that the equipment is made of. Different equipment will have different MOC's. But since we use the same "pool" of MOC's in all our equipment, I thought it was best to just have one MOC lookup table. The table for each equipment has a Long Integer field to store a pk val from the MOC lookup table. Then in my query later on, I add in the description corresponding to this particular MOC long integer value. Of course, I have the tables joined in the Relationships window. This is where I was getting the multiple tables with an underscore and number at the table name end. I was trying to use the same table to be the row source for combo boxes in my form, so that I have only one lookup table. For example, I have: tblSiteInfo tblSiteInfoReactor tblSiteInfoSeparator and lookup table is tblMOC_LU Since there can be multiple reactors and separators in a site, I have a 1: many relationship set up between tblSiteInfo and tblSiteInfoReactor, as well as between tblSiteInfo and tblSiteInfoSeparator. In tblSiteInfoReactor, I have a field called lngzMOCID, and the same for tblSiteInfoSeparator. On my form, I have a combo box that uses tblMOC_LU as the row source, and stores the pk val corresponding to a MOC choice into the appropriate table's current record lngzMOCID field. When I set up relationships between these tables, that is where tblMOC_LU gets the suffixes of _1, _2, etc. Perhaps I was just confused, but I did not think I should have several different lookup tables, each containing the same data just with a different table name. John W. Vinson wrote: I have several tables that all have a common field which I would like to use the same lookup table to be the source of this value because they all stand [quoted text clipped - 11 lines] Thanks a ton! First off... NEVER use Lookup Fields in your tables. Not even oncet. http://www.mvps.org/access/lookupfields.htm has a critique. Secondly, never use Tables for data editing or data entry. That's not their purpose. Using a Form with combo boxes (based on your lookup field) is fine. That's how you should interact with data. You'll get the number-suffixed copies of the lookup table in the relationships window if you have the same two table related more than once. Relating TableA to LookupTable and TableB to LookupTable shouldn't cause this effect - could you explain what your tables are and how they are related? -- Message posted via http://www.accessmonster.com |
#5
|
|||
|
|||
Lookup Table useage
On Wed, 24 Jun 2009 16:00:12 GMT, "ionic-fire via AccessMonster.com"
u35684@uwe wrote: Perhaps I was just confused, but I did not think I should have several different lookup tables, each containing the same data just with a different table name. Don't worry, you don't. There's only one table. It's just displayed repeatedly on the Relationships diagram window, since you have multiple relationships to the same table. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|