convert sql query to be used in access (1 Viewer)

[Mike Hughes]

Can someone help me convert this sql query so I can use it in access? I have no idea of how to do it. Thanks

SELECT a.case_id
FROM NOLDBA_INT_CASE_STATUS a, NOLDBA_RPT_CASE_CHG_IND b
WHERE b.case_id = a.case_id
AND b.sys_curr_date =
(SELECT MAX (c.sys_curr_date)
FROM NOLDBA_RPT_CASE_CHG_IND c
WHERE c.case_id = b.case_id
AND c.sys_curr_date <= '30/AUG/2013')
AND b.time_code =
(SELECT MAX (d.time_code)
FROM NOLDBA_RPT_CASE_CHG_IND d
WHERE d.case_id = b.case_id
AND d.sys_curr_date = b.sys_curr_date)
AND b.aft_case_status = 'O'
ORDER BY a.case_id

 

[WayneRyan]

Mike,

Good to see you're still around here ...

You can start by using a series of queries:

SELECT a.case_id
FROM NOLDBA_INT_CASE_STATUS a, NOLDBA_RPT_CASE_CHG_IND b
WHERE b.case_id = a.case_id AND 
      b.sys_curr_date = (SELECT MAX (c.sys_curr_date)			'
                         FROM   NOLDBA_RPT_CASE_CHG_IND c		' Make one query out of this
                         WHERE  c.case_id = b.case_id AND 		'
                                c.sys_curr_date <= '30/AUG/2013') AND 
      b.time_code = (SELECT MAX (d.time_code)				'
                     FROM NOLDBA_RPT_CASE_CHG_IND d			' And another out of this
                     WHERE d.case_id = b.case_id AND 			'
                           d.sys_curr_date = b.sys_curr_date) AND 
     b.aft_case_status = 'O'
ORDER BY a.case_id 



qryMaxDates:
============
Select case_id, Max(sys_curr_date) As MaxDate
FROM   NOLDBA_RPT_CASE_CHG_IND
Having sys_curr_date <= #30/AUG/2013#
Group By case_ID



qryMaxTimes: (Note: There is only one MaxDate from the prior query, but we'll pass it along)
============
Select a.case_ID, Max(a.sys_curr_date) As MaxDate, Max(a.time_code) As MaxTime
From   NOLDBA_RPT_CASE_CHG_IND As A Inner Join qryMaxDates As B On
          a.case_ID = b.Case_ID
Group By A.Case_ID



qryFinal:
=========
Select a.case_id, b.MaxDate, b.MaxTime
From   NOLDBA_RPT_CASE_CHG_IND As A Inner Join qryMaxTimes As B On
          a.case_ID = b.Case_ID
Order By a.case_id

hth,
Wayne