Hi Mark
Yes, for a lookup to work effectively you need to store the primary key of the lookup table in the foreign key field, otherwise it isn't a true one-to-many relationship, and you run into the updateability issues that have been discussed.
Also, a general table will only be useful in very simple situations. Where you have a hierarchy of lookups – for example, product type and category – then it's not really feasible. Also, when you have more complex related data in your lookup table. I think Gender is an example of this. My Gender table usually has additional fields for pronouns etc – for example, M/Male/he/him/his/son, F/Female/she/her/her/daughter. That way, the corresponding field from the lookup table can be inserted into reports and mail merges, etc.
Best wishes,
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Saturday, 9 September 2017 04:48
To: MS_Access_Professionals@yahoogroups.com
Subject: 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: "Graham Mandeno" <graham@mandeno.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (7) |
Tidak ada komentar:
Posting Komentar