Solved

Translate Oracle SQL syntax to Access 2007 SQL syntax

Posted on 2013-10-28
6
297 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

809 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