Jim Dettman
jimdettman at verizon.net
Tue Aug 12 16:14:31 CDT 2014
<<Just saying that some codes are just not as "forever" as you may think.>> True enough, but there is nothing in relational design that says a primary key cannot change. That myth grew up out of data warehousing requirements and has since been carried forward into relational designs. AN/Identity keys are a convenience used to achieve performance and sometimes, it's plain silly to include one just for the sake of having one. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jack drawbridge Sent: Tuesday, August 12, 2014 05:00 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Field linked to different tables I recall when Canada created a new area Nunavut (NU), then changed the abbreviation for Quebec from PQ to QC, and Newfoundland to Newfoundland and Labrador and the abbreviation from NF to NL Just saying that some codes are just not as "forever" as you may think. Just for reference, here is a brief item from ISO 3166 Country Codes - ISO 3166 What is ISO 3166? ISO 3166 is the International Standard for country codes and codes for their subdivisions. The purpose of ISO 3166 is to define internationally recognised codes of letters and/or numbers that we can use when we refer to countries and subdivisions. However, it does not define the names of countries - this information comes from United Nations sources (Terminology Bulletin Country Names and the Country and Region Codes for Statistical Use maintained by the United Nations Statistics Divisions). Using codes saves time and avoids errors as instead of using a country's name (which will change depending on the language being used) we can use a combination of letters and/or numbers that are understood all over the world. For example, all national postal organizations throughout the world exchange international mail in containers identified with the relevant country code. Internet domain name systems use the codes to define top level domain names such as '.fr' for France, '.au' for Australia. In addition, in machine readable passports, the codes are used to determine the nationality of the user and when we send money from one bank to another the country codes are a way to identify where the bank is based. What is included in ISO 3166? ISO 3166 has three parts: codes for countries, codes for subdivisions and formerly used codes (codes that were once used to describe countries but are no longer in use). The *country codes* can be represented either as a two-letter code (alpha-2) which is recommended as the general purpose code, a three-letter code (alpha-3) which is more closely related to the country name and a three digit numeric code (numeric -3) which can be useful if you need to avoid using Latin script. . On Tue, Aug 12, 2014 at 1:14 PM, Jim Dettman <jimdettman at verizon.net> wrote: > <<why give a list of countries an ANPK when there already exists a > well-defined > ISO list of countries, each having a unique char code>> > > Someone on my side of the fence for a change<g> > > Jim. > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller > Sent: Tuesday, August 12, 2014 11:39 AM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Field linked to different tables > > David, > > I've had to skin a cat like this a few times in the past, and agree with > Gustav's suggestion. In fact, the experience has caused me, in several > situations, to re-think the whole knee-jerk AutoNumberPK thins that I once > depended on to the exclusion of logic and common sense. (For example, why > give a list of countries an ANPK when there already exists a well-defined > ISO list of countries, each having a unique char code?) > > Arthur > > > On Mon, Aug 11, 2014 at 8:24 PM, David Emerson <newsgrps at dalyn.co.nz> > wrote: > > > Thanks for the reply Stuart. This will still have the problem in the > > combobox that it may not show the correct selected record if the ID's > from > > the various tables in the first column double up. > > > > Gustav suggested making a composite key that combines the "WhoAboutType" > > and > > source table ID's so that there will not be any replications. This will > > solve the problem. > > > > Regards > > > > David Emerson > > Dalyn Software Ltd > > Wellington, New Zealand > > > > > > > > -----Original Message----- > > From: accessd-bounces at databaseadvisors.com > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart > > McLachlan > > Sent: Tuesday, 12 August 2014 12:06 p.m. > > To: Access Developers discussion and problem solving > > Subject: Re: [AccessD] Field linked to different tables > > > > How about another field in the Notes table "WhoAboutType" (1 for Client, > 2 > > for Staff, 3 for Company or whatever) > > > > Then > > Select 1 as WAType, ClientPK as PK, CLientName as EntityName frm > tblClients > > UNION Select 2 as WAType, StaffPK as PK, StaffName as EntityName from > > tblStaff UNION Select 3 as WAType, CompanyPK as PK, CoName as EntityName > > from tblCompany. > > > > > > Then Join on (WAType = WhoAboutType and PK = LinkRecordID) > > > > > > -- > > Stuart > > > > On 11 Aug 2014 at 15:23, David Emerson wrote: > > > > > Hi Listers, > > > > > > > > > > > > I am looking for some data structure advice. I will use a simple > > > example but the real world application will be more complicated. > > > > > > > > > > > > I have a Notes table. The Notes table fields include WhoAbout, > > > LinkRecordID. > > > > > > > > > > > > WhoAbout could be Client, Staff member, or Company. > > > > > > LinkRecordID is the ID from the Client, Staff member, or Company table > > > depending on what was selected in the WhoAbout field. > > > > > > > > > > > > The records from the Notes table will be displayed in a continuous > > > form showing WhoAbout and the name from the corresponding table for > > > the record selected in LinkRecordID. > > > > > > > > > > > > The Linkrecord field on the form is a combobox with a union of the > > > records from the three tables. Data from all three tables are needed > > > because it is a continuous form and I don't want to change the > > > recordsource for each current row as this would show incorrect data in > > > other rows. > > > > > > > > > > > > Immediate problem: Even though I could put code in place to ensure > > > that the record selected is of the same type as the WhoAbout field the > > > correct row may not show if it has the same ID as another record from > > > one of the other tables. > > > > > > > > > > > > Question: Has anyone else come across this problem and what might be > > > the solution? > > > > > > > > > > > > Regards > > > > > > David Emerson > > > Dalyn Software Ltd > > > Wellington, New Zealand > > > > > > > > > > > > > > > > > > -- > > > AccessD mailing list > > > AccessD at databaseadvisors.com > > > http://databaseadvisors.com/mailman/listinfo/accessd > > > Website: http://www.databaseadvisors.com > > > > > > > > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > > > -- > Arthur > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com