Jumat, 08 September 2017

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

 

Graham,

You're suggesting a foreign key relationship between Lookup and each table field that needs a lookup? And store the Lookup's autonumber in the table? I guess that could work, but I've always just kept a short string value in the table, then used the Lookup to expand it. In my example, the Gender field contains M, F, or N, which corresponds to as DBValues in Lookup. I still use relationships between "major" tables when I need to enforce integrity (Employer > Employer etc).

Mark Berry
MCB Systems

On Thu, Sep 7, 2017 at 9:42 PM, 'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
 

Hi Mark

I think it should be fine if the Lookup table has a single autonumber primary key.  You can still filter on the FieldName to get the range of valid values, but the unique ID value would be the only thing stored in the foreign key field.

Of course, you would also need a unique secondary key comprising FieldName and DisplayValue, to prevent duplicates in a given display list.

Best wishes,
Graham

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, 8 September 2017 15:00


To: MS_Access_Professionals@yahoogroups.com
Subject: 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: Mark Berry <mcbsystems@gmail.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (6)

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.


.

__,_._,___

Tidak ada komentar:

Posting Komentar