One table pointing to many tables

[bayouc]

Hello all,

I've got a mental block on this one - and could use someone's help knocking it loose...

I have a rather unusual application - but to simplify it: the user of the DB needs to be able to generate a "troubleticket" on any ID number (autonumber), from any field, on any table. Every one of my tables has an autonumber ID. So those three pieces of information (table, field, ID#) - effectively point to any piece of data in the whole db.

My "troubleticket" is also a table - it includes information about the problem and screen grabs etc... and also the table name, field name, and ID number which generated the ticket.

The problem is in reviewing the troubletickets. I'd like it to have a relationship back to the underlying data... because I need to run queries on the troubletickets that use information from the source tables.

Thinking about coding this gave me a headache - and made me think I have some major design flaw and am going about this in the wrong way.

Also of note: multiple troubletickets could be generated for any table,field,ID combo.

Any ideas?

 

[Galaxiom]

Sounds like a structural problem.
Please explain why you have the information separated into different tables and fields?

 

[bayouc]

The DB tracks a workflow. Different tables are different processes, and fields the steps of those process. The DB essentially tracks what has been completed, by whom, and when.

For example:

Table A is equipment maintenance... the fields are the steps required for maintenance.

Table B is instrument data analysis... the fields are the steps used to analyze instrument data.

Now - on any step, on any item, in any table - some problem can arise. A common "troubleticket" is generated, regardless of where it came from.

Table C is the troubleticket - with fields for the description of the problem, time to close, who it is assigned to, etc. For simplicity - the trouble ticket is common regardless of process, because there are MANY process tables.

Table C also includes the table name, field, and ID# of the step that had the problem.

Troubletickets are reviewed and sorted via forms, and I want to see the underlying data from the row in the table that generated the ticket. I suppose I could just create a subform and set its recordsouce using an sql string comprised of the table name, field, and ID# --

But it just seems, like you said, that I have a structure problem. There is a clear relationship between the troubleticket table and all the process tables, but either I don't know how to define it, or I can't define it in this structure because of a design flaw.

 

[Galaxiom]

All the fields are step in some process. It doesn't really matter that they have different names becuase you are more interested in tracking the process. You need to be able to link the trouble ticket records to one field in one table and this will allow you to do it.

Make a table Context
Fields ContextID, ContextName

This table stores the value that formerly allocated the process to a particular table.
It will contain two records:

1 | Equipment Maintenance
2 | Data Analysis

Make a table of Processes.
Fields: ProcessID, ProcessName, ContextID

Enter all the possible processes. ContextID is a foreign key to the record in the Context table showing where they apply.

The main table fields would include:
StepID (PK), EquipmentID, ProcessID, ProcessState, ProcessDate etc

The record source for a subform on the associated form uses joins between these tables to include ContextID and ProcessID and ProcessName.

The form also has an unbound combo where the user selects the Context. The Where clause of the subform record source uses this combo to pick ony records assocatied with the selected context.

Similarly the Equipment is selected by an unbound combo.

Delete all the labels from the textboxes on the subform and substitute textboxes bound to the ProcessName from the record source.

Make the subform a Continous form and keep the controlls close together.

When you choose the Context and Equipment the subform will show the records associated with the Equipment against the approprate label that changes as appropriate for that equipment.

I posted some information about controlling the form layout in Post 28 on this page.
http://www.access-programmers.co.uk/forums/showthread.php?t=187076&page=2