troubleshooting Question

MS Access to MS SQL - report requires single SQL statement

Avatar of Shadow Breeze
Shadow BreezeFlag for United States of America asked on
Microsoft AccessMicrosoft SQL Server 2005SQL
26 Comments1 Solution580 ViewsLast Modified:
I am converting an existing MS Access report to MS SQL 2005.  The wild card here is that the reporting environment is CA's CleverPath Forest & Trees (F&T)  - which has several quirks in it's SQL interface.  But more of that below.  The first code snippet here is the original MS Access queries, they build on one another to get the final results in the last query.

The attached file is my converted SQL query, which is basically a 1-1 conversion using table varaibles.  This works perfectly in SQL Mgt Studio.  The problem is that F&T uses application/report components called 'views' which can be, among other things, database interfaces.  Each SQL interface 'view' can only accept a single SQL statement.  There can be mutiple views, but any variables defined inside are local in scope, so I can't pass my results between views unless they are part of the output.  

Adding to the fun, is that you must use the F&T sql language to connect F&T views.  And this, dear reader, does not support outer joins.  They recommend using UNIONs to simulate joins, as UNIONS are allowed as part of the single sql statement.

So, can the SQL be written as a single statement or several statements that can be combined with UNIONS?
Query 1 (Active P Tickets-new)

SELECT call_req.ref_num, call_req.persid, call_req.type, cr_stat.sym AS Code, call_req.summary, CvrtFromUnixTime([call_req]![last_mod_dt]) AS [Last Modified Date], call_req.description
FROM call_req LEFT JOIN cr_stat ON call_req.status=cr_stat.code
GROUP BY call_req.ref_num, call_req.persid, call_req.type, cr_stat.sym, call_req.summary, CvrtFromUnixTime([call_req]![last_mod_dt]), call_req.description, call_req.close_date
HAVING (((call_req.type)="p") AND ((call_req.close_date) Is Null));

Query 2 (Active P Tickets-new1)

SELECT [Active P Tickets log-new].ref_num, [Active P Tickets log-new].code, [Active P Tickets log-new].summary, [Active P Tickets log-new].[Last Modified Date], [Active P Tickets log-new].description, act_log.action_desc, act_log.description, CvrtFromUnixTime([act_log]![time_stamp]) AS [Activity Time]
FROM [Active P Tickets log-new] LEFT JOIN act_log ON [Active P Tickets log-new].persid = act_log.call_req_id
WHERE (((act_log.action_desc) Like "*status change*" And (act_log.action_desc) Not Like [act_log.description])) OR (((act_log.action_desc) Like "log a user comment"));

Query 3 (Active P Tickets-new1-5)

SELECT [Active P Tickets log-new1].ref_num, [Active P Tickets log-new1].code, [Active P Tickets log-new1].summary, [Active P Tickets log-new1].[Last Modified Date], [Active P Tickets log-new1].[Active P Tickets log-new].description, Max([Active P Tickets log-new1].[Activity Time]) AS [MaxOfActivity Time]
FROM [Active P Tickets log-new1]
GROUP BY [Active P Tickets log-new1].ref_num, [Active P Tickets log-new1].code, [Active P Tickets log-new1].summary, [Active P Tickets log-new1].[Last Modified Date], [Active P Tickets log-new1].[Active P Tickets log-new].description;

Query 4 (Active P Tickets-new3)

SELECT [Active P Tickets log-new].ref_num, [Active P Tickets log-new].Code, [Active P Tickets log-new].summary, [Active P Tickets log-new].description, [Active P Tickets log-new1].action_desc, [Active P Tickets log-new1].act_log.description, [Active P Tickets log-new1-5].[MaxOfActivity Time]
FROM [Active P Tickets log-new] LEFT JOIN ([Active P Tickets log-new1-5] LEFT JOIN [Active P Tickets log-new1] ON ([Active P Tickets log-new1-5].ref_num = [Active P Tickets log-new1].ref_num) AND ([Active P Tickets log-new1-5].[MaxOfActivity Time] = [Active P Tickets log-new1].[Activity Time])) ON [Active P Tickets log-new].ref_num = [Active P Tickets log-new1-5].ref_num
GROUP BY [Active P Tickets log-new].ref_num, [Active P Tickets log-new].Code, [Active P Tickets log-new].summary, [Active P Tickets log-new].description, [Active P Tickets log-new1].action_desc, [Active P Tickets log-new1].act_log.description, [Active P Tickets log-new1-5].[MaxOfActivity Time];

Open in new window

Access2SQL.txt
ASKER CERTIFIED SOLUTION
Bodestone

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 26 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 26 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros