Union Query

[302jess]

I need to write a Union Query based on 12 seperate Query's when i try I only get the results of 3 of them. Is it even possable to get 12 results or will I have to run 4 seperate union query's

 

[Golom]

The limit is 32 queries so that's not the problem.

Is it possible that some of the queries are returning records that duplicate those from other queries? If so, and you're using UNION rather than UNION ALL, then the duplicates will be eliminated from the final result.

 

[Quehay]

There is a bug with more than 22 UNION's (MS KB 244125), and this is fixed by Jet Service Pack 5. But that shouldn't apply here. Posting your SQL will give people a better chance of helping you out here...

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[302jess]

dont laugh I am still learning but here is my union query it should be ez but can't figure it out.




SELECT [CountOfScheduled Grad Date],[CountOfStatus],[Expr1],[CountOf4 Training Related],[Expr2],[CountOfReason for Ineligibility],[Expr3],[Expr4]
FROM [January Total]
UNION SELECT [CountOfScheduled Grad Date],CountOfStatus,Expr1,[CountOf4 Training Related],Expr2,[CountOfReason for Ineligibility],Expr3,Expr4
FROM [February Total]
UNION SELECT [CountOfScheduled Grad Date],CountOfStatus,Expr1,[CountOf4 Training Related],Expr2,[CountOfReason for Ineligibility],Expr3,Expr4
FROM [March Total]
UNION SELECT [CountOfScheduled Grad Date],CountOfStatus,Expr1,[CountOf4 Training Related],Expr2,[CountOfReason for Ineligibility],Expr3,Expr4
FROM [April Total]
UNION SELECT [CountOfScheduled Grad Date],CountOfStatus,Expr1,[CountOf4 Training Related],Expr2,[CountOfReason for Ineligibility],Expr3,Expr4
FROM [May Total]
UNION SELECT [CountOfScheduled Grad Date],CountOfStatus,Expr1,[CountOf4 Training Related],Expr2,[CountOfReason for Ineligibility],Expr3,Expr4
FROM [June Total]
UNION SELECT [CountOfScheduled Grad Date],CountOfStatus,Expr1,[CountOf4 Training Related],Expr2,[CountOfReason for Ineligibility],Expr3,Expr4
FROM [July Total]
UNION SELECT [CountOfScheduled Grad Date],CountOfStatus,Expr1,[CountOf4 Training Related],Expr2,[CountOfReason for Ineligibility],Expr3,Expr4
FROM [August Total]
UNION SELECT [CountOfScheduled Grad Date],CountOfStatus,Expr1,[CountOf4 Training Related],Expr2,[CountOfReason for Ineligibility],Expr3,Expr4
FROM [September Total]
UNION SELECT [CountOfScheduled Grad Date],CountOfStatus,Expr1,[CountOf4 Training Related],Expr2,[CountOfReason for Ineligibility],Expr3,Expr4
FROM [October Total]
UNION SELECT [CountOfScheduled Grad Date],CountOfStatus,Expr1,[CountOf4 Training Related],Expr2,[CountOfReason for Ineligibility],Expr3,Expr4
FROM [November Total]
UNION SELECT [CountOfScheduled Grad Date],CountOfStatus,Expr1,[CountOf4 Training Related],Expr2,[CountOfReason for Ineligibility],Expr3,Expr4
FROM [December Total];

 

[Quehay]

If I read correctly you're trying to get subtotals for various columns by month, no? If you provide a year and month column you'll get accurate sorting by date with month bracketing, then just do the aggregate functions on the columns desired and you'll get the by month totals.

This example uses Northwind customers & orders:

[tt]SELECT DatePart('yyyy',[OrderDate]) AS [Year], DatePart('m',[orderdate]) AS [Month], Customers.CompanyName, Count(Orders.OrderDate) AS [Order Count]
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY DatePart('yyyy',[OrderDate]), DatePart('m',[orderdate]), Customers.CompanyName
ORDER BY DatePart('yyyy',[OrderDate]), DatePart('m',[orderdate]), Customers.CompanyName;


RESULTS:

Year Month Company Name Order Count
1997 2 Around the Horn 1
1997 2 Berglunds snabbköp 2
1997 2 Blondel père et fils 2
1997 2 Consolidated Holdings 1
1997 2 Ernst Handel 1
1997 2 Folk och fä HB 2
1997 2 Königlich Essen 2
1997 2 La maison d'Asie 1
1997 2 LILA-Supermercado 1
1997 2 Mère Paillarde 1
1997 2 Old World Delicatessen 1
1997 2 Princesa Isabel Vinhos 1
1997 2 QUICK-Stop 1
1997 2 Rancho grande 1
1997 2 Reggiani Caseifici 1
[/tt]

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[Golom]

Yeah its a bit ugly because (apparently) you have accepted the default field aliases (e.g. Expr1, Expr2, Expr3, ...) for your aggregate expressions. You can modify this to see what you are actually getting with something like
[tt]
SELECT "January" As [theMonth], [CountOfScheduled Grad Date],[CountOfStatus],[Expr1],[CountOf4 Training Related],[Expr2],[CountOfReason for Ineligibility],[Expr3],[Expr4]
FROM [January Total]

UNION

SELECT "February", [CountOfScheduled Grad Date],CountOfStatus,Expr1,[CountOf4 Training Related],Expr2,[CountOfReason for Ineligibility],Expr3,Expr4
FROM [February Total]

UNION

SELECT "March", [CountOfScheduled Grad Date],CountOfStatus,Expr1,[CountOf4 Training Related],Expr2,[CountOfReason for Ineligibility],Expr3,Expr4
FROM [March Total]

etc.
[/tt]
If there are any duplicates, this should make records from different queries unique and you should see all 12 (assuming that every source query has records.)