[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