Adding queries together

[darkhat01]

I am confused on where to start on this project.

I have 3 queries that are doing counts for different things. They count High's Medium's and Low's. I would like to build a query that takes the totals of all three queries that counts different things and combine them into one query that Adds all the High's together, all Medium’s together, and all the Low's together. I need it to build a bar chart from the new query. I am sure this is simple but I am not sure where to start.

Example:

Query 1 Query 2 Query 3
High 10 High 5 High 3
Medium 5 Medium 30 Medium 15
Low 20 Low 15 Low 5

New Query that adds all the above queries together:
High 18
Medium 50
Low 40

Thanks,
Darkhat01

 

[Remou]

I am not sure from the information you show whether you need a Union Query or a query that joins the three queries on a description field (High, Medium, Low). Perhaps you could post some SQL?

 

[darkhat01]

Remou,

I hope this helps, they are Crosstab Queries That I need add together.
In Query 1 Criticality of Risk creates categories High, Medium, and Low

In Query 2 The complexity_desc creates categories High, Medium, and Low

Query 1:
TRANSFORM Count([EIP All SCA].[SCA_Issue ID]) AS [CountOfSCA_Issue ID]
SELECT [EIP All SCA].[Criticality of Risk]
FROM [EIP All SCA]
GROUP BY [EIP All SCA].[Criticality of Risk]
PIVOT [EIP All SCA].database;

Query 2:
TRANSFORM Count([EIP All RA].RefID) AS CountOfRefID
SELECT [EIP All RA].complexity_desc
FROM [EIP All RA]
GROUP BY [EIP All RA].complexity_desc
PIVOT [EIP All RA].database;

Thanks,

Darkhat01

 

[Remou]

A Union Query might suit:

[tt]TRANSFORM Count(IDCount) AS CountOfID
Select C.Desc, C.DB From
(SELECT A.[SCA_Issue ID] AS IDCount,
A.[Criticality of Risk] AS Desc,
A.Database As DB
FROM [EIP All SCA] A
UNION ALL
SELECT B.RefID AS IDCount,
B.complexity_desc AS Desc,
A.Database As DB
FROM [EIP All RA] B) As C
GROUP BY C.Desc
PIVOT C.DB[/tt]

Or there abouts.

 

[darkhat01]

Hi Remou,

Thanks for getting back to me so quickly.

I went to Query > SQL Specific > Union and cut and pasted the above text in.

I am getting an error that says:

"The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

Any idea what might cause this? I have never heard of a union query so I am not sure where to start in looking at this SQL Statement.

Thanks,

darkhat01

 

[Remou]

Try this:

[tt]TRANSFORM Count(IDCount) AS CountOfID
Select C.Desc, C.DB From
(SELECT A.[SCA_Issue ID] AS IDCount,
A.[Criticality of Risk] AS Desc,
A.[Database] As DB
FROM [EIP All SCA] A
UNION ALL
SELECT B.RefID AS IDCount,
B.complexity_desc AS Desc,
B.[Database] As DB
FROM [EIP All RA] B) As C
GROUP BY C.Desc
PIVOT C.DB[/tt]

Database is a reserved word and I had an error in an alias.

A Union Query is a means of selecting data from two or more tables, if you want to break the above query down, you can test the Union Query part:
[tt]SELECT A.[SCA_Issue ID] AS IDCount,
A.[Criticality of Risk] AS Desc,
A.[Database] As DB
FROM [EIP All SCA] A
UNION ALL
SELECT B.RefID AS IDCount,
B.complexity_desc AS Desc,
B.[Database] As DB
FROM [EIP All RA] B[/tt]

 

[darkhat01]

Remou,

I am sorry, I am still getting the same error...
I am starting to see what you are doing and how it works.

I rewrote it also as with brackets around RefID and complexity_desc but I get the saem error...:

SELECT A.[SCA_Issue ID] AS IDCount,
A.[Criticality of Risk] AS Desc,
A.[Database] As DB
FROM [EIP All SCA] A
UNION ALL
SELECT B.[RefID] AS IDCount,
B.[complexity_desc] AS Desc,
B.[Database] As DB
FROM [EIP All RA] B


Thanks,

darkhat01

 

[Remou]

Desc is a reserved word! That is a surprise:

[tt]SELECT A.[SCA_Issue ID] AS IDCount,
A.[Criticality of Risk] AS [Desc],
A.[Database] As DB
FROM [EIP All SCA] A
UNION ALL
SELECT B.[RefID] AS IDCount,
B.[complexity_desc] AS [Desc],
B.[Database] As DB
FROM [EIP All RA] B[/tt]

 

[JoeAtWork]

Psssst.......

as in

SELECT DateBilled, Total FROM tbl_Invoices ORDER BY DateBilled [COLOR=red]DESC[/color]

 

[Remou]

Doh,

 

[darkhat01]

Thanks Remou,

It did work....

There is one small problem but I want to see if I can figure it out before I ask...

Thanks...

darkhat01