Using Lookup Tables
Feb 20, 2005
I have seen a lot of database applications utilizing lookup tables with only one field. That field being the list of objects that is being used to "lookup" in a drop down or list box. (example: State lookup.. "South Carolina")
On the other hand, I see a log of database apps using a lookup table with at least 2 fields. One field representing an object code, the other being the object description. Typically in this setup, the object description is what is displayed in the drop down or list box of a form for the user's selection; whereas, the object code actually gets inserted into the database main table. (example: State lookup.. code: SC; Desc: "South Carolina")
I'm sure using either of these methods depends on preference; however, which is the most acceptable practice? What advantage does one method have over another. Please let me know if the question is not understood so I can clarify.
View Replies
ADVERTISEMENT
Jul 24, 2007
I'm wrestling with the issues; in other threads, it became apparent that because I could not know ahead of time what I will need to know about a given entity, I will use a table to enumerate attributes that is applicable for a given entity.
However, the stumper is that what if an attribute should conform to a set list of values? Since they are dynamic, I would have problem predicting what I will need to be able to lookup, and am even don't know whether I will need a one-many lookup or many-many lookup.
I thought that generic lookup table with a table listing "classes" of lookup would allow me to have one big generic lookup table while using "classes" to act like virtual tables so I can then set the query to appropriate "class" to return just right set of values.
But as I thought about it, I ran into some issues which is pulling me toward the crazy idea that I should have freestanding tables, and use a field in tblAttribute to give me the table's name so I'd know which free-standing table it points to, and have the necessary key to lookup the values within that table.
Even though my gut instincts tell me that I shouldn't be going against the conventions of database design (who the frick goes around creating free-standing lookups?!?), I'm simply not sure how I can use a generic lookup table to hold all information.
For example, suppose I was given a list of values that has its own categories. Since the former design allows only for two level (lookup and lookupclass), where am I to insert that extra level?
Furthermore, I found myself needing a set of virtual keys to reference a certain "class" of lookups for report purposes. That means I need an extra field in my lookup table than I originally anticipates. What if I find myself needing one more field that just won't fit the generic lookup table?
So does anyone have suggestions on how we would create a placeholder for a lookup table that will be made just in time?
View 4 Replies
View Related
Nov 23, 2012
how to do a particular thing in Access 2010 (I don't even know if it is possible).
I have a table named PRODUCTS:
ID_PRODUCT (primary key, autonumber long integer)
ALLOWED_OPTIONS (multi value text lookup field: "Option 1";"Option 2";...;"Option 9")
So I can store, for each different product, none, one, or more options to let the customers choose from.
I have a table named ORDERS:
ID_ORDER (primary key, autonumber long integer)
FK_CUSTOMER (foreign key, linked to the primary key of a CUSTOMERS table; represents the customer that places the order.)
FK_PRODUCT (foreign key, linked to PRODUCTS.ID_PRODUCT; represents the product that the customer has choosen)
CHOOSEN_OPTION (lookup text field; the customer must choose ONE option among those allowed for the product he has ordered)
The problem is that I would like the CHOOSEN_OPTION field to show as a combobox, listing the values stored into PRODUCTS.ALLOWED_OPTIONS, so that when a customer buys a product, he can choose only among the options allowed by that particular product.How can I manage a multi value field to populate a combobox, in which every item stays on its line? If I use, as a query to populate the combobox:
select [PRODUCTS].[ALLOWED_OPTIONS]
from PRODUCTS
where [PRODUCTS].[ID_PRODUCT]=[FK_PRODUCT]
I obtain an empty combobox.If I refer to the last field as [ORDERS].[FK_PRODUCT], Access asks me to type a value for "[ORDERS].[FK_PRODUCT]", treating it as an unknown parameter.I think that the problem is that when the combobox expands, the record is not committed yet, so FK_PRODUCT is unknown (NULL?). But this happens even if I commit the record typing something in FK_PRODUCT and then I re-enter the record and I expand the CHOOSEN_OPTION combobox, that is still empy although FK_PRODUCT exists, now.Is there a particular syntax to refer to a field in a record not committed yet (something like "THIS." or "ME.")?
View 5 Replies
View Related
Feb 18, 2013
I have an Access Table with about 28,000 Automobile dealerships across the country shown. I've joined a new/small phone contact table to this to keep up with our phone contacts with the dealership and followup efforts. When I search/filter on the dealership table all is fine. However when I search/filter on the phone contact table with a few test entries, I get nothing at all. I supposed that after joining the tables, I'd be able to do a search on the field named follow up date and find/filter today's date or other dates and locate which dealerships to contact when the correct date arrives. But nothing.
View 14 Replies
View Related
Jun 11, 2014
I have a database with various tables, customers, jobs_table, black_book. I am wanting to have a field that looks up a latest date.
Both the [jobs_table] and [black_book] have a date field, there can be multiple entries per customer in both fields. What I want to do is have 2 fields in the [customer] table that looks up the customer name in [jobs_table] and [black_book] and brings back the latest date.
View 1 Replies
View Related
Mar 13, 2006
I have a look-up box in a form that saves the description selected into a field in a query. I then use that query to produce a report, but on the report, the query has stored the ID field so the report doesn't show the description the user selected - just a number. How do I get the field in the query to save the description rather than the ID field so I can show it on a report?
View 5 Replies
View Related
Nov 7, 2004
Hi ... Question #4 (question #3 is beyond me)
Should all the lookup field be in one table or in several? Either way, how do you normalise them?
View 14 Replies
View Related
Aug 2, 2006
Hey all
I have always in the past created separate lookup tables but i am considering using one big lookup table for all lookups. For example having the states, cities, departments etc all in one table.
Can anyone give me the good and bad of doing it this way.
Thanks Hooks
View 2 Replies
View Related
Dec 11, 2006
Hi,
I was reading an article about look up tables, and it recommends that lookup fields shouldn't be done at table level but rather as combos at form level. Is this recommendation a valid one, and since I have some of my tables set up like this, if i change the field from combo box to text box in the table design, would that get rid of lookup table, or are there any further actions which i need to address?
Thank You very much for your help.
View 2 Replies
View Related
Mar 5, 2012
I have two tables; 1, a Customer Table that a customer supplies an ID and some personal information; 2, an Order Table that opens once the Create Order button is pushed on the Customer Table.
What I need to do is get the Customer ID in the Order Table to look up and populate from the Customer Table. I thought the best way to dothis is to use a LookUp Wizard, but every time I try to do that a window pops up and says that I need to save the table before any relationships can be saved. I save it and try again and get the same response. I tried to test it and it does not work because none of the information I populated in the LookUp Wizard was saved.
View 1 Replies
View Related
Aug 5, 2006
I would like to create a lookup field based on a table. However, I want the data displayed in a particular way. That is, I want 'dummy' fields for headings, and the selectable items are tabbed from these headings.
It is probably best to demonstrate what I mean, so here goes!
Sony
-PSX
-PS2
-PS3
-PSP
Microsoft
-XBox
-XBox 360
In my example above, the bold manufacturer names should be non-selectable. The console names are the selectable values.
A good example is the genre search field at:
http://www.pegi.info/pegi/search.do
However, as mine is for data entry rather than a search, the headings cannot be selectable.
Is this possible?
Any assistance would be greatly appreciated! Thanks in advance.
View 4 Replies
View Related
Aug 22, 2006
I have a database that has been developed by a consultant. Unfortunately the database contains look-up tables. Obviously when I query it returns and references the codes for the lookup field rather than the lookup field value. Unfortunately I am unable to change the database to remove lookup fields/tables. How can I get around this to produce tables with the lookup field value or perhaps reproduce the old tables (without just cut and paste) to then conduct my queries. Am I missing something simple as I am fairly new to Access development.
Regards,
Deesy.
View 2 Replies
View Related
Feb 15, 2007
Hello,
I have two tables in my access database.
A. Lets call one table "Map" and Map table has the following fields:
1) ZipCode_From
2) ZipCode_To
3) Salesperson
ZipCode_From : ZipCode_To : Salesperson
00001 : 10000 : Smith
10001 : 19999 : Jones
20000 : 29999 : Johnson
B. Second table is called "SaleMap" and SalesMap table has the following fields:
1) ZipCode
ZipCode :
00252 :
12568 :
22563 :
C. I would like my end query to look like this:
ZipCode : Salesperson
00252 : Smith
12568 : Jones
22563 : Johnson
Any help would be much appreciated.
View 1 Replies
View Related
May 1, 2012
I have a table which contains information about personnel. There are several fields which I want to have consistent values inputted. For example, somebody might populate the "State" field with: California, CA, Ca, or C.A. To avoid this, I created a seperate lookup table with a list of all states, fully spelled out, and the digraph abreviation associated with each state. The digraph is the primary key for this new table. In all, I have 12 such lookup tables.
Having all these extra tables, while nice for clarity sake, seems excessive. In most cases, the lookup tables really only require one column, though I've always used a minimum of two (one is an acronym set as the primary key, and one is a fully spelled out description). I'm finding the primary key is often not useful to somebody reading the data; the full description is much better.Could I have one catch all lookup table which combines all of the standardized fields which I want to use, and have no primary key for said table?
View 2 Replies
View Related
Sep 9, 2005
I have a table of 'things' and two related tables, type and subtype. Each thing has a type and a subtype. The types table contains simply Index (autonumber) and Type (text). The Subtype table Index (autonumber), type (number) and subtype (text), where index and type form the primary key and type is a foreign key to the types table. Thus each type has its own sub-set of subtypes.
In the design of the Things table I have set the lookup for Type and Subtype to be a dropdown, but of course the subtype drop-down shows all subtypes, not just valid ones. If I select a subtype that is incorrect for the selected type then of course I get an error, but it would be nice to set the query in the subtype lookup's Row Source to just display the valid subtypes. At the moment it just says "SELECT index, type FROM subtype" How do I put in a WHERE clause that references the Type field for the current row: "SELECT index, type FROM subtype WHERE type=<type selected in current row>"?
View 3 Replies
View Related
Dec 8, 2005
Hi ppl,
Anyone who thinks that they may be able to help me, there is a copy of the database at :
http://members.iinet.net.au/~a.beardsley/database/StaffProLearning.mdb
Here is my problem, as you will see. In the from called frmStaff, I have created 2 lookup tables that work fine in the main form. In the subform under activites though, I have attempted to create a lookup form for Name and Location of activities. It does not appear to be storing the information from previous entries though. My aim is so that when someone goes to enter a new activity name or location, the data will be saved and be in the list for the lookup next time with a different entry, however, it does not appear to be storing the information in the appropriate table. Any ideas on how to fix this?
Regards,
sugar05
View 3 Replies
View Related
Mar 20, 2008
Hey all.
I'm having a few issues with creating a Lookup on an Access form. I'll recreate the scenario below as best i can to help with my explanation.
I have 2 data entries. The first is a Post Code (Zip code), and the second is an Area Rating that applies to the Post code. For example:
Post code AB1 8 is awarded a rating of A
Post code SO1 3 is awarded a rating of F
I have already created a table in Excel with all 2,922 Post Codes (AB1 1 to ZE3 9) and the corresponding Area Rates (A to F)
What i'm making is a form that holds customer details. I'd like a combo box or text box that when you type in the Post Code, another text box automatically displays it's corresponding Area Rating.
Code:Post Code: Area Rating:------------- --------------| CM2 7 | | A |------------- --------------
I apologise for my really very poor diagram :P
So the Post Code box is either typed into or a combo box with the list of Post Codes, and the Area Rate box cannot be typed in to, but it automatically displays the record dependant on what is in the Post Code box.
Ideally, these will also be recorded onto the main table (IE not the table with the list of post codes or area ratings, but a 3rd table holding the rest of the details that will be used to create statistics and graphs)
Can anyone help me with how to do this? I've been trying to work it out all afternoon!
Any help is really appreciated.
View 3 Replies
View Related
Jul 12, 2007
Hi, all! I'm once again needing your sage, wise advice.
I was reading on MVPS.org, and found a page that denounces the use of lookup fields in tables, because of the way it skews relationships, weighing down the database.
http://www.mvps.org/access/tencommandments.htm
(#2)
Do you all agree with this?
What do you recommend I do, in lieu of lookup fields, when I need to maintain integrity of the data.
Thanks SO much. You guys are my heroes!
KellyJo
View 2 Replies
View Related
Oct 13, 2014
When I set up lookup fields that point to a table do I save those tables in the back end with the main tables or is it okay to have in in the front end and not the back end? Users will need to make udpates to the tables for instance an employee table they would need to add employee names.
View 2 Replies
View Related
Dec 1, 2012
I am building a horse racing based database, two of my tables being Courses (details of each track) and Races (Type of race, where run, prize money etc). In my Courses table I have CourseID (autonumber, key) and CourseName (text, max 25). The longest name in the list is 19 characters.
In my Races table, I have CourseID set up as a lookup, related to CourseName in the Courses table. This works fine if I type the CourseName in manually, or select it from the drop-down list.
My problem arises when I import it via excel, when the whole CourseID column is deleted, as not the correct data type. I have tried formatting the CourseID column in excel as text, general, and even number prior ti importing, all to no avail. How should I format this column in excel?
View 4 Replies
View Related
Oct 21, 2012
I have a member table and donation table. For a donation, I'd like to lookup the last and first names from the member table. I set the lookup for the last name and can see bothe first and last names in the drop down list. When I pick, I get the last name in the DonationT but how to I pick up the first name? That is, how do I fill DonorFirstName in DonationT from the FirstName field in the MemberT?
View 3 Replies
View Related
Jan 12, 2012
I've created a table (Films). One of the fields is linked to another table (People). The table 'People' is a list of people with a categorization next to them (e.g. actor, director etc)
Is there a way that I can create a field called 'Actors' in the table 'Films' and for the drop down only to display those people who have been categorized as an actor?
View 1 Replies
View Related
Apr 24, 2007
I'm new to access and trying to figure out how to use a field to look up and return results from multiple tables. I've tried several books and also the northwind traders example. What i'm trying to do is just like the oder form in the orthwind traders example on the order form. I would like to insert the customer name via a combo box then have the address, phone etc. automatically update as well as automatically update the shipping address etc with the current billing address. I know this is pretty basic but I must me missing a step.
Thanks in advance
:confused:
View 2 Replies
View Related
Oct 30, 2004
Hi, I am fairly new to ACCESS. I understand the basics, thus i am a novice user.
I am trying to create a new database for managing booking and storing info on various artists.
In my main table where bands' info will be stored I am trying to make data input as efficient as possible and to ensure consistancy.
So, it leads me to ask this question... regarding inputing info of bands' location.
I want to use a lookup table to enter data for:
1 Country (Canada/US),
2 Province/State
3 Cities?
Questions:
1 Whats the best way to organize this?
2 Do i have to create seperate tables for each country and province? Or any kind of suggestion would greatly appreciated.
Furthermore, when entering data on the form, for instance if i select:
Canada --> then I'd want only Canadian Provinces to show in the following field ---> then i select Ontario --> then i'd like only ontario cities to show in the next field....
3 What would require for me to create this kind of thing... Not sure if there are 'if' functions in ACCESS.
Could someone please guide me towards the light?
thanks.
View 2 Replies
View Related
Nov 22, 2013
I have problems understanding lookup tables, especially how to populate them with data. I'm working on an exams database, and have many such tables...
Table for Students
Table for Subjects
and a lookuptable for studentsubjects
Table for Classes 1, 2, 3, 4
Table for Subjects Eng, Math, Geo, Chem... 11 subjects in all
and a lookuptable SubjectClasses
Having inserted data for all subjects and all classes, should I then go ahead and Insert data for SubjectClasses? There will be 44 items!
Even more appalling, studentsubjects table will have (no of students * 11) for my small school, it will be 1,760
View 8 Replies
View Related
May 30, 2013
I have a date field that is just a typical date field. When I do a query, I want to look up by just the YEAR in that date field. Can I do that or do i need a separate text field with JUST the year entered in? or can i have a field in my table that just looks up the main date field and displays/uses ONLY the year??
View 3 Replies
View Related
