Relationship Help - Incident and Problem Database

[Moon]

Okay, time for me to ask you experts.

I have a make shift Incident/Problem management database.

The main table holds unique reference ID's for each "Defect". A
defect has a type field which can be a Problem or an Incident.

I simply want to link many Incidents to 1 Problem, due to how I
operate the database, I cannot separate the problems to another
table.

Any help would be gratefully received.

Regards
Alan

 

[John Spencer]

Can you add a field to your table?
Field: ProblemSource (or whatever) - Same data type as the ReferenceID

IF you do that you can have a self-referencing table and populate the new
field with the value of the "Master" problem in each defect.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Moon]

Hi John, thanks for your note.

I have now read up and created a self-referencing table however i
don't know if this will do what I need.

I would like to create a query to show all incidents to each problem

Problem1
-Incident 1
-Incident 2
Problem 2
....etc

My other thought is a full redesign of the database. I could split
the incidents and problems into 2 identical tables...just wanted to
avoid duplication.

Thanks in advance

 

[John Spencer]

Need more details on your actual structure to give you really specific advice

The SQL of a query might look like:

SELECT Problems.*, Incidents.*
FROM MainTable as Problems LEFT JOIN MainTable as Incidents
ON Problems.ReferenceID = Incidents.ProblemSource
WHERE Problems.Type = "Problem"


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County