[AccessD] A2003: Design Question

Darren,

I've done something similar in the past...not sure if this will 
help...but...if you are using a union, your union knows where it is getting 
the data.  Why not create a field(Entity) in the union that differentiates 
the data.  Here is an example.

SELECT tblClient.PK, "Client" AS Entity, tblClient.Data, tblClient.MoreData
FROM tblClient
ORDER BY tblClient.PK, "Client"
WITH OWNERACCESS OPTION;

UNION SELECT tblFranchise.PK,"Franchise" AS Entity, tblFranchise.Data, 
tblFranchise.MoreData
FROM tblFranchise
WITH OWNERACCESS OPTION;

Hope this helps.

Mark A. Matte

>From: "Darren DICK" <d.dick at uws.edu.au>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: "'Access Developers discussion and problem 
>solving'"<accessd at databaseadvisors.com>
>Subject: RE: [AccessD] A2003: Design Question
>Date: Tue, 16 Nov 2004 13:24:55 +1100
>
>Hi Stuart
>Thanks for the reply
>In your suggestion I would create a 3rd table called tblEntity - yes?
>How does it get populated?
>
>How would I store entries to the tblTimeBilling table.
>IE in the union combo how would I store that PK 4 in this case is a
>franchise
>Not a client?
>
>Many thanks
>
>Darren
>
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
>Sent: Tuesday, 16 November 2004 12:25 PM
>To: Access Developers discussion and problemsolving
>Subject: Re: [AccessD] A2003: Design Question
>
>On 16 Nov 2004 at 12:03, Darren DICK wrote:
>
> >
> > BUT my client does work for both the Franchises and individual 
>Customers.
> > In order to present both of them in a TimeBilling Screen I use a union
> > query to 'merge' both groups into a single combo
> >
> > The problem is storing the PK correctly. eg say I select the 4th
> > Franchise in the list - it has a PK of 4 Well an entry on the client
> > side will also have a PK of 4.
> >
> > I am tossing up with various ways to deal with this including merging
> > both tables into one table and just setting a flag to differentiate
> > Clients from Franchises.
> > Of course I can have separate fields in the tblTimeBilling for
> > FranchiseID and for ClientID.
> >
>
>I'd probably go for a tblEntity with 3 fields:
>
>ID
>EntityType (Franchise or Client)
>EntityID
>
>You can then base your ComboBox on a query something like:
>
>Select ID , IIF(EntityType = "F", FranchiseName,ClientName) as EntityName
> >From tblEntities Left  Join tblFranchises ....... Left Join TblClients
>  ......
>
>
>
>
>--
>Stuart
>
>
>--
>_______________________________________________
>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