Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Database Design / December 2004

Tip: Looking for answers? Try searching our database.

linking two tables from one field

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Rick Bailey - 03 Dec 2004 00:45 GMT
I have a person table and a business table that I (think I) would like to
keep separate. But I want to able to create an invoice (or similar) that has
a customer field that can point to either a person or a business.
This is just one example of this problem, so I'm looking for a general
solution. How have other people (or businesses :)) done this?
Allen Browne - 03 Dec 2004 04:16 GMT
Rick, I strongly suggest creating one table to hold both.

For the simple example that you gave, it's very easy to use an IsCorporate
field (yes/no) to distinguish them. The MainName field contains the surname
for personal records (IsCorporate contains No), and the company name for
businesses (IsCorporate contains Yes). There are some fields that apply only
to personal clients (FirstName, BirthDate, ...).

For the interface, you can use 2 different forms so that it looks like two
different "tables" to the end user. For personal clients, the form's
RecordSource will be:
   SELECT * FROM tblClient WHERE IsCorporate = False;
But because they are all in the one table, you don't have any of the
problems of trying to UNION different tables.

If your actual case is more complex where there are many divergent fields
between the two or more types, I would still encourage you to put the common
fields into one table, and then put the various divergent fields into
further tables, related 1-to-1 to the common table. However, you should
first try to get around this by creating a related table to hold these
various extra attributes - fields like:
   ClientID            foreign key to the common table.
   AttribTypeID    select from the kinds of things that could apply.
   AttribValue       the actual value for this attribute that applies to
this client.

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have a person table and a business table that I (think I) would like to
> keep separate. But I want to able to create an invoice (or similar) that
> has
> a customer field that can point to either a person or a business.
> This is just one example of this problem, so I'm looking for a general
> solution. How have other people (or businesses :)) done this?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.