lookup fields in tables are bad practice? What's the alternative?

[merlin777]

I'm an Access novice and I'm building tables.

I tried changing a datatype in a table to 'lookup wizard' so i could enter data in that field from a list of items in another table.

I was given some errors as a result, researched the issue and I find this is bad practice (mainly because it leaves an underlying number to work with).

I'm happy to accept this is the case but what's thhe alternative? I haven't moved on to forms yet - i'd like to get mytables set up.

Thanks IA

 

[alansidman]

Your alternative is to do your lookups in your form which is bound to the table you have established. Just keep it as an open item until you get to building your forms.

 

[merlin777]

Thanks for that Alan. I'm not quite understanding a couple of those things.

Do you mean the table I was trying to put the data in or the table that contains my list of items?

What do you mean by 'keep it as open item'?

 

[alansidman]

Look at this link and scroll down to combo boxes. There are three tutorials that will show you how. "Keep it as an open item" means that it is on your to do list when you get around to building your forms.

DataPig Technologies Access Videos

 

[merlin777]

Thanks, Alan. That's a great resource.

 

[LeslyeE]

If you are wanting to change data in the fields of a table why don't you use an update qry based on an identifier? Otherwise if it is new data to the table you could use an append qry. Both would allow you to add data to a table without having to have knowledge of forms.

 

[merlin777]

i'll take a look at that but, novice as i am, 'form' seems a lot simpler than 'use an update qry based on an identifier'....

If you are wanting to change data in the fields of a table why don't you use an update qry based on an identifier? Otherwise if it is new data to the table you could use an append qry. Both would allow you to add data to a table without having to have knowledge of forms.

 

[LeslyeE]

https://support.office.com/en-us/ar...-a-query-f01eb7a3-a673-4edd-a425-f6769d2bb717

This will help you learn to use Update qrys. It is very simple and easier than creating a form. You can update based on other table data or a static value. There is good versatility in use of both an update qry and an append qry. It depends on if you want to change existing data "Update" or create a new data set "Append" on an existing table.

 

[Micron]

If anyone but you is using this db, by all means update data or create new records via form only. The last thing you should do is expose (or allow to be exposed) tables or queries to users. This is why db's are split into front and back end components. Designers go to great lengths to keep people out of the back end for good reason. Granted, whatever you can learn about queries will help since they are the underlying record sources for your forms and reports. Maybe you'd also gain more insight as to why lookup fields are generally considered a questionable idea by MS - The Access Web - The Evils of Lookup Fields in Tables

Hopefully, what you gained from your review of combo boxes is that they are unique in that you can bind them to a table AND their row source (aka list items) can come from somewhere else. The chosen value ends up in your table.

 

[merlin777]

thanks, everyone.