Combine of 2 queries from different Tabled

[Peter Plate]

Hi all.

I have a system which operates on Windows MSSQL.
It is used for registering Suppotr requests. The system works with different Tables inside a Database.

One of the tables is for new support calls , another table is for additions to a support call , call_no being the linked cell.

I'm using Excell to pull the information from MSSQL with queries.

First query for the logged support calls by user :

SELECT LOGGED_CALLS.LOGGED_BY, CALL_CATEGORIES.DESCRIPTION, LOGGED_CALLS.DATE_LOGGED, LOGGED_CALLS.CALL_NO, LOGGED_CALLS.CALL_DESCRIPTION, LOGGED_CALLS.CALL_DURATION
FROM iesm_support.dbo.CALL_CATEGORIES CALL_CATEGORIES, iesm_support.dbo.LOGGED_CALLS LOGGED_CALLS
WHERE CALL_CATEGORIES.CALL_CATEGORY = LOGGED_CALLS.CALL_CATEGORY AND ((LOGGED_CALLS.LOGGED_BY=64) AND (LOGGED_CALLS.DATE_LOGGED>=? And LOGGED_CALLS.DATE_LOGGED<=?))
ORDER BY LOGGED_CALLS.DATE_LOGGED

Second query for the addtional support call by user :

SELECT ADDITIONAL_CALLS.LOGGED_BY, CALL_CATEGORIES.DESCRIPTION, ADDITIONAL_CALLS.CALL_NO, ADDITIONAL_CALLS.DATE_LOGGED, ADDITIONAL_CALLS.CALL_DURATION, LOGGED_CALLS.CALL_DESCRIPTION
FROM iesm_support.dbo.ADDITIONAL_CALLS ADDITIONAL_CALLS, iesm_support.dbo.CALL_CATEGORIES CALL_CATEGORIES, iesm_support.dbo.LOGGED_CALLS LOGGED_CALLS
WHERE LOGGED_CALLS.CALL_NO = ADDITIONAL_CALLS.CALL_NO AND LOGGED_CALLS.CALL_CATEGORY = CALL_CATEGORIES.CALL_CATEGORY AND ((ADDITIONAL_CALLS.LOGGED_BY=64) AND (ADDITIONAL_CALLS.DATE_LOGGED>? And ADDITIONAL_CALLS.DATE_LOGGED<?))

What I now have is : Query 1 and Query 2 on different Worksheets in Excel.
What I want : Information from both queries on SAME worksheet in Excel.

Is this even possible ? (I was messing around with UNION but couldn't get it working)

And If it is possible , how to do so ?

Your help is much appriciated , as I'm not an SQL tech , just a support tech ;)

Peter

[iburyak]

I am not an Excel programmer but I am familiar with basic SQL rules.

First I changed position of columns to match them in both queries which is important.

Second I put order by at the end.


Try this:

[PHP]SELECT LOGGED_CALLS.LOGGED_BY, CALL_CATEGORIES.DESCRIPTION, LOGGED_CALLS.CALL_NO,
LOGGED_CALLS.DATE_LOGGED, LOGGED_CALLS.CALL_DURATION, LOGGED_CALLS.CALL_DESCRIPTION,
FROM iesm_support.dbo.CALL_CATEGORIES CALL_CATEGORIES,
iesm_support.dbo.LOGGED_CALLS LOGGED_CALLS
WHERE CALL_CATEGORIES.CALL_CATEGORY = LOGGED_CALLS.CALL_CATEGORY
AND ((LOGGED_CALLS.LOGGED_BY=64)
AND (LOGGED_CALLS.DATE_LOGGED>=? And LOGGED_CALLS.DATE_LOGGED<=?))

UNION

SELECT ADDITIONAL_CALLS.LOGGED_BY, CALL_CATEGORIES.DESCRIPTION, ADDITIONAL_CALLS.CALL_NO,
ADDITIONAL_CALLS.DATE_LOGGED, ADDITIONAL_CALLS.CALL_DURATION, LOGGED_CALLS.CALL_DESCRIPTION
FROM iesm_support.dbo.ADDITIONAL_CALLS ADDITIONAL_CALLS,
iesm_support.dbo.CALL_CATEGORIES CALL_CATEGORIES,
iesm_support.dbo.LOGGED_CALLS LOGGED_CALLS
WHERE LOGGED_CALLS.CALL_NO = ADDITIONAL_CALLS.CALL_NO
AND LOGGED_CALLS.CALL_CATEGORY = CALL_CATEGORIES.CALL_CATEGORY
AND ((ADDITIONAL_CALLS.LOGGED_BY=64)
AND (ADDITIONAL_CALLS.DATE_LOGGED>? And ADDITIONAL_CALLS.DATE_LOGGED<?))
ORDER BY 4[/PHP]

Hope it helps.

Good Luck.

[Peter Plate]

Tnx for helping.

The first step seems logical to me so i've rewritten the Excel queries, both same ordering now.

But when trying your code it displays :
[]
Microsoft Query

Parameters are not allowed in queries that can't be displayed graphically

<OK>
[]

While I was trying to get it going , I also got this error everytime I changed the query.

Peter

[iburyak]

It is comming from these lines:


[PHP]AND (LOGGED_CALLS.DATE_LOGGED>=? And LOGGED_CALLS.DATE_LOGGED<=?))
AND (ADDITIONAL_CALLS.DATE_LOGGED>? And ADDITIONAL_CALLS.DATE_LOGGED<?))
ORDER BY 4 [/PHP]


Remove thhem and try again.

[Peter Plate]

Tnx but again no go.

When I copy the PHP Code from the Code Block it has a very weird layout (too much spaces and returns ..)

I will dig further , as I cannot get a simple Query from both Tables to display all the wanted information. (I.e in my two excel sheets I get in total 20 results , while the simple query return only 16 results)

Peter

[iburyak]

Try this:

SELECT LOGGED_CALLS.LOGGED_BY, CALL_CATEGORIES.DESCRIPTION, LOGGED_CALLS.CALL_NO,
LOGGED_CALLS.DATE_LOGGED, LOGGED_CALLS.CALL_DURATION, LOGGED_CALLS.CALL_DESCRIPTION,
FROM iesm_support.dbo.CALL_CATEGORIES CALL_CATEGORIES,
iesm_support.dbo.LOGGED_CALLS LOGGED_CALLS
WHERE CALL_CATEGORIES.CALL_CATEGORY = LOGGED_CALLS.CALL_CATEGORY
AND ((LOGGED_CALLS.LOGGED_BY=64)


UNION All

SELECT ADDITIONAL_CALLS.LOGGED_BY, CALL_CATEGORIES.DESCRIPTION, ADDITIONAL_CALLS.CALL_NO,
ADDITIONAL_CALLS.DATE_LOGGED, ADDITIONAL_CALLS.CALL_DURATION, LOGGED_CALLS.CALL_DESCRIPTION
FROM iesm_support.dbo.ADDITIONAL_CALLS ADDITIONAL_CALLS,
iesm_support.dbo.CALL_CATEGORIES CALL_CATEGORIES,
iesm_support.dbo.LOGGED_CALLS LOGGED_CALLS
WHERE LOGGED_CALLS.CALL_NO = ADDITIONAL_CALLS.CALL_NO
AND LOGGED_CALLS.CALL_CATEGORY = CALL_CATEGORIES.CALL_CATEGORY
AND ((ADDITIONAL_CALLS.LOGGED_BY=64)

ORDER BY 4

[Peter Plate]

Thanks all for helping.

But the company who made the program have included some reports now into the application.

With one of the reports I can see a users activity. With another report I can see the total activity , by day , week or even month

You're help is/was much appriciated !!

Pete