question about creating lookup forms

I'm creating a database to track our participants in Access 2010.

I have a few fields that I want to assign lookup values to.

School (MLK High, Central High, Northeast High.)

Interests (Visual Arts, Drama, Sports, Literature.)

Color (Red, Blue, Green.)

I could create a table School, another table Interests, another table Color, and then have the source for each one to be SELECT Color.ColorName FROM Color and then SELECT School.SchoolName FROM School and then SELECT Interests.InterestPicker from Interest. this is how I usually do things.

But then I wondered if instead of three separate tables, I could have one table called ParticipantData with fields called School, Interests, Color. and then have my lookup queries be SELECT ParticipantData.School from ParticipantData, and SELECT ParticipantData.Interests FROM Participant, and SELECT ParticipantData.Color FROM ParticipantData

Are there advantages/disadvantages to one way or the other

Sponsored Links:

Related Topics

Download in MS Word file format (.doc)
This tutorial has been designed to give an introduction to some of the basic features of MS Access. The tutorial will cover the following topics:
Creating a database; Creating tables; Changing the Field Properties in the tables; Adding Lookup Tables; Creating relationships; Adding Passwords to the database; Creating simple forms using the Form Wizard; Adding graphics and creating customized forms; Creating forms using multiple tables; Creating reports using the report wizard; Formatting reports; Creating Switchboards.


Lookupform(s)
I'm in the pre-stage of building an application with about 15 lookup tables. In stead of building 15 lookup forms, I'm thinking about making one generic lookup form and changing the record source depending on the table needed. I would have to add code for validation purposes.

Is this doable or should I make 15 separate forms?


Can't see choices in lookup drop-down
I am creating a database that includes two tables of names, one for our Admins and one for our Inspectors. In the other tables/forms, I am putting text boxes as lookup fields so that the user can select their name from a dropdown as they complete the various forms in the database.

Except, when the down arrow is selected for the lookup, you can't see the names. The dropdown acts as if there is text there, the Admin table has 2 names and its dropdown has blank spaces for 2 names. The inspector table has 3 names and its dropdown has blank space for 3 names.

When you position the cursor where the name text should be in the drop-down and left-click, the name magically appears in the text box. I used the lookup wizard to create the fields and I can't figure out why I can't see the text, but it acts like its there.


Field In Form Lookup
I am not well versed in form creating. I have a database that I am creating. I am building a form and a field on the form I need to be a lookup field that is based on query results. This form also needs to update a table that has the field that has the lookup value. Is this possible.

After first I created a table that had a lookup value from the query but I got an error message that it would not allow me to do this


Access 2007: Creating a Lookup List
Learn how to create a Lookup List to populate field data and to ensure consistency. This article shows you how to add lookup fields to a Microsoft Access table. First, it shows you how to use a wizard to add the lookup field, and then it shows you how to add the lookup field manually.


Lookup tables made slow
I used lookup tables (using the lookup table wizard) when I designed my DB. Now that I've split the DB it's caused some serious slowness when forms are first open and of the tables start looking elsewhere to pull data.

Is it possible to break the relationships created by the wizard and then recreate the relationships and have the lookup be done by the form withouthaving to completely rebuild the DB?


Is a Lookup Field the same thing as a lookup table
From reading some of the threads, it's clear that Lookup Fields are not a good idea in tables.

I have found many resources online with examples of different databases and in one I am going through right now, there is one that has a People table (for both people and companies) and a Type table.

The Type table categorizes the type of relationship between the person/company in the People table and (presumably) the user/company keeping the database (relationships such as: Friend, Family, Professional, Auto, Hospital, Art Supply, etc).

**In the description for the Types table, there is a statement that says "The Types table is commonly referred to as a lookup table."**

My question is this: Is a Lookup Field the same thing as a lookup table, as described?


Putting three list boxes once selected into one column
I have a MS Access 2003 database with several tables, queries, forms etc. My question is I have a simple form which has 5 boxes on it listing information from tables and queries. One of the boxes is linked to a lookup where I can get it to display three values but once the user selects which one they want it only displays the first value.

I want it to display all three under the one column so to speak. The Lookup lists Order, Forenames and Surname but only lists the Order when selected.

Is there any way I can get three values under the one column rather than having to put three boxes with three lookup's and then the user having todo it three times to get the information?


Use a lookup field to influence values in another field
I've been out of practise with using Access for a while, so this may seem a rookie question!

Basically, I'm looking to create a table that holds event information. One of the fields I've successfully included is a Lookup function to pull info from a contacts table. This table also has statistics in it.

What I'd like to do on the events table is lookup a contact name in one field, and then in another field within the events table, pull over the relevant statistics based on my selection.

Now, I've been able to get the lookup to pull across all of this already, but as far as I can tell, you're only able have one column displayed (bound) in the lookup - is this right?

If you've any suggestions I'd love to hear them. Please ask if you need any more info


Why creating lookup fields
I am a bit confused as to why creating lookup fields in my table is such a bad idea. I read "Access: The Missing Manual" as an introduction to Access. I found it very helpful but I did not find in it any admonision against this practice


Table Lookup
I understand the problems that can be brought upon ones self by creating the table with lookup fields, But if the table was orginally designed with them and then the Field is then changed to a Text box instead of a Combo box will the inherent problems associated with the lookup within a table disappear?


simple address db question
I know in the back of my mind I know how to do this but.

when I select the suburb in the lookup field I need the table to automatically update the postcode and state field from the lookup table - like a vlookup function in excel


Unwanted Lookups automatically added to Forms
I'm a beginner in Access and just completed a 3 day class. Was doing fine until a few days ago. I created a table in a new database, and put in a lookup in one of the fields of the table. Realizing that I should have done this in the corresponding form instead, I tried to delete it.

When I created the form, the control for that table field had the up/down lookup arrows on the control, even though there was no list to look up.

Could not delete it. I started over with a new database and got the same results.
Now - every single form I try to create, either in existing databases or new ones, has the lookup arrows on every control of a text field. I am not adding lookups to the tables or the forms.The forms have no lists of data to look up, just the arrows.

How do I get rid of this? I have done several dozen tests and keep getting the same results.


Why can't I find a table in the lookup wizard?
Using Access 2007, I need to create a lookup field for an existing table, but it is not listed when creating it.


Lookup Fields in Form
I have a lookup field in a payroll database table. It pulls up a payroll 'service code' from a different table. I made a form so that staff can enter their payroll data, (name, date, mileage, hours, etc) including entering the 'service code' from a dropdown box to designate what they were doing.

I was asked to make a change so that there was both a 'service code' and a 'description of the service code' column in the drop down.

I changed the lookup field at the table level, but how do I get the drop down on the form to reflect this change? When I couldn't immediately figure it out I tried to delete the textbox on the field and put a new one in its place in the hopes it would reflect the qualities of the new lookup field in the table but now Ican't even figure out how to get it back to being a dropdown again.

(I'll admit, I made this DB a few months ago, I don't work in Access often and I forget how I made the drop downs on the form work to begin with.

I just don't want to have to delete all my forms (there are actually 3, one for each department) and spend my weekend creating new forms from scratch.)


where to put the query ?
so my DB is split. I noticed that I cannot link to queries in the back end. As a result, my front end has all my queries.

Originally my DB was designed as a non-split DB. some of my tables had lookup fields based on queries. Now that my DB is split, the tables in the back end do not show the correct data when opening the table in the back end.

If I open the table link in the front end, all is correct.

So now to my question, should I create the lookup based on "bult in" query, or should I leave it as is.

Also, should I base fields in forms on a query, or should I have the query embeded into the forms field ?


Sum/addition function
Is it possible to create something like this in Access?

If MR.X input values such as:
5 for question A
5 for question B
10 for question C
5 for question D

then Question E should be the sum of A+B+C+D which is equal to 25

Now, if E is equal to 25, I need a field (lets name it as F) where it will automatically subtract on what E has. The output should be 75 (from 100-25 = 75)

I was thinking of creating a form and then a query


Creating my survey database
I am trying to create a database that will have a set of questions to be answered by an employee regarding a potential client. Each question will be comprised of three data types; A Yes/No, Memo and Attachment as well as a drop-down (lookup) of the type of review being done for a set of questions.

A client can have multiple reviews seeing as there are different review types.

Each question can have upwards of thirty attachments (screenshots). I've tried so many different schemas and nothing seems to be working right.

Below is a list of my latest schema with tables and their respective relationships.


Geeting more out of a lookup table
I have tried a few things in MS Access help but I can't seem to get what I need.
I have a very simple database and I enter records using a simple form using several simple (I keep using that word but it's true.) lookup tables.

On the lookup table in question, it would be really useful if the form could collect more than one item of data and put it in to the form (and therefore the main database automatically.

I'm sure this is easy so let me give an example for clarity.

If I enter a bird species in the form, then that lookup table will give me the full species name and enter it. Great. What I need is to be able to enter info on to the lookup table regarding its conservation status and then have this appear automatically on the form and the main database table


Open Unfiltered Form to Specific Record
I have two forms: Employee Lookup & Edit Record. These forms work, for the most part, as you would expect.look up an employee on the Employee Lookup form; if you want to edit the information for the person, click the Edit Record button to have the Employee Lookup form close & the Edit Record form open to the desired record.

the Edit Record formis filtered to show only the desired record.)

When all editing is done, if you click the Save Changes button, I can get the Edit Record form to close and the Employee Lookup form to open but it's either opening as an unfiltered form to the first record (not the record that was edited).or it's opening as a filtered form to the edited record.

using VB, in opening the Employee Lookup form (unfiltered) to the specified record.

The names of the forms are:

* frmEmployees
* frmEmployee_Edit

The unique identifier for each record is EmpID.

my explanations can sometime come off that way.)