[AccessD] Field linked to different tables

Stuart McLachlan stuart at lexacorp.com.pg
Tue Aug 12 16:48:20 CDT 2014


Because indexing on a Long is more efficient than indexing on a character string?

(It's computationally much faster and uses far less storage).

-- 
Stuart


On 12 Aug 2014 at 13:14, Jim Dettman 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
> 




More information about the AccessD mailing list