Solved

Translate Oracle SQL syntax to Access 2007 SQL syntax

Posted on 2013-10-28
6
304 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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 48

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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

752 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