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!

 

[Guus2005]

use the wizard to create a crosstab query. It will turn your query 90 degrees.

HTH

 

[mcalex]

Sounds like you've already got crosstab'd data.

If your column list isn't too long, excel might be the quickest way to get your data into a 1 table, 2 column format. Export your current table to an excel spreadsheet and sort it so the most replaced part (the one with the most alternates) is top of the list. Copy the contents of Col A:Col B into a new spreadsheet, then copy the contents of Col A:Col C into the same columns in the new sheet below the data put there before.

Then do the same with Col A:Col D, Col A:Col E, etc

The end result will be a two column spreadsheet that you can import into a table in Access, and should look like your example, but backwards, ie:
Part A <- Part B
Part A <- Part C
Part A <- Part D

hth
mcalex