Darren DICK
d.dick at uws.edu.au
Wed Nov 17 16:12:29 CST 2004
Thanks Arthur and Stuart et al who replied I combined the tables and set a flag in each to dictate their Entity Type Simple solution solved everything <sigh> Many thanks See y'all Darren -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller Sent: Thursday, 18 November 2004 3:17 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] A2003: Design Question Arthur Fuller wrote: > Darren DICK wrote: > >> 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 >> >> >> >> > Is there a profitable reason to store these entities in different > tables? There may well be, but in your problem description it is not > apparent. If my hunch is correct, then you can put both Franchises and > Customers in the same table, adding a flag to indicate which. There is > the possibility however that an entity is both a Customer and a > Franchise, in which case a boolean won't do, so replace it with a > numeric. > > Up side: much simpler combo selectors. > Down side: selecting only one or the other type demands hitting the > whole table (unless the BE is SQL). > > HTH, > Arthur SELECT EntityID, EntityName, IIF(EntityType="C", "Customer","Franchise") AS Type FROM tblEntities ORDER BY EntityName You could ORDER BY EntityType first to gather them into two groups. -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com