Stuart McLachlan
stuart at lexacorp.com.pg
Mon Aug 11 19:06:03 CDT 2014
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 >