Heenan, Lambert
Lambert.Heenan at aig.com
Tue Aug 12 16:01:02 CDT 2014
Perhaps because standards are designed by people and people sometime change their minds? For instance there is a standard out there: ICD9 which is a system of codifying medical diagnosis. But now "they" have come up with ICD10 to replace it, and just for fun there is "No Clear Mapping Between ICD-9-CM and ICD-10-CM Code Sets" :-) Lambert -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman Sent: Tuesday, August 12, 2014 1:14 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Field linked to different tables <<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