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

 

I don't think you are missing anything. When used in reports, the single table should work well.  As long as you use combo boxes in forms the data should allow edits.

Duane

---

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 7:40 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] One lookup table for entire app vs. multiple lookup tables

 

Hi Duane,

Thanks for the heads-up re. updating. I remember a similar issue with multi-table updates in ADO.

I can see using JOINs to retrieve Lookup values for reports. In my old project, the JOINs looked like this:

SELECT Contacts.*

, GenderLookup.DisplayValue AS Gender_Lookup

LEFT OUTER JOIN Lookup GenderLookup 

ON GenderLookup.FieldName = 'Gender' 

AND Contacts.Gender = GenderLookup.DBValue

Haven't looked at Access reports yet so don't know if that's applicable.

However, I don't see the need for a JOIN when retrieving the base table for a form. Just retrieve the raw data, then make the combobox pull the display values from another table and update the base table when necessary.

I tested this in Access by creating this Query for Lookup_Gender:

SELECT Lookup.FieldName, Lookup.SortByValue, Lookup.DBValue, Lookup.DisplayValue, Lookup.ValueDescription

FROM Lookup

WHERE (((Lookup.FieldName)='Gender'));

Then in the form, the combobox Row Source looks like this:

SELECT Lookup_Gender.SortByValue, Lookup_Gender.DBValue, Lookup_Gender.DisplayValue

FROM Lookup_Gender

ORDER BY Lookup_Gender.[SortByValue];

It wants to display all three values in the combobox, but I shrank SortByValue and DBValue so only DisplayValue appears.

Bound Column = 2 (DBValue) so when the combobox value is changed, DBValue is copied to the base table.

Am I missing something?

Mark Berry

MCB Systems

On Thu, Sep 7, 2017 at 11:15 AM, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

 

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 (4)

---

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

.

__,_._,___