Table does lookup in itself

[Bill Sturdevant]

I have a table of Projects. The primary fields are
Project_ID and Project_Name. I need to add a field called
Related_Project_ID with a dropdown that lists all of the
other Project_IDs except the one in the current record.

How can I do this?

When I set up a lookup to the table I am in, I get an
error that says the referenced table is already open.

 

[John Vinson]

I have a table of Projects. The primary fields are
Project_ID and Project_Name. I need to add a field called
Related_Project_ID with a dropdown that lists all of the
other Project_IDs except the one in the current record.

How can I do this?

When I set up a lookup to the table I am in, I get an
error that says the referenced table is already open.

Don't use Lookup Fields - I'd say NEVER; they are very limited and can
be confusing. In any case they aren't suitable for this task; if there
are more than just one related projects, you need another Table
instead, since you cannot store multiple values in a single field.

I'd suggest an "indirect many to many self join": create a table
RelatedProjects with fields ProjectID, RelatedProjectID, and perhaps
one or more other fields detailing the nature of the relationship
(e.g. "prerequisite").

Use a Form based on the projects table, with a Subform based on
RelatedProjects; it would be handy to use a Combo Box based on
Projects to enter the RelatedProjectID.

 

[Tim Ferguson]

I have a table of Projects. The primary fields are
Project_ID and Project_Name. I need to add a field called
Related_Project_ID with a dropdown that lists all of the
other Project_IDs except the one in the current record.

Not sure what you mean by "primary fields": for simplicity, can I assume
that ProjectID is the Primary Key and ProjectName is a text field?

You can certainly add another field called RelatedProjectID which will
connect to exactly one other project -- if you need to relate to more than
one project, then you need a different design. Note that you must NOT make
the field Required. Bear in mind too that RelatedProject is a rotten name
for the field: it would be much easier to describe what the relation is.
For example, ParentProject or DependsOn or HasToWaitFor or whatever.

You can create the relationship in the window in the normal way: you have
to Add Table twice for the Projects table, and the second one will be
called Projects_1 but don't worry about that because it is only for the
benefit of the relationships window itself. Drag the RelatedProjectID from
one table toward the ProjectID of the other one and complete the dialog.

You can use a form to display the link. Place a combo box or a list box on
the form, and set its ControlSource to the RelatedProjectID field. Set its
RowSource to a query that gets the correct values like this:

SELECT ProjectID, ProjectName
FROM Projects
WHERE ProjectID <> Forms!frmProjectsForm!cboRelatedProject.Value
ORDER BY ProjectName

Note that I have assumed that the control is called cboRelatedProject and
the form is called frmProjectsForm. To get the combo box to look right, set
the ColumnCount to 2, the BoundColumn to 1 (the ProjectID value) and the
ColumnWidths to "0;", which hides the first column (the ProjectID number,
which you do not want your users to see).

That should be all there is to it.


Best wishes


Tim F

 

[TC]

That would limit you to *one* related project per project. Surely there
could be more than one? If so:

tblRelatedProject
ProjectID ( composite )
RelatedProjectID ( primary key )

Then a project could be related to as many other project as you needed.

HTH,
TC