lookup fields

in my main table i have five of the fields which their datatype is set to lookup wizard. i have created another table with 5 fields where i have type in the values from where the main table can get the values from.

my problem is that when i created a form using the main table fields, when i come to the combo boxes, so that the user can see a list of possible values, between the values there is being created a lot of empty space. This is due to the fact that in the table where i keep the values, not all of the fields have the same number of entries. Is there a way, that i can make all the values being listed exactly under each other ?

example:

ValuesTable
=========

Category SubCategory Subject
Computing Programming C
Literature Networking C++
Web Design Javascript
Perl
Pascal


and so on;

in the combo boxes, the values from the fields with less values are being displayed as:

Programming

Networking
etc

depending on the sortorder of the subject field.

Thank you!

First of all, you can edit the query which the lookup wizard created to exclude the null value from each field.Thats in case you insist on putting the five fields in the same table. But typically lookup fields have a specific lookup table for each field and this is simplest, most straight forward and best approach.

so you mean that i should have 5 tables (since i have five lookup fields) all with 1 field each ?

Moreover, can you give me further detail pls regarding the exclusion of the Null value ?

Thank you for your help!!

Yes I have worked with several databases over 6 years, some of them I designed myself and developed ones designed by others.
The idea of having lookup tables is fundamental to database desing. You will always need to have lookup tables for Categories, Cities, Areas, Payment methods, departments, sales reps,....etc
This kind of tables is called static tables because they change rarely, but from time to time they will need some maintenance like adding an extra field (for example.....by time some departments no longer exist and some sales reps are no longer working and it is a good idea to mark those which are still active using an extra field) you might also want to add a date where this sales rep left the company or a product got discontinued.
So it is always a good idea (in my opinion) to keep lookup fields in separate tables because this will keep things more organised, readable and manageable. Having to many lookup tables in your database wont be taken as a sign that you are a poor developer.

Best of luck!

Regarding the null values, you can always filter null values out, or search for them by using the 2 access functions IsNull() and NotNull(), if you place them as a query parameter in the query grid, you get the desired result.

thank you for the prompt reply.

i think i will go for your idea of creating a seperate table for each lookup.

thanks!!

I use a single table to hold all of my simple lookup values. I built a code table maintenance application for the first time over 20 years ago and have implemented similar mainframe systems numerious times since. Now that I've switched to Access, I can more easily port my application into each new application that I develop.

tblCode:
CodeID (autonumber primary key)
TableID (foreign key to tblCode.CodeID - self-referencing relationship)
LongDesc
ShortDesc
ActiveFlag
ChangedBy
ChangedDate

The table contains a "list" of tables in addition to the values for each individual table. A sample data list might be:

1, 1, CodeTable
2, 1, Gender Code
3, 1, Relationship Code
4, 2, Female
5, 2, Male
6, 2, Unknown
7, 3, Mother
9, 3, Father
10, 3, Sibling
11, 3, Child

I've included a picture of the report I use to print the code table values.

Thank you for the tip.

However, i would like to ask if someone can help me with this:

i was thinking of keeping one table and then in the row source where there is the SQL statement SELECT field FROM table i would add a WHERE is not null.

Anyone can help me how can i set this pls ?

Thanks you !!

I recommend creating all queries in the query builder. Then once the query works, save it. You can refer to a stored query by changing the recordsource of a form/report or the rowsource of a listbox/combo.