Alternative to Lookup fields? (1 Viewer)

Hey all,

I was just wondering, in datasheet view (on a subform) I normally use Lookups to let the user select their options. However, I've been told this is poor design and impedes performance. Is there an alternative to this? I'd like to keep my subform in datasheet view so I can do bulk updates (copy and paste) from Excel when needed.

Any help would be great!
-NS

Lookups (combo and listboxes) on forms are not an issue and are very common. However, lookup fields at the table level are the problem; this site explains why.

jzwp22 said:

Lookups (combo and listboxes) on forms are not an issue and are very common. However, lookup fields at the table level are the problem; this site explains why.

Click to expand...

Yes, that is what I meant (at the table level). Is there a suitable alternative?
Thanks,

-NS

The alternate is to just use combo or list boxes in your forms.

You would just reference a key field in what was the lookup field in the table. That key field (also known as a foreign key field) would correspond to the primary key of another table. Let's take a simple table that holds information about people. Each person has a title, but that title can apply to many different people.

tblPeople
-pkPeopleID primary key, autonumber field
-txtFName
-txtLName
-fkTitleID foreign key to tblTitles, this has to be a long integer datatype field and should not be a lookup field

tblTitles
-pkTitleID primary key, autonumber
-txtTitle

When you create your form based on tblPeople, you would use a combo box (or list box) to populate the fkTitleID. You can use the combo box wizard to create the combo box, you would include the primary key field and the txtTitle field of the tblTitles. Access will give you the option of hiding the key field value so that you only see the actual title but the key field's value is what is stored in tblPeople.

After you enter records and look at the records in tblPeople, you will just see a number in the fkTitleID field; that number would correspond to a title in the tblTitles.

If you want to see the people with their respective title outside of the form, you would use a query that joins the tblPeople with tblTitles and select the fields you want to see.

I've attached a simple database that illustrates this.

Thanks for the example Jzwp. I understand what you have done and how. However, what I ideally need is to have that laid out horiztonally and in a continuous form (in a subform, if that makes sense). However, the reason I was opting for datasheet view was so I could do bulk copy and pastes from Excel if needed. I dont think this can be done if using forms such as the one in your example?

-NS

Oops, I forgot to switch the form to datasheet view. Here is the DB again.


By the way you will be able to copy from Access to Excel but not the other way around. You will have to use append queries to get the Excel data into Access.

jzwp22 said:

Oops, I forgot to switch the form to datasheet view. Here is the DB again.


By the way you will be able to copy from Access to Excel but not the other way around. You will have to use append queries to get the Excel data into Access.

Click to expand...

Just saw the update! That is exactly what I wanted - I wasn't aware you even do that in datasheet view without using table level lookups!

Many thanks,
-NS

You're welcome.

Hi, Thanks to all. it helped me to try out doing lookup combo in a form linked back to source tbl for input text to a target tbl without a join query. Attaching revised file.

jzwp22 said:

When you create your form based on tblPeople, you would use a combo box (or list box) to populate the fkTitleID. You can use the combo box wizard to create the combo box, you would include the primary key field and the txtTitle field of the tblTitles. Access will give you the option of hiding the key field value so that you only see the actual title but the key field's value is what is stored in tblPeople.

Click to expand...

Thanks a lot for the explanation, jzwp22!

I was doing the query at table level and also at form level! :banghead:

But I guessed it was wrong and I was looking for an explanation on how to solve the "Evils of Lookup Tables" and your explanation was super simple and direct!

Now I know and that´s half of the way!