[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