[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

Translate Oracle SQL syntax to Access 2007 SQL syntax

Since Access 2007 does not use With... As or MINUS, can the following query be translated into Access 2007 SQL language?

I am running an exception report for staff sign in dates, showing the names & dates of the staff who did not sign in during a certain date range (i.e., 10/1/2013-10/15/2013).
Thanks!

With distinct_users as (
select distinct lastname from aps.facultydates
)
select b.lastname, a.visitstartdate from aps.facultydates a,distinct_users b
minus
select lastname, visitstartdate from aps.facultydates
0
lbarnett419
Asked:
lbarnett419
3 Solutions
 
sdstuberCommented:
SELECT b.lastname, a.visitstartdate
  FROM aps.facultydates a, (SELECT DISTINCT lastname FROM aps.facultydates) b
 WHERE NOT EXISTS
           (SELECT NULL
              FROM aps.facultydates x
             WHERE b.lastname = x.lastname AND a.visitstart_date = x.visitstartdate)
0
 
sdstuberCommented:
you could also try it with an outer join, but then filter out all values that actually join



SELECT b.lastname, a.visitstartdate
  FROM aps.facultydates a,
       (SELECT DISTINCT lastname FROM aps.facultydates) b
       LEFT JOIN aps.facultydates x
           ON b.lastname = x.lastname AND a.visitstart_date = x.visitstartdate
 WHERE x.lastname IS NULL
0
 
lbarnett419Author Commented:
Hi SdStuber:
(1) Tried first version but every row is duplicated. The code & results are in the attachment Exceptions.doc.
Note:Access changed the WHERE NOT EXISTS to WHERE EXISTS=false clause

(2) The second version: Access doesn't like the format or something. I get this vague error:  Syntax error in JOIN operation. The code is also in the Exceptions.doc attachment.

Thank you!
Exceptions.doc
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
SharathData EngineerCommented:
give a try.
SELECT T1.* 
  FROM (SELECT B.FacultyName, 
               A.VisitStartDate1 
          FROM TFacultyDates2 AS A, 
               (SELECT DISTINCT facultyname 
                  FROM TFacultyDates2) AS B) T1 
       LEFT JOIN TFacultyDates2 AS X 
              ON b.facultyname = x.facultyname 
                 AND a.visitstartdate1 = x.visitstartdate1 
 WHERE x.facultyname IS NULL

Open in new window

0
 
PortletPaulCommented:
the alias T1 should be used in that final left join
SELECT T1.*
FROM (
      SELECT
        B.FacultyName
      , A.VisitStartDate1
   FROM [TFacultyDates2] AS [A]
      , (SELECT DISTINCT facultyname FROM [TFacultyDates2]) AS [B]
   ) AS T1
LEFT JOIN [TFacultyDates2] AS [X] ON T1.facultyname = X.facultyname AND T1.visitstartdate1 = X.visitstartdate1
WHERE X.facultyname IS NULL

Open in new window

the use of [ ] isn't essential.

note the basis of this query is building a Cartesian product (all combinations) of B.FacultyName and A.VisitStartDate1
 then joining to the existing records
 then ignoring those joined records
= those combinations of B.FacultyName and A.VisitStartDate1 that don't yet exist as records
0
 
lbarnett419Author Commented:
Thanks to all for helping with this. I am now able to produce the exception report.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now