Clients - internal and external, how design and process?

[mscertified]

My application must cater for internal and external clients. Internal clients
are employees who are maintained in an existing table which is updated by
importing from another database (which I have no control over). External
clients are people outside the company. The information to be kept will be
partly the same (name/address/phone no) but partly unique for internal or
external clients.
However, I will need treat these two types of client as a single entity in
many queries and forms. What is the best way to set this up? I'm leaning
toward keeping the data in two separate tables but having a query that draws
both together. However, this query will not have any common column to
uniquely identify the client and I can see that being a problem.

 

[Gina Whipp]

MSCeritfied,

Here's my two cents worth... Why not create a custom autonumbering field
that no one sees but you. IF you have a way to determine which ones are
external/internal, assign the external as odd numbers and the internal as
even numbers. This you have one table to create queries from and you have a
way to uniquely identify them.

 

[Pat Hartman \(MVP\)]

Use a single table to manage both types of people. Add a column to use to
identify which rows are internal and which are external. If you have more
than a few unique fields for each type of person, you can create two
additional tables which are related 1-1 with the shared table. These tables
will be sparse. That means that they will only have matching rows when data
is actually entered. To work with these tables you would use left joins so
that you get all selected rows from the shared table and any matching rows
from the separate table. You won't need data from both separate tables at
one time (if you do, they shouldn't be separate tables) so you can create
separate queries for internal and external people for use when you need the
extra fields.

 

[Pat Hartman \(MVP\)]

Gina,
Using even/odd numbers is a creative solution but inappropriate. Embedding
"hidden" information in a field is not good design practice as well as being
a violation of first normal form (all fields should be atomic). A better
solution is to create a new column that can be used to distinguish the two
different types of people. The new column is not limited to two choices,
should a third type of person be identified as the design progresses.
Pat

 

[Gina Whipp]

Pat,

Perhaps I didn't expalin that clearly enough... I was not referring to
embedding the information but creating a seprate field/cloumn, mine would
just be odd/even numbers being created without the users knowledge. (I
always use custom automubering because the end-user has no use for the
number. Only I need it for design purposes.) However, you make a good
point about not limiting the choice to 2, so perhaps letters or some other
type of identifier is more appropiate.

Thanks for the feed back!
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

 

[BruceM]

Whether an odd/even number or a letter, how would you assign it? That is,
how would Access "know" if the client is internal or external?

 

[Gina Whipp]

I made and assumption (I know, I know) that one would know who was internal,
external based on this statement, "Internal clients are employees who are
maintained in an existing table which is updated by
importing from another database (which I have no control over)."

 

[mscertified]

Thanks for the response. I like your solution. However, there is one problem.
When the data for internal clients is imported the entire table is replaced.
If I have external clients intermixed in this table, it will make the
importing process much more complex.

 

[Pat Hartman \(MVP\)]

There are always reasons for exceptions to normalization rules and this is a
reasonable one. I hope you are not updating this table locally. That would
cause conflicts.

Is the ID of the employee records consistent? If it is, you can use an
update query each time you get a new copy rather than actually replacing the
data. If you use a right join, the update query will add new records and
update existing records - it is a magical thing That will allow you to
use a single table which will ultimately cause you less trouble.

 

[Armen Stein]

On Fri, 31 Aug 2007 11:18:50 -0400, "Pat Hartman \(MVP\)" <please no

There are always reasons for exceptions to normalization rules and this is a
reasonable one. I hope you are not updating this table locally. That would
cause conflicts.

Is the ID of the employee records consistent? If it is, you can use an
update query each time you get a new copy rather than actually replacing the
data. If you use a right join, the update query will add new records and
update existing records - it is a magical thing That will allow you to
use a single table which will ultimately cause you less trouble.

Another idea:

Instead of importing into the main table, you can import into a work
table (after deleting all records). Then you can used queries to add
or update records in your main table.

We sometimes call these work tables "quarantine tables", because you
can bring the records into a safe place before moving them into your
main tables. Another advantage is that if something goes wrong during
your import because of unexpected data values or formats, only the
work tables are affected, not the main tables.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com