Why can't I have a lookup to the same table?

[Martin Holmes]

Hi all,

I have a table used for storing linguistic errors made by a speaker.
Some of these errors are repeats of previous errors the same speaker has
made. I'd like to be able to encode this by having a field in the table
which is a lookup to the same table -- in other words, Error # 357 is a
repeat of Error # 350. Ideally, this field would show a dropdown list of
error numbers from the same table.

However, Access won't let me create a lookup field pointing at the table
which contains it. Why not? How could this be problematic? Does anyone
know how I could get around it?

All help appreciated,
Martin

 

[Steve Schapel]

Martin,

As a matter of principle, you should avoid lookup fields.

However, you can make a query based on the ErrorNumber field of your
table, and use this as the rowsource of the combobox on your form.

- Steve Schapel, Microsoft Access MVP

 

[Kelvin Lu]

You can't make the lookup in a table look at itself. You can in a form.
Create a form for your table. You can then have the lookup linked to the
same table.

Kelvin

 

[Martin Holmes]

Hi there,

Why avoid lookup fields? I'm using them so that when I have multiple
fields in various tables that need one of the same set of choices, I can
centralize that set of choices to one table that can be added to where
necessary. For example, I have a table of "Speakers", consisting of
Teacher, Student, and Other Student. This set of choices is required in
many different tables; I use lookups so that if it's necessary to add
(say) Other Teacher at some stage, I can do it in one table and not have
to edit lots of fields. Lookups seem ideal for this. Would you suggest
that I enter the same data choices in multiple locations?

Cheers,
Martin

 

[Martin Holmes]

Many thanks -- I haven't started the UI for the db yet, so I hadn't got
as far as seeing whether this would work using a form.

Cheers,
Martin

 

[Ben]

I think Steve not suggesting you avoid the *concept* or lookup fields, just
the Access Automagic implementation, which can be problematic. Create your
lookup tables and define foreign key relationships explicitly, rather than
use the lookup datatype.
HTH
Ben

 

[Duane Hookom]

Abandon all attempts to create lookup fields in tables in favor of using
combo boxes on forms. You might want to read
http://www.mvps.org/access/lookupfields.htm.

 

[Tim Ferguson]

Error # 357 is a
repeat of Error # 350. Ideally, this field would show a dropdown list of
error numbers from the same table.

Everything that everyone else has said is true, but I don't think this is
going to solve your problem anyway. You can certainly set up FK field that
points to the PK of the same table, but this is usually to implement some
method of inheritance or recursion: for example Employee.ManagerID points
to the Employee.EmployeeID of the person's manager.

I think you are really describing a straightforward many-to-many
relationship, where each person makes several errors. You may need three
tables like this:

Person ---< Makes >--- Errors

or even four of them:

Person --< Speeches --< Contains >-- Errors

so that the Contains table looks like this:

SpeechNumber ErrorCode NumberOfTimes Corrected
============ ========= ------------- ---------
102834 37 3 Yes
102834 49 1 No
102993 37 1 No


Of course, you have not given much information about your situation, so I
may well be miles off

All the best


Tim F

 

[Martin Holmes]

Hi there,

Everyone's suggestions have been helpful, and I think I'll look at using
combo boxes in forms rather than the wizard lookup thing. That's easy
enough. The "recursion" situation is actually a bit different, in that
Student, Teacher, Other Student etc. are not individuals with IDs;
they're just generic identifiers that would not necessarily refer to the
same person in two different exchanges. The identities of people don't
matter, just their role in a given exchange. The base item is the
utterance, and each utterance just needs to be labelled as being spoken
by (someone who in that exchange happens to be) Teacher, Student or
Other Student.

Thanks to all for your help!
Martin