Moving report from Access to Reporting Services

I created 2 stand alone queries and another that combines/joins the 2 for a
cumulative repoirt in MS Access. I need to recreate the combined report in
Reporting Services but do no know how:
Query 1 MON _is Down Karen 10_05
SELECT call_req.ref_num, First(act_log.time_stamp) AS FirstOftime_stamp,
First(CvrtFromUnixTime([time_stamp])) AS [Time Stamp Down]
FROM act_log RIGHT JOIN call_req ON act_log.call_req_id = call_req.persid
WHERE (((act_log.action_desc) Like "% to 'MON-System Down'%"))
GROUP BY call_req.ref_num
ORDER BY call_req.ref_num;
Query 2 MON_is Up Karen 10_05
SELECT call_req.ref_num, First(act_log.time_stamp) AS FirstOftime_stamp,
First(CvrtFromUnixTime([time_stamp])) AS [Time Stamp Up]
FROM act_log RIGHT JOIN call_req ON act_log.call_req_id = call_req.persid
WHERE (((act_log.action_desc) Like "% to 'MON-System Up'%"))
GROUP BY call_req.ref_num
ORDER BY call_req.ref_num;
Combined Query
SELECT [MON _is Down Karen 10_05].ref_num, [MON _is Down Karen 10_05].[Time
Stamp Down], [MON_is Up Karen 10_05].[Time Stamp Up],
AHD_net_res.nr_prim_search_key
FROM (([MON_is Up Karen 10_05] INNER JOIN [MON _is Down Karen 10_05] ON
[MON_is Up Karen 10_05].ref_num = [MON _is Down Karen 10_05].ref_num) INNER
JOIN call_req ON [MON _is Down Karen 10_05].ref_num = call_req.ref_num) LEFT
JOIN AHD_net_res ON call_req.affected_rc = AHD_net_res.id;
Thank you,
KarenI wanted to add the query in Reporting Services I have created:
SELECT DISTINCT
AHD.call_req.ref_num, MIN(DISTINCT
AHD.act_log.time_stamp) AS MINOftime_stamp, MIN(DISTINCT
AHD.act_log.time_stamp) AS [Time Stamp Down],
DATEADD(ss, AHD.act_log.time_stamp - 18000,
CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) AS [Time Down], DATEADD(ss,
act_log_1.time_stamp - 18000, CONVERT(DATETIME,
'1970-01-01 00:00:00', 102)) AS [Time Up], MAX(DISTINCT act_log_1.time_stamp)
AS [Time Stamp Up], MAX(DISTINCT act_log_1.time_stamp)
AS MaxOftime_stamp, AHD.net_res.nr_prim_search_key
FROM AHD.net_res RIGHT OUTER JOIN
AHD.call_req ON AHD.net_res.id =AHD.call_req.affected_rc LEFT OUTER JOIN
AHD.act_log act_log_1 ON AHD.call_req.persid =act_log_1.call_req_id LEFT OUTER JOIN
AHD.act_log ON AHD.call_req.persid =AHD.act_log.call_req_id
WHERE (AHD.act_log.action_desc LIKE '%to ''MON-System Down%') AND
(act_log_1.action_desc LIKE '%to ''MON-System Up%')
GROUP BY AHD.call_req.ref_num, DATEADD(ss, AHD.act_log.time_stamp - 18000,
CONVERT(DATETIME, '1970-01-01 00:00:00', 102)), DATEADD(ss,
act_log_1.time_stamp - 18000, CONVERT(DATETIME,
'1970-01-01 00:00:00', 102)), AHD.net_res.nr_prim_search_key
ORDER BY AHD.net_res.nr_prim_search_key, AHD.call_req.ref_num
--The query works up to a point. I changed "First" to "Min" and "Max". I
am trying to weed out multiple instances of time_stamp when action_desc
inlcludes Mon is Up or Mon is Down. In a perfect world the user would enter
each only once but you know how that goes. So, I want the earliest time_stamp
when action_desc includes "to Mon-System Down" and Max time_stamp when
action_desc includes "to Mon-System Up".
Thanks in advance for any help with this.
Karen
"Moving rpts from Access to Rptg Services" wrote:
> I created 2 stand alone queries and another that combines/joins the 2 for a
> cumulative repoirt in MS Access. I need to recreate the combined report in
> Reporting Services but do no know how:
> Query 1 MON _is Down Karen 10_05
> SELECT call_req.ref_num, First(act_log.time_stamp) AS FirstOftime_stamp,
> First(CvrtFromUnixTime([time_stamp])) AS [Time Stamp Down]
> FROM act_log RIGHT JOIN call_req ON act_log.call_req_id = call_req.persid
> WHERE (((act_log.action_desc) Like "% to 'MON-System Down'%"))
> GROUP BY call_req.ref_num
> ORDER BY call_req.ref_num;
>
> Query 2 MON_is Up Karen 10_05
> SELECT call_req.ref_num, First(act_log.time_stamp) AS FirstOftime_stamp,
> First(CvrtFromUnixTime([time_stamp])) AS [Time Stamp Up]
> FROM act_log RIGHT JOIN call_req ON act_log.call_req_id = call_req.persid
> WHERE (((act_log.action_desc) Like "% to 'MON-System Up'%"))
> GROUP BY call_req.ref_num
> ORDER BY call_req.ref_num;
>
> Combined Query
> SELECT [MON _is Down Karen 10_05].ref_num, [MON _is Down Karen 10_05].[Time
> Stamp Down], [MON_is Up Karen 10_05].[Time Stamp Up],
> AHD_net_res.nr_prim_search_key
> FROM (([MON_is Up Karen 10_05] INNER JOIN [MON _is Down Karen 10_05] ON
> [MON_is Up Karen 10_05].ref_num = [MON _is Down Karen 10_05].ref_num) INNER
> JOIN call_req ON [MON _is Down Karen 10_05].ref_num = call_req.ref_num) LEFT
> JOIN AHD_net_res ON call_req.affected_rc = AHD_net_res.id;
> Thank you,
> Karen