Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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