Sorting un-normalised data... (1 Viewer)

[The John Rambo]

Hi,

I created a table of stock codes each with multiple subcomponents, each subcomponent (1 to 20) as different new field:
Field 1: Stock_Code
Field 2: Sub1
Field 3: Sub2
Field 4: Sub3
Field 5: Sub4
etc.

As far as I've picked up this table isn't normalised.
I would like to create a second table with only 2 fields i.e.
Field 1: Stock Code (I'll use a form to select from the original Table)
Field 2: Subcomponent (I'll use a form to select from a Sub Table).

Please see the attachment for more details.

There are about 1500 records with multiple subcomponents so it is quite cumbersome to manually sort the data in Excel (the next best thing that I know of besides Access)
Is there a quicker way than to manually sort the data?

Thanks

 

[plog]

The easiest way to accomplish that is by creating 20 queries 1 for each 'Sub' field. First, create your new table, next create an Append query that pulls in Field1 data and appends it to the new table:

INSERT INTO ToThisTable (Stock_Code, Sub_Comp)
SELECT [FromThisTable].[Stock_Code], [FromThisTable].[Sub1]
FROM FromThisTable;

After running it, change the [FromThisTable].[Sub1] to [FromThisTable].[Sub2] and so on. Consider this process as pennance for your sins against database design.

 

[The John Rambo]

Thanks plog!
Done! That saved me many hours of work
I also used another query to remove any duplicates that I had.

At the time I created that table I didnt know any better, I'll be sure not to do it again.

I have repented and seek to abolish my sinful ways along with the Excel demons that linger within.