Is this a dumb question?

[jenestra]

I'm still trying to figure Access out so bear with me.



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? Or is it different and have I provided enough information to determine?

This is all confusing enough without trying to distinguish two different things with similar names.

[Ryan]

No Question is dumb!

And if I understand your question right,

The lookup Field REFERS to something in table

For a better understanding here is a Link to Microsoft, and Examples.

http://support.microsoft.com/kb/304462

[martyn]

A lookup field can be either a table or you define your own look up. When you use the drop down look up wizard in the table design you will see where it is. But one looks up the info in a table where the define your own looks this information up in a source row. Hope this helps.

[dcrake]

Using native lookups in directly in tables is generally concieved as a bad option. Something that Microsoft thought would be useful. When you create a field in a table that is of a certain type you can tell Access to get the allowable entries from another table so that when you look at the table as a table in datasheet view when you click on that field the field becomes a drop down combo box (if chosen) thus preventing invalid entries.


Now this is fine, until you try to analyse it in a query or the like. This is why people frown upon it. However you can still have a "Lookup" table that contains a code (Primary Key) and description. However in your main table you would store only the relevant code. This is known as a foreign key. You would then use a query to grab the description of the code. Also in your query do not bring down the code from your main table and query that field, but bring down the code from your lookup table and query that one instead. Why? Well in your main table you will have many instances of this code and probably is not indexed, however the code in the lookup table should have an index on it as it is the primary key. This means that searches are greatly improved as it is searching an indexed field as opposed to an non-indexed field.

If you need any further clarification let me know.

David

[jenestra]

Okay....here's what I need to do. First of all, thank you so much for your help.

I'm trying to create a database to keep track of homicides in my city. (No, I don't have a weird fascination, I work in the Crime Analysis Unit in my city's PD. We currently have our homicide information located in a few different places and I'm just trying to consolidate it all)

Most of the fields/values are dictated by the California Dept of Justice (DOJ). So far, I've created tables for each of these fields and I'm still in the process of figuring out the relationships so I'm not really near a point where I'm going to start building this thing soon. Just wondering how I'm going to do it, once I get there.

Basically, I want a form that a homicide detective can open and plug in information. In the DOJ fields specifically, they'll only be allowed to select certain answers (depending on the field, anywhere from 4 to 40 choices will be available) and under no circumstances do I want them to be able to fill in their own answer. (Which they will try )

So there'll be a form, the detective will fill in the basics: victim name, date of birth, sex, race, location of event, date occurred, etc. Then, they'll get to the part where they need to (for example) define the relationship between the victim and offender. At this point, they'll need to see their options. So the detective will either click a drop down or scroll down a mini table to look for the correct relationship and select it.

So I guess my question is this.....
Is there a way to still have a physical drop down field to make a selection without it being a lookup field? Or is a drop down box always a lookup field and what is my best option for fields like these? Subforms?

[farls]

you're confusing tables and forms here. On your form you would use either an unbound combo box or an unbound list box that would look at a table or even beter a query that looks at a table that pulls the data from the table in the manor you want.
which would limit the user to selecting only the data for the field(s) that pre exists in your orginal table

[dcrake]

If you consider that certain questions can only have certain answers as per your explanation. So if one of the questions is "What is the relationship between victim and assalant?" and you want to limit the choice. You first need to create a table to contain the choices, this table will consist of an autonumber (which becomes the primary key) and a description field. (available options).

Now in your main table you will create a field that will store the code from your above table, this is known as a foreign key. Then if you are going to set up relationships you would link the primary key in the relationships table to the foreign key in your main table. So that when using queries you can display the corresponding description.

Now onto your form, If you are using a bound form you can still use a combo box for reationships you just need to ensure that the bound column in your combobox relates to the foreign key in your main table. You would hide the bound column and just have the description appearing in the combo box.

You can repeat this for combos that require lookup lists.

David