SQL statement to put similar values into one column, not a concatentate

[smg]

I have a form that allows the user to add three Error Precursors to an item like this:

ErrorPrecursor1	ErrorPrecursor2	ErrorPrecursor3
TD-7
TD-1	TD-3	WE-2
WE-3	TD-1	HN-1
HN-2
TD-7
WE-1
HN-2
HN-4	HN-5
IC-7
TD-7	TD-8
TD-7	HN-2
WE-4
TD-6
HN-4
HN-4
HN-4
HN-4
HN-4
TD-8
HN-5	HN-7
HN-4
HN-5

I need a query that will provide a list of all Errors like this in one column:

All Error Precursors

TD-7

TD-1

WE-3

HN-2

TD-7

WE-1

HN-2

HN-4

IC-7

TD-7

TD-7

WE-4

TD-6

HN-4

HN-4

HN-4

HN-4

HN-4

TD-8

HN-5

HN-4

HN-5

TD-3

TD-1

HN-5

TD-8

HN-2

HN-7

WE-2

HN-1

Please help with a sql statement to do this.

[orange]

smg,

When readers see fields with names like Field1, Field2, Field3.....FieldX, the immediate reaction is "probably poor table structure". It seems this data should be put into its own table along with some unique identifier. Whether or not this is the case here really depends on your application.
Can you give us an overview of what you are dealing with --in simple English - and how he pieces fit together in the business this database is intended to support?

Please show us the tables and relationships you have currently.
Good luck with your project.

[Peter M]

Agree with Orange that if you are still early in the design stage, replace with ErrorPrecursor, ErrorPrecursorCount.

BUT to answer your question, use a Union query.

Select {fields that are all the same}, ErrorPrecursor1 as ErrorPrecursor, 1 as ErrorPrecursorCount from tbl_Table
UNION
Select {fields that are all the same}, ErrorPrecursor2 as ErrorPrecursor, 2 as ErrorPrecursorCount from tbl_Table
UNION
Select {fields that are all the same}, ErrorPrecursor3 as ErrorPrecursor, 3 as ErrorPrecursorCount from tbl_Table;

Save that as TemproraryQuery and then run a Make table query to pull all the data into a single narrow table where ErrorPrecursor is not null.

[smg]

ok, so far so good. I used the following sql statement:
Select ErrorPrecursor1 as ErrorPrecursor from AssessmentDetail
UNION
Select ErrorPrecursor2 as ErrorPrecursor from AssessmentDetail
UNION
Select ErrorPrecursor3 as ErrorPrecursor from AssessmentDetail
and got the following result:

ErrorPrecursor
HN-1
HN-2
HN-4
HN-5
HN-7
IC-7
TD-1
TD-3
TD-6
TD-7
TD-8
WE-1
WE-2
WE-3
WE-4

now, I would like to get a count of each code, for instance, between the three ErrorPrecursor columns, HN-4 has a count of 7.
Thanks

[smg]

I went back to design and created a multi-field combo box instead. Thanks for all the help.