Very daft question

S

scubadiver

I am trying to create a query using two different tables. I have established
a relationship for employee name and put all the relevant columns in the
query.

I can't view the fields I have put in the form already although I am using
the query as the source. Whats up?

cheers
 
B

BruceM

I'm not clear on what you mean by "the fields I have put in the form
already", but if you mean that you created the form using something other
than your new query as the form's record source, you will need to change the
record source to the query.
 
J

John Vinson

I am trying to create a query using two different tables. I have established
a relationship for employee name and put all the relevant columns in the
query.
Click to expand...

If you're joining the two tables on the employee's name - reconsider!
I once worked with Dr. Lawrence David Wise, Ph.D., and his colleague,
Dr. Lawrence David Wise, Ph.D. Linking fields should be unique,
stable, and short; people's names fail on all three counts. Consider
using a unique EmployeeID instead.
I can't view the fields I have put in the form already although I am using
the query as the source. Whats up?
Click to expand...

I suspect there just aren't any records in the second table. By
default you'll only see records where there is already data in both
tables. Consider using a Form (for the "one" table) with a Subform
(for the "many") instead.

John W. Vinson[MVP]
 
T

Tom Ellison

Dear John:

The lack of a unique natural key is an interesting topic. I would argue
that this actually DOESN'T HAPPEN.

If the two men work in adjacent offices, or in any context where they are
otherwise difficult to distinguish, wouldn't ordinary humans solve the
problem in some natural way? Would they not call one of them:

Dr. Lawrence David Wise, Ph.D.

and the other:

Dr. Lawrence D. Wise, Ph.D.

That is to say, would there not be some convention that would be used to
distinguish the two men, a convention that would catch on more or less
universally? It is my opinion that people will solve a problem like this.
If so, then why cannot a database take advantage of this mechanism?

I instruct my users to decide on a convention that makes the natural key
unique. Maybe Dr. Lawrence David Wise, Ph.D. has been at the university for
5 years when the new one comes along. Could you not call the second one
"Dr. Lawrence David Wise, Ph.D. New". That makes a unique natural key that
is intuitively functional. You will probably need a separate column to show
his name as would be used in correspondence. The natural key column can be
something not otherwise functional. Indeed, I would make the users create a
short natural key, almost never more than 20 characters. These might be:

Dr. L. D. Wise
Dr. L. D. Wise 2

Where "2" is the second one.

In a combo box, I don't think you want a lot of extremely long names.
Indeed, they should be entered so the user types only 2 or 3 letters to get
the desired row on the screen. Likely it would be:

Wise, Dr LD
Wise, Dr LD 2

This is what makes a combo box work well (my opinion). Of course, users
must know somehow which one of these two they want! That's a problem not
solved at all with an autonumber / identity. From a technical standpoint,
adding an EmployeeID makes the two persons with the same name unique.
However, it does not make then "distinguishable" unless you expect users to
memorize the ID numbers. When a user needs to add a row to some dependent
table and make sure it is related to the correct Dr. Wise. How will they do
this?

I suggest the only workable solution is to make sure you create a unique
natural key before the problem gets any worse than it alread is. Whether
you choose to use identity keys, having a unique natural key is, in the
final analysis, completely inescapable. Indeed, if people are to have
conversations in which they refer to Dr Wise, and they wish to be
unambiguous, then some convention will be created. The computer's need for
uniqueness should be a reflection of whatever convention is adopted in
natural conversation, or if there isn't one, the need for one in the
computer should be the incentive for the invention of a unique natural key
that can be adopted both in the system and in conversation.

Thanks for listening. This is a real and very important problem, and not
just one for those who prefer using unique natural keys for relationships in
the database. Unique natural keys are essential whether you add autonumber
keys or not. Or, does your combo box say:

Dr. Lawrence David Wise, Ph.D. 117203
Dr. Lawrence David Wise, Ph.D. 242391

Do you expect users to be able to pick the correct instance from the above?

If you have some other alternative, I'd be very glad to hear of it. I'm
studying this topic in depth just now.

Tom Ellison
 
J

John Vinson

Dear John:

The lack of a unique natural key is an interesting topic. I would argue
that this actually DOESN'T HAPPEN.

If the two men work in adjacent offices, or in any context where they are
otherwise difficult to distinguish, wouldn't ordinary humans solve the
problem in some natural way? Would they not call one of them:
Click to expand...

One (Larry) was a tall, blond, affable chemist; the other (L. David)
was a stocky, dark, taciturn biologist.
Dr. Lawrence David Wise, Ph.D.

and the other:

Dr. Lawrence D. Wise, Ph.D.
Click to expand...

But suppose that they did not WANT their name to be changed for the
convenience of a limited-intelligence computer program?
That is to say, would there not be some convention that would be used to
distinguish the two men, a convention that would catch on more or less
universally? It is my opinion that people will solve a problem like this.
If so, then why cannot a database take advantage of this mechanism?
Click to expand...

I'm sure that Parke-Davis' personnel database used their Social
Security number as a unique arbitrary key.
I instruct my users to decide on a convention that makes the natural key
unique. Maybe Dr. Lawrence David Wise, Ph.D. has been at the university for
5 years when the new one comes along. Could you not call the second one
"Dr. Lawrence David Wise, Ph.D. New". That makes a unique natural key that
is intuitively functional. You will probably need a separate column to show
his name as would be used in correspondence. The natural key column can be
something not otherwise functional. Indeed, I would make the users create a
short natural key, almost never more than 20 characters. These might be:

Dr. L. D. Wise
Dr. L. D. Wise 2

Where "2" is the second one.
Click to expand...

And there of course you need some additional information to determine
which is who. Unless you KNEW that 2 was the biologist David and blank
was the chemist Larry, this text would be anything *but* helpful!

In a combo box, I don't think you want a lot of extremely long names.
Indeed, they should be entered so the user types only 2 or 3 letters to get
the desired row on the screen. Likely it would be:

Wise, Dr LD
Wise, Dr LD 2

This is what makes a combo box work well (my opinion). Of course, users
must know somehow which one of these two they want! That's a problem not
solved at all with an autonumber / identity. From a technical standpoint,
adding an EmployeeID makes the two persons with the same name unique.
However, it does not make then "distinguishable" unless you expect users to
memorize the ID numbers. When a user needs to add a row to some dependent
table and make sure it is related to the correct Dr. Wise. How will they do
this?

I suggest the only workable solution is to make sure you create a unique
natural key before the problem gets any worse than it alread is. Whether
you choose to use identity keys, having a unique natural key is, in the
final analysis, completely inescapable. Indeed, if people are to have
conversations in which they refer to Dr Wise, and they wish to be
unambiguous, then some convention will be created. The computer's need for
uniqueness should be a reflection of whatever convention is adopted in
natural conversation, or if there isn't one, the need for one in the
computer should be the incentive for the invention of a unique natural key
that can be adopted both in the system and in conversation.

Thanks for listening. This is a real and very important problem, and not
just one for those who prefer using unique natural keys for relationships in
the database. Unique natural keys are essential whether you add autonumber
keys or not. Or, does your combo box say:

Dr. Lawrence David Wise, Ph.D. 117203
Dr. Lawrence David Wise, Ph.D. 242391

Do you expect users to be able to pick the correct instance from the above?

If you have some other alternative, I'd be very glad to hear of it. I'm
studying this topic in depth just now.
Click to expand...

It's a difficult issue, I agree - and (as in real life) any solution
is likely to be imperfect!

John W. Vinson[MVP]
 
T

Tom Ellison

Dear John:

What do you mean:

"But suppose that they did not WANT their name to be changed for the
convenience of a limited-intelligence computer program?"

Creating a natural key column for the internal use of the computer in
creating combo box lists, so these lists can have unique, recognizable
forms, does not amount to "changing their name". It amounts to having a
representation of their name that is just that, recognizable and unique. I
specifically propose to continue storing and using the formal version of
their names, so nothing they will ever see is changed whatsoever.

What it amounts to is this. If these two men worked in adjacent offices, I
truly believe that others in the office would quickly adopt a way of
referring to them distinctly and unambiguously. This is natural to
conversational language. So, why can't the computer do the same thing? Of
course, it can, and should.

That's what I mean when I say that there is not a lack in unique natural
keys. In speech and writing, humans will create a unique natural key and
make a convention of it.

I had a neighbor with the same first name as mine. Soon, everyone was
calling me Tom and him Tommy. Everyone knew who was meant by each. That's
a convention, it's natural, and happens all the time. People don't usually
prefer a formal distinction. They didn't start using both our last names,
but just consistently used variations of our first names to eliminate
ambiguity.

For your example, the natural key could be:

Wise, Dr LD bio
Wise, Dr LD chem

This might reflect the way the two were uniquely identified
conversationally. I was not prescribing the "2" as the only way they could
be distinguished. It's just that, in the absense of knowledge as to what
differences there were between these two, I just invented something.

In creating unique natural keys, there need not be just one method for
distinguishing one entity from another. It can be "bio" and "chem" one time
and "tall" and "short" the next. It can be "tall bio", "short bio" and
"chem" the next time, using a combination of attributes when necessary to
make them unique.

I do not present this as a perfect solution. I'm not sure there is one. I
present it as a workable solution, one that is practical and intuitive.
That's about the best for which we can hope.

I'm not arguing for natural key relationships here, although this is not
unrelated. I'm arguing that there must be a unique natural key if there is
to be a dependent table. If the natural key is not unique, and there is a
dependent table one-to-many, how will users be able to consistently and
reliably choose one of these two men to record awards they have received?
The two are indistinguishable until an award is assigned. Likely, after an
award is assigned, they are then unique on that basis. When assigning a new
award to one of them, the question arises, to which of them is this award to
be recorded? The one who already has an award, or the other one with no
award? Will the user posting that know the answer? It certainly matters to
which FK the dependent rows are posted. This posting, if done correctly,
likely does make the two unique, but not in a way that is likely to be
apparent to the person posting.

If there is no unique natural key, if there are two rows in the combo box
that are indistinguishable, how do you solve this? Since you do not seem to
agree with my proposed solution, what do you propose? Am I missing
something here?

Tom Ellison
 
Top