[AccessD] Field linked to different tables

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