Change ID to Name in Query

[Rabastan99]

I have have a that shows hierarchic data.




stepID
parentID
stepName


The Parent ID is the step that comes before it in the list, it however is stored as a number. I need to export this data to excel, how would I create a query that will export it with the name and not the ID?


Rab

[June7]

Don't understand this data structure. Provide example of data. If you want to provide db for analysis, follow instructions at bottom of my post.

[Rabastan99]

OK No problem. The database is to map steps in trouble shooting processes. The eventual out come is to print them out in a flow chart.

DataExample1.zip

[June7]

I still don't understand "Parent ID is the step that comes before it in the list".

I don't see a ParentID field, just stepParentID.

Where should the 'name' come from?

Perhaps example of desired output from the example data would help.

Consider:

Query1 - ParentIDs
SELECT tblProcessStep.stepID, [stepID]-1 AS ParentID FROM tblProcessStep;

Query2 - ParentNames
SELECT ParentIDs.stepID, ParentIDs.ParentID, tblProcessStep.stepName AS ParentName
FROM tblProcessStep INNER JOIN ParentIDs ON tblProcessStep.stepID = ParentIDs.ParentID;

Query3
SELECT tblProcessStep.stepID, tblProcessStep.ProcedureID, tblProcessStep.stepParentID, tblProcessStep.stepName, ParentNames.ParentName
FROM tblProcessStep LEFT JOIN ParentNames ON tblProcessStep.stepID = ParentNames.stepID;

This presumes the unique stepID field is sequential with no gaps.

[Rabastan99]

It is quite possible you don't understand because I blew the entire design from the beginning.

The database will store about 40-50 flowcharts for various troubleshooting procedures.

All the steps are in the same table, with the ID of the step that comes before it named "ParentID". So the "ParentID" is the "stepID" of the step that came before it.

[June7]

You may have read my previous post before I edited. Might look at again.

[Rabastan99]

The data is not sequential by any means

[June7]

My suggestion requires a field or combination of fields that can be used to sort the records into proper order and a field or fields that can be used to calculate the unique ParentID. I used the unique stepID field for both. Requires that the stepID be sequential without gaps.

If the stepID does not sort records into proper hierarchical order and/or has gaps, you have a big problem.

The suggested query will only go the one level. Surely the decision process would be many levels. I've never seen a Access database for this purpose. It's probably not an easy database to construct.

As for printing into a 'flow chart', have doubt Access is best tool for this. Might look at MS Visio. It can pull data from Access. Or something like Visio: http://www.smartdraw.com/specials/ppc/flow-chart.htm?id=45104&gclid=CL3m1tCcv8ECFQaNaQodCRAAj Q