SQL - MS Access Query Conversion

SELECT l.learner_number , a.offering_delivery_method AS delivery_type , 
       Delivery_Development_Type , 
      sum (a.learning_hours) 
 
FROM (SELECT a.learner_id, a.delivery_id, a.course_vendor_name,
               a.completion_date, a.action_status, a.completion_status,
               a.offering_template_id, a.offering_id, 
               IIF(MID(o.offering_id,1,5) = 'dowbt' , o.wbt4 , o.ilt4 ) AS Delivery_Development_Type , 
               a.DURATION,
               a.course_title, a.course_number, a.learning_hours,
               IIF(ISNULL(a.delivery_id) AND LOWER (a.course_vendor_name) LIKE "*skl*st*",
                   'E-Learning', IIF(ISNULL(a.delivery_id) , 'Ad-hoc', a.offering_delivery_method))
                  AS offering_delivery_method
       FROM usa.dtv_bv_offering_action a) a
           INNER JOIN usa.dtv_bv_learners l ON l.learner_id = a.learner_id
       LEFT JOIN 
          usa.dtv_bv_offerings o ON o.offering_id = a.offering_id
 WHERE a.completion_status = 'Successful'
   AND a.action_status = 'Completed'
   AND a.completion_date BETWEEN #2009/05/31 00:00:00# AND #2009/06/24 23:59:59#
   AND LENGTH (TRIM (TRANSLATE (a.learning_hours, '.0123456789', ' '))) IS NULL
 
GROUP BY l.learner_number,  a.offering_delivery_method, Delivery_Development_Type

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:

SELECT learner_number , offering_delivery_method AS delivery_type , 
      Delivery_Development_Type , sum(learning_hours) AS TotalHours
 
FROM (SELECT 
      IIF(MID(o.offering_id,1,5) = 'dowbt' , o.wbt4 , o.ilt4 ) AS Delivery_Development_Type , 
      a.learning_hours, l.learner_number, 
      IIF(ISNULL(a.delivery_id) AND LOWER (a.course_vendor_name) LIKE "*skl*st*",
         'E-Learning', IIF(ISNULL(a.delivery_id) , 'Ad-hoc', a.offering_delivery_method))
         AS offering_delivery_method
 
      FROM (usa.dtv_bv_offering_action a
          INNER JOIN usa.dtv_bv_learners l ON l.learner_id = a.learner_id)
          LEFT JOIN usa.dtv_bv_offerings o ON o.offering_id = a.offering_id
 
      WHERE a.completion_status = 'Successful'
          AND a.action_status = 'Completed'
          AND a.completion_date BETWEEN #2009/05/31 00:00:00# AND #2009/06/24 23:59:59#
          AND LENGTH (TRIM (TRANSLATE (a.learning_hours, '.0123456789', ' '))) IS NULL
 
GROUP BY l.learner_number,  a.offering_delivery_method, Delivery_Development_Type
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:

SELECT learner_number , offering_delivery_method AS delivery_type , 
      Delivery_Development_Type , sum(learning_hours) AS TotalHours
 
FROM (SELECT 
      IIF(MID(o.offering_id,1,5) = "dowbt" , o.wbt4 , o.ilt4 ) AS Delivery_Development_Type , 
      a.learning_hours, l.learner_number, 
      IIF(ISNULL(a.delivery_id) AND LOWER (a.course_vendor_name) LIKE "*skl*st*",
         "E-Learning", IIF(ISNULL(a.delivery_id) , "Ad-hoc", a.offering_delivery_method))
         AS offering_delivery_method
 
      FROM (usa.dtv_bv_offering_action a
          INNER JOIN usa.dtv_bv_learners l ON (l.learner_id = a.learner_id))
          LEFT JOIN usa.dtv_bv_offerings o ON (o.offering_id = a.offering_id)
 
      WHERE (a.completion_status = "Successful")
          AND (a.action_status = "Completed")
          AND (a.completion_date BETWEEN #2009-05-31 00:00:00# AND #2009-06-24 23:59:59#)
          AND (LEN(TRIM(REPLACE(a.learning_hours, ".0123456789", " "))) = 0)
) t
GROUP BY l.learner_number,  a.offering_delivery_method, Delivery_Development_Type

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:

AND (LEN(REPLACE(a.learning_hours, ".0123456789", "")) = 0)
                                              

1:

SELECT l.learner_number
, NZ(a.offering_delivery_method, "Ad-hoc") AS delivery_type 
, IIF(Mid(o.offering_id,1,5) = "dowbt", o.wbt4, o.ilt4) AS Delivery_Development_Type
, sum (a.learning_hours)
FROM (
   SELECT learner_id, delivery_id, course_vendor_name,
   completion_date, action_status, completion_status,
   offering_template_id, offering_id, DURATION,
   course_title, course_number, learning_hours,
   IIF(ISNULL(delivery_id)=1 AND LCASE(course_vendor_name) LIKE "*skl*st*", "E-Learning", offering_delivery_method) AS offering_delivery_method
   FROM usa.dtv_bv_offering_action
) a 
INNER JOIN usa.dtv_bv_learners l ON (l.learner_id = a.learner_id))
LEFT OUTER JOIN usa.dtv_bv_offerings o ON (o.offering_id = a.offering_id)
WHERE (a.completion_status = "Successful")
AND (a.action_status = "Completed")
AND ((a.completion_date >= #2009-05-31 00:00:00#)
   AND (a.completion_date <= #2009-06-24 23:59:59#))
AND (LEN(TRIM(REPLACE(a.learning_hours, ".0123456789", " "))) = 0)
GROUP BY l.learner_number, NZ(a.offering_delivery_method, "Ad-hoc"), IIF(Mid(o.offering_id,1,5) = "dowbt", o.wbt4, o.ilt4);

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:

SELECT l.learner_number
, NZ(a.offering_delivery_method, "Ad-hoc") AS delivery_type 
, IIF(Mid(o.offering_id,1,5) = "dowbt", o.wbt4, o.ilt4) AS Delivery_Development_Type
, SUM(CDBL(a.learning_hours))
FROM (
   SELECT learner_id, delivery_id, course_vendor_name,
   completion_date, action_status, completion_status,
   offering_template_id, offering_id, DURATION,
   course_title, course_number, learning_hours,
   IIF(ISNULL(delivery_id) AND LCASE(course_vendor_name) LIKE "*skl*st*", "E-Learning", offering_delivery_method) AS offering_delivery_method
   FROM usa.dtv_bv_offering_action
) a 
INNER JOIN usa.dtv_bv_learners l ON (l.learner_id = a.learner_id))
LEFT OUTER JOIN usa.dtv_bv_offerings o ON (o.offering_id = a.offering_id)
WHERE (a.completion_status = "Successful")
AND (a.action_status = "Completed")
AND ((a.completion_date >= #2009-05-31 00:00:00#)
   AND (a.completion_date <= #2009-06-24 23:59:59#))
AND (ISNUMERIC(a.learning_hours))
GROUP BY l.learner_number, NZ(a.offering_delivery_method, "Ad-hoc"), IIF(Mid(o.offering_id,1,5) = "dowbt", o.wbt4, o.ilt4);

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:

SELECT learner_number , offering_delivery_method AS delivery_type , 
      Delivery_Development_Type , Sum(CDBL(learning_hours)) AS TotalHours
 
FROM (SELECT 
      IIF(MID(o.offering_id,1,5) = "dowbt" , o.wbt4 , o.ilt4 ) AS Delivery_Development_Type , 
      a.learning_hours, l.learner_number, 
      IIF(ISNULL(a.delivery_id) AND LOWER (a.course_vendor_name) LIKE "*skl*st*",
         "E-Learning", IIF(ISNULL(a.delivery_id) , "Ad-hoc", a.offering_delivery_method))
         AS offering_delivery_method
 
      FROM (usa.dtv_bv_offering_action a
          INNER JOIN usa.dtv_bv_learners l ON (l.learner_id = a.learner_id))
          LEFT JOIN usa.dtv_bv_offerings o ON (o.offering_id = a.offering_id)
 
      WHERE (a.completion_status = "Successful")
          AND (a.action_status = "Completed")
          AND (a.completion_date BETWEEN #2009-05-31 00:00:00# AND #2009-06-24 23:59:59#)
          AND (IsNumeric(a.learning_hours) = True)
) t
 
GROUP BY learner_number, offering_delivery_method, Delivery_Development_Type
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:

CREATE OR REPLACE FUNCTION is_number(char_in VARCHAR2) RETURN BOOLEAN IS
 n NUMBER;
BEGIN
  n := TO_NUMBER(char_in);
  RETURN TRUE; 
EXCEPTION
  WHEN OTHERS THEN
    RETURN FALSE;
END is_number;

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9: