Criteria set up for a Query

[June7]

That's why I suggest UNION query.

[JBC]

June 7,
I need to ensure I am substituting the right tablenames and fields. So, can you give me one more assist?

My Primary Key is "DOORS Feature ID" I have 1000+ of these records
My source table (only have one) is "VIDA System Release Symphony Matrix"
My 6 Column names are P25T, P25C, BeOn, None, OS, NF
The data in the cells are R1, R2, tbd, Fut !



SELECT ID, EngMarkField, "1" AS Source, DataField1 AS Data FROM tablename
UNION SELECT ID, EngMarkField, "2", DataField2 FROM tablename
UNION SELECT ID, EngMarkField, "3", DataField3 FROM tablename
UNION SELECT ID, EngMarkField, "4", DataField4 FROM tablename
UNION SELECT ID, EngMarkField, "5", DataField5 FROM tablename;

[June7]

Why don't you make an attempt to figure out the substitutions and post it if it doesn't work. I think my example is rather clear.

Since names have spaces, be sure to enclose in [].

[JBC]

Seems it was a bit more complicated that we thought. Not exactly what I want/need but getting closer.

SELECT [VIDA System Release Symphony Matrix].ID, [VIDA System Release Symphony Matrix].[DOORS Feature ID], [VIDA System Release Symphony Matrix].Feature, [VIDA System Release Symphony Matrix].[Feature Type], [VIDA System Release Symphony Matrix].P25T
FROM [VIDA System Release Symphony Matrix]
WHERE ((([VIDA System Release Symphony Matrix].[Feature Type])="Marketing") AND (([VIDA System Release Symphony Matrix].P25T)="R1"))
UNION
SELECT [VIDA System Release Symphony Matrix].ID, [VIDA System Release Symphony Matrix].[DOORS Feature ID], [VIDA System Release Symphony Matrix].Feature, [VIDA System Release Symphony Matrix].[Feature Type], [VIDA System Release Symphony Matrix].P25C
FROM [VIDA System Release Symphony Matrix]
WHERE ((([VIDA System Release Symphony Matrix].[Feature Type])="Marketing") AND (([VIDA System Release Symphony Matrix].P25C)="R1"))
UNION
SELECT [VIDA System Release Symphony Matrix].ID, [VIDA System Release Symphony Matrix].[DOORS Feature ID], [VIDA System Release Symphony Matrix].Feature, [VIDA System Release Symphony Matrix].[Feature Type], [VIDA System Release Symphony Matrix].OS
FROM [VIDA System Release Symphony Matrix]
WHERE ((([VIDA System Release Symphony Matrix].[Feature Type])="Marketing") AND (([VIDA System Release Symphony Matrix].OS)="R1"))
UNION
SELECT [VIDA System Release Symphony Matrix].ID, [VIDA System Release Symphony Matrix].[DOORS Feature ID], [VIDA System Release Symphony Matrix].Feature, [VIDA System Release Symphony Matrix].[Feature Type], [VIDA System Release Symphony Matrix].None
FROM [VIDA System Release Symphony Matrix]
WHERE ((([VIDA System Release Symphony Matrix].[Feature Type])="Marketing") AND (([VIDA System Release Symphony Matrix].None)="r1"))
UNION SELECT [VIDA System Release Symphony Matrix].ID, [VIDA System Release Symphony Matrix].[DOORS Feature ID], [VIDA System Release Symphony Matrix].Feature, [VIDA System Release Symphony Matrix].[Feature Type], [VIDA System Release Symphony Matrix].BeOn
FROM [VIDA System Release Symphony Matrix]
WHERE ((([VIDA System Release Symphony Matrix].[Feature Type])="Marketing") AND (([VIDA System Release Symphony Matrix].BeOn)="R1"));

[JBC]

I added in "P25T" As Type, etc., to determine what System provided the input. I can use this now to create a report that is useful.

SELECT [VIDA System Release Symphony Matrix].ID, [VIDA System Release Symphony Matrix].[DOORS Feature ID], [VIDA System Release Symphony Matrix].Feature, [VIDA System Release Symphony Matrix].[Feature Type], [VIDA System Release Symphony Matrix].P25T, "P25T" AS Type
FROM [VIDA System Release Symphony Matrix]
WHERE ((([VIDA System Release Symphony Matrix].[Feature Type])="Marketing") AND (([VIDA System Release Symphony Matrix].P25T)="R1"))
UNION
SELECT [VIDA System Release Symphony Matrix].ID, [VIDA System Release Symphony Matrix].[DOORS Feature ID], [VIDA System Release Symphony Matrix].Feature, [VIDA System Release Symphony Matrix].[Feature Type], [VIDA System Release Symphony Matrix].P25C, "P25C" As Type
FROM [VIDA System Release Symphony Matrix]
WHERE ((([VIDA System Release Symphony Matrix].[Feature Type])="Marketing") AND (([VIDA System Release Symphony Matrix].P25C)="R1"))
UNION
SELECT [VIDA System Release Symphony Matrix].ID, [VIDA System Release Symphony Matrix].[DOORS Feature ID], [VIDA System Release Symphony Matrix].Feature, [VIDA System Release Symphony Matrix].[Feature Type], [VIDA System Release Symphony Matrix].OS, "OS" As Type
FROM [VIDA System Release Symphony Matrix]
WHERE ((([VIDA System Release Symphony Matrix].[Feature Type])="Marketing") AND (([VIDA System Release Symphony Matrix].OS)="R1"))
UNION
SELECT [VIDA System Release Symphony Matrix].ID, [VIDA System Release Symphony Matrix].[DOORS Feature ID], [VIDA System Release Symphony Matrix].Feature, [VIDA System Release Symphony Matrix].[Feature Type], [VIDA System Release Symphony Matrix].None, "None" As Type
FROM [VIDA System Release Symphony Matrix]
WHERE ((([VIDA System Release Symphony Matrix].[Feature Type])="Marketing") AND (([VIDA System Release Symphony Matrix].None)="r1"))
UNION
SELECT [VIDA System Release Symphony Matrix].ID, [VIDA System Release Symphony Matrix].[DOORS Feature ID], [VIDA System Release Symphony Matrix].Feature, [VIDA System Release Symphony Matrix].[Feature Type], [VIDA System Release Symphony Matrix].BeOn, "BeOn" As Type
FROM [VIDA System Release Symphony Matrix]
WHERE ((([VIDA System Release Symphony Matrix].[Feature Type])="Marketing") AND (([VIDA System Release Symphony Matrix].BeOn)="R1"))
UNION
SELECT [VIDA System Release Symphony Matrix].ID, [VIDA System Release Symphony Matrix].[DOORS Feature ID], [VIDA System Release Symphony Matrix].Feature, [VIDA System Release Symphony Matrix].[Feature Type], [VIDA System Release Symphony Matrix].NF, "NF" As Type
FROM [VIDA System Release Symphony Matrix]
WHERE ((([VIDA System Release Symphony Matrix].[Feature Type])="Marketing") AND (([VIDA System Release Symphony Matrix].NF)="R1"));

[June7]

The first SELECT line sets the field names. Use alias as in my example. Like:

[VIDA System Release Symphony Matrix].P25T AS Data

You also might want to create a field in the UNION to show the source column, also demonstrated in my example. Like:

"P2T1" AS Source

Don't put the WHERE clauses in the UNION. This is just to reorganize the data. Do another query that uses the UNION as source (like a table) and apply filter criteria in that query.

It would be easier to read without repeating the table name as prefix for each field.

[JBC]

Okay, I will work it. I am only now starting to understand your first SQL language suggestion. I did not when I first tried to read your input. I am learning this cold.