Solved

Translate Oracle SQL syntax to Access 2007 SQL syntax

Posted on 2013-10-28
6
311 Views
Last Modified: 2013-11-01
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
Comment
Question by:lbarnett419
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39606374
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 50 total points
ID: 39606384
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
 

Author Comment

by:lbarnett419
ID: 39606897
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 41

Accepted Solution

by:
Sharath earned 400 total points
ID: 39607868
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
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 50 total points
ID: 39611158
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
 

Author Closing Comment

by:lbarnett419
ID: 39617740
Thanks to all for helping with this. I am now able to produce the exception report.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question