By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,838 Members | 2,222 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,838 IT Pros & Developers. It's quick & easy.

Combine of 2 queries from different Tabled

P: 4
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT LOGGED_CALLS.LOGGED_BY, CALL_CATEGORIES.DESCRIPTION, LOGGED_CALLS.DATE_LOGGED, LOGGED_CALLS.CALL_NO, LOGGED_CALLS.CALL_DESCRIPTION, LOGGED_CALLS.CALL_DURATION
  2. FROM iesm_support.dbo.CALL_CATEGORIES CALL_CATEGORIES, iesm_support.dbo.LOGGED_CALLS LOGGED_CALLS
  3. 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<=?))
  4. ORDER BY LOGGED_CALLS.DATE_LOGGED
Second query for the addtional support call by user :
Expand|Select|Wrap|Line Numbers
  1. SELECT ADDITIONAL_CALLS.LOGGED_BY, CALL_CATEGORIES.DESCRIPTION, ADDITIONAL_CALLS.CALL_NO, ADDITIONAL_CALLS.DATE_LOGGED, ADDITIONAL_CALLS.CALL_DURATION, LOGGED_CALLS.CALL_DESCRIPTION
  2. FROM iesm_support.dbo.ADDITIONAL_CALLS ADDITIONAL_CALLS, iesm_support.dbo.CALL_CATEGORIES CALL_CATEGORIES, iesm_support.dbo.LOGGED_CALLS LOGGED_CALLS
  3. 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
Mar 31 '07 #1
Share this Question
Share on Google+
6 Replies


iburyak
Expert 100+
P: 1,017
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.
Apr 1 '07 #2

P: 4
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
Apr 1 '07 #3

iburyak
Expert 100+
P: 1,017
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.
Apr 1 '07 #4

P: 4
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
Apr 1 '07 #5

iburyak
Expert 100+
P: 1,017
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
Apr 1 '07 #6

P: 4
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
Apr 4 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.