Solved

SQL Subquery Help

Posted on 2012-03-29
5
291 Views
Last Modified: 2012-03-29
Hi Guys and Gals

I have 2 SQL statements that i've created in MSAccess but ideally i want just one SQL.

Statement 1:

SELECT Roster.member, Attendance.date
FROM Roster, Attendance
GROUP BY Roster.member, Attendance.date;

Open in new window


This SQL creates a list of all members and all possible dates.


Statement 2:

SELECT JAGM1.member, JAGM1.Date
FROM (Attendance RIGHT JOIN JAGM1 ON (Attendance.member = JAGM1.member) AND (Attendance.date = JAGM1.date)) LEFT JOIN Roster ON JAGM1.member = Roster.member
WHERE (((JAGM1.Date)>=([Roster].[join_date])) AND ((Attendance.member) Is Null));

Open in new window


This query uses the result of JAGM1 (which is generated from statement 1) to find some unmatched records that fall on or after the join date.

Is it possible to combine the two SQL statements into one?

James
0
Comment
Question by:Delerium1978
[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
  • 3
  • 2
5 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 37782370
Try this (you may have to rename your extsing query1 to something like JAGM1_Backup, sinc JAGM1 is used as an alias in this):


SELECT JAGM1.member, JAGM1.Date
FROM (Attendance RIGHT JOIN 
(SELECT Roster.member, Attendance.date
FROM Roster, Attendance
GROUP BY Roster.member, Attendance.date) AS JAGM1
ON (Attendance.member = JAGM1.member) AND (Attendance.date = JAGM1.date)) LEFT JOIN Roster ON JAGM1.member = Roster.member
WHERE (((JAGM1.Date)>=([Roster].[join_date])) AND ((Attendance.member) Is Null));

Open in new window

0
 

Author Comment

by:Delerium1978
ID: 37782392
syntax error between line 5 & 6 - error in FROM clause.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 37782415
Try this:
SELECT J1.member, J1.Date
FROM (Attendance RIGHT JOIN 
(SELECT Roster.member, Attendance.date
FROM Roster, Attendance
GROUP BY Roster.member, Attendance.date) AS J1
ON (Attendance.member = J1.member) AND (Attendance.date = J1.date)) LEFT JOIN Roster ON J1.member = Roster.member
WHERE (((J1.Date)>=([Roster].[join_date])) AND ((Attendance.member) Is Null));

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 37782479
Cleaned up a little:

SELECT J1.member, J1.Date
FROM (Attendance RIGHT JOIN
(SELECT Roster.member, Attendance.date
FROM Roster, Attendance
GROUP BY Roster.member, Attendance.date) AS J1
ON Attendance.member = J1.member AND Attendance.date = J1.date) LEFT JOIN Roster ON J1.member = Roster.member
WHERE J1.Date >= Roster.join_date  AND member Is Null
0
 

Author Closing Comment

by:Delerium1978
ID: 37782521
Perfect - many thanks!!!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

730 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