How to take a chain of data, convert it into rows (vertical instead of horizontal)

[jershie]

I'm having a problem with a database that manages part's supersession (supersession = a list of the current parts followed by the "chain" of old parts that have been replaced either because of quality reasons or because a better alternative was available) history for our company.



What I'm trying to do is this:

1) Right now I have a chain of parts history:

Example (|=column):

Part A (newest part) | Part B | Part C | Part D

2) I want to turn this into a query or table that looks like this:

Part B -> Part A
Part C -> Part A
Part D -> Part A

*In other words, I want to build a table that has two columns:

Part Number | Alternate (OR Replaced By Part Number)

CONCLUSION: I need to build this table so I can link it to my main queries that will make sure that when the customer tries to purchase any part (Part B, C, or D), that it will tell them they can only buy Part A (I don't want to know what the next part in the chain is, I want to know what the most current part in the chain is).

Thanks in advance for any assistance you might be able to provide!

[TheShabz]

Just how many parts are we talking about? Hopefully its not too many because your table structure is all wrong. It's being treated more like an excel spreadsheet than it is a database table.

What you would want to have is a table with ALL of your parts listed on it, with descriptions as field names as well as the following fields
isObsolete (a yes/no field that lets you know if it has a new part)
currentPartNum (the most current part used for it)

You are on the right track in trying to fix the system you already have, but if your table isn't set up somewhat like how I mentioned, you'd simply be band-aiding it instead of fixing the issue. Hopefully there aren't TOO many parts and you can re-enter them into a properly structured table (or pass the buck to a min-wage data entry =P)