[MS_AccessPros] One lookup table for entire app vs. multiple lookup tables

 

Tried searching the Yahoo Groups history but I'm not seeing a discussion of this topic.

I'm designing a new Access 2016 app that will include some lookup fields. Users must be able to extend and change the list of values in some lookups.

I've had good success in the past using one table to host all string-based lookup fields for an entire app. (This is in a VB6 / SQL app.) Probably the main advantage is that I only had to create one form for adding/updating lookups.

It seems that most Access examples I'm seeing use one Lookup table per field. Is there something in Access that makes this approach more advantageous?

Here for clarification are the two tables I've used in the other app to manage lookups (as defined in SQL). LookupHeader is only needed on the Manage Lookups form to control what the user is allowed to do. When other forms and reports need a lookup value, they only need to reference the Lookup table, though of course they have to include the FieldName in the query.

CREATE TABLE [dbo].[LookupHeader](

[FieldName] [varchar](50) NOT NULL,

[FieldDescription] [varchar](200) NULL,

[UserCanViewItem] [bit] NOT NULL,

[UserCanAddItem] [bit] NOT NULL,

[UserCanChangeSortByValue] [bit] NOT NULL,

[UserCanChangeDisplayValue] [bit] NOT NULL,

[DBValueMaxLength] [tinyint] NOT NULL

CREATE TABLE [dbo].[Lookup](

[FieldName] [varchar](50) NOT NULL,

[SortByValue] [varchar](50) NOT NULL,

[DBValue] [varchar](50) NULL,

[DisplayValue] [varchar](50) NOT NULL,

[ValueDescription] [varchar](200) NULL

Mark Berry

MCB Systems

__._,_.___

---

Posted by: Mark Berry <mcbsystems@gmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___