Horizontal to Vertical Help - Please

[Scott Duncan]

We have a Table that stores Test Points for a particular Device [Serial
Number]
This Table is laid out Horizontally:
[Serial Number], [TP1], [TP2], etc...
[Serial Number - 12345], [TP1 - 100], [TP2 - 200], etc...

We want to create a graph or a chart and want to chart the Test Points.

I want to create a Query that is Vertical in layout:
[Serial Number 12345], [TP - 100]
[Serial Number 12345], [TP - 200]
[Serial Number 12345], [TP - 300]
etc...

Any help would greatly be appreciated.

TIA,

SD

 

[Brian Camire]

You might use a union query whose SQL looks something like this:

SELECT
[Serial Number],
[TP1] AS [Test Point]
FROM
[Your Table]
UNION
SELECT
[Serial Number],
[TP2] AS [Test Point]
FROM
[Your Table]
UNION
SELECT
[Serial Number],
[TP3] AS [Test Point]
FROM
[Your Table]
UNION
..
..
..
..
UNION
SELECT
[Serial Number],
[TPN] AS [Test Point]
FROM
[Your Table];

 

[Scott Duncan]

Brian,

Works like a champ... I have never tried a Union Query before.

Thank you !

SD

You might use a union query whose SQL looks something like this:

SELECT
[Serial Number],
[TP1] AS [Test Point]
FROM
[Your Table]
UNION
SELECT
[Serial Number],
[TP2] AS [Test Point]
FROM
[Your Table]
UNION
SELECT
[Serial Number],
[TP3] AS [Test Point]
FROM
[Your Table]
UNION
.
.
.
.
UNION
SELECT
[Serial Number],
[TPN] AS [Test Point]
FROM
[Your Table];

We have a Table that stores Test Points for a particular Device [Serial
Number]
This Table is laid out Horizontally:
[Serial Number], [TP1], [TP2], etc...
[Serial Number - 12345], [TP1 - 100], [TP2 - 200], etc...

We want to create a graph or a chart and want to chart the Test Points.

I want to create a Query that is Vertical in layout:
[Serial Number 12345], [TP - 100]
[Serial Number 12345], [TP - 200]
[Serial Number 12345], [TP - 300]
etc...

Any help would greatly be appreciated.

TIA,

SD

 

[Brian Camire]

Be aware that the default behaviour of UNION is to return distinct records
(that is, no duplicate records).

For example, if you have data that looks like this:

Serial Number, TP1, TP2, TP3
1, 0.1, 0.5, 0.1
2, 0.3, 0.3, 0.4

The query I posted will return something like this:

Serial Number, Test Point
1, 0.1
1, 0.5
2, 0.3
2, 0.4

instead of something like this

Serial Number, Test Point
1, 0.1
1, 0.5
1, 0.1
2, 0.3
2, 0.3
2, 0.4

If you want the latter, use UNION ALL instead of UNION.

Brian,

Works like a champ... I have never tried a Union Query before.

Thank you !

SD

You might use a union query whose SQL looks something like this:

SELECT
[Serial Number],
[TP1] AS [Test Point]
FROM
[Your Table]
UNION
SELECT
[Serial Number],
[TP2] AS [Test Point]
FROM
[Your Table]
UNION
SELECT
[Serial Number],
[TP3] AS [Test Point]
FROM
[Your Table]
UNION
.
.
.
.
UNION
SELECT
[Serial Number],
[TPN] AS [Test Point]
FROM
[Your Table];

We have a Table that stores Test Points for a particular Device [Serial
Number]
This Table is laid out Horizontally:
[Serial Number], [TP1], [TP2], etc...
[Serial Number - 12345], [TP1 - 100], [TP2 - 200], etc...

We want to create a graph or a chart and want to chart the Test Points.

I want to create a Query that is Vertical in layout:
[Serial Number 12345], [TP - 100]
[Serial Number 12345], [TP - 200]
[Serial Number 12345], [TP - 300]
etc...

Any help would greatly be appreciated.

TIA,

SD