Looking up a field in the same table

[JamieNC]

Probably being really thick here, but here goes:

I'm trying to create a field that looks up the key field of the table that it is in (ie so that the table references itself). However, when I work my way through the lookup wizard in the table design view, I am only invited to select fields from other tables in my database.

Does anybody know how I can look up fields from the same table?

Thanks in advance for any help you can offer,

Jamie

 

[hneal98]

Why do you need this?

 

[JamieNC]

I have created a database that holds information about all of the PCs in our office. We filter these PCs down through our employees so that a PC belonging to a support analyst one year will be given to an office administrator the next, etc. I want to include a facility in the database that shows which PC the a PC will be replaced by the following year. In order to do so I need to be able to lookup the PCs that are currently in circulation.

Any thoughts?

 

[JamieNC]

Managed to work it out, so here's the solution for anyone who is interested:

In table design view set the Data Type of the relevant field to Lookup Wizard and start working your way through the wizard when the window appears.

Select to lookup values in a table or query. As I say in my post above, the wizard does not give you the option of selecting the table that you are currently in. Therefore, for the time being, select any table in the list you are presented with (NB if your database only contains 1 table you will need to create one temporarily for this purpose). Select any field from the list of fields in the table you selected and complete the wizard.

Now, making sure that the field you are working on is still selected, click on the Lookup tab below the list of fields. In the Row Source entry you should see a SQL statement describing the lookup you just created. Overwrite this statement so that it reads as follows:

SELECT TableName.FieldName FROM TableName;

where TableName is the name of the table you wish to look up and FieldName is the name of the field within TableName that you wish to look up.

The field should now look up and be connected to the field that you have selected, regardless of whether it is part of the same table or not.