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

 

Mark,

I've also used a single table in the past for the same reasons. I would be concerned about the updateability of record sources that combine the generic lookup table with the main tables. 

Your unique primary key in the Lookup table would probably be:

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

  [DBValue] [varchar](50) NULL,

You can't join using both fields.

I did a quick test

SELECT tblProductionTT.prdPrdID, tblProductionTT.prdFacID, tblProductionTT.prdTS, tblProductionTT.prdLineNum, Lookup.DisplayValue

FROM tblProductionTT INNER JOIN Lookup ON tblProductionTT.prdLineNum = Lookup.DBValue

WHERE (((Lookup.FieldName)="prdLineNum"));

I get the message the recordset is not updateable.

Regards,

Duane Hookom

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Mark Berry mcbsystems@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Thursday, September 7, 2017 12:41 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [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: Duane Hookom <duanehookom@hotmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

---

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

.

__,_._,___