Preventing result of multiple queries from being sorted 2

[TonyBoex]

I have an ASP page which displays statistics of my database (# of records matching specific criteria). There are approx. 50 stats that need to be gathered and passed back to the calling page. I have the following code to return the first 4 stats, but rather than returning the "MA" Count, then the "MC" Count, etc., the result seems to be sorted so that the smallest count is first, then the next smallest, etc...

SELECT Count([MishapID]) AS MA
FROM tblMishaps
WHERE (((tblMishaps.MishapID) In (
SELECT DISTINCT tblMishaps.MishapID
FROM tblMishaps, tblFactors, tblMishapFactors
WHERE tblMishaps.MishapID = tblMishapFactors.MishapID_FK AND
tblFactors.[3rdLevelCode] = tblMishapFactors.[3rdLevelCode_FK] AND (
tblFactors.[1stLevelCode] = 'MA'))))
UNION
SELECT Count([MishapID]) AS MC
FROM tblMishaps
WHERE (((tblMishaps.MishapID) In (
SELECT DISTINCT tblMishaps.MishapID
FROM tblMishaps, tblFactors, tblMishapFactors
WHERE tblMishaps.MishapID = tblMishapFactors.MishapID_FK AND
tblFactors.[3rdLevelCode] = tblMishapFactors.[3rdLevelCode_FK] AND (
tblFactors.[1stLevelCode] = 'MC'))))
UNION
SELECT Count([MishapID]) AS MG
FROM tblMishaps
WHERE (((tblMishaps.MishapID) In (
SELECT DISTINCT tblMishaps.MishapID
FROM tblMishaps, tblFactors, tblMishapFactors
WHERE tblMishaps.MishapID = tblMishapFactors.MishapID_FK AND
tblFactors.[3rdLevelCode] = tblMishapFactors.[3rdLevelCode_FK] AND (
tblFactors.[1stLevelCode] = 'MG'))))
UNION
SELECT Count([MishapID]) AS WC
FROM tblMishaps
WHERE (((tblMishaps.MishapID) In (
SELECT DISTINCT tblMishaps.MishapID
FROM tblMishaps, tblFactors, tblMishapFactors
WHERE tblMishaps.MishapID = tblMishapFactors.MishapID_FK AND
tblFactors.[3rdLevelCode] = tblMishapFactors.[3rdLevelCode_FK] AND (
tblFactors.[1stLevelCode] = 'WC'))));

How do I get the results to appear in the same order as the SELECT? Or more importantly, is it possible to return the results so that each column has the 2 letter criteria as the column name, and the count associated with each criteria in their respective columns?

Thanks for any assistance.

 

[Sepahram]

You can pick one of the following:

1. add a constant number as a column to your SELECT which indicates the order of executed statement. Then, sort the whole results by it.

SELECT 1 AS SortNo , YourFieldList1 ...
UNION
SELECT 2 AS SortNo , YourFieldList2 ...
ORDER BY SortNo
...

2. Create a temp table. Execute the statement separatedly an an INSERT query. Select results. Drop temp table..

CREATE TABLE #TempTable
INSERT INTO #TempTable SELECT YourFieldList1 ...
INSERT INTO #TempTable SELECT YourFieldList2 ...
SELECT * FROM #TempTable
DROP TABLE #TempTable
...

HTH,
Amir

 

[tlbroadbent]

Use UNION ALL rather than UNION. SQL removes duplicates from the UNION unless you indicate ALL. In process of removing duplicates, it must sort the results. Terry

http://members.home.net/tlbroadbent/index.htm