How would I combine or nest these two SQL queries into one?

P

Pato-chan

I've created two separate queries and called the first one
AssignmentEndQuery. Now I need to combine them into one and place it inside
my VB code, but am stumped. I can't seem to get the right syntax for
embedding the 1st query inside the second. Help!

SELECT A.Post AS Post, A.AssignmentEndDate, A.AssignedEmployeeSS AS SS
FROM Assignments AS A
WHERE (((A.Post)=[Forms]![TurnoverRecordsByPostCrew].[cmbPost].[value])
AND ((A.AssignmentEndDate) Is Not Null));

SELECT AssignmentEndQuery.SS, AssignmentEndQuery.AssignmentEndDate,
Employees.QuitReason, Employees_1.[Employee Name]
FROM (Employees RIGHT JOIN AssignmentEndQuery ON
(Employees.EndDate=AssignmentEndQuery.AssignmentEndDate)
AND (Employees.SS=AssignmentEndQuery.SS)) INNER JOIN Employees AS
Employees_1 ON AssignmentEndQuery.SS=Employees_1.SS;
 
J

John W. Vinson

I've created two separate queries and called the first one
AssignmentEndQuery. Now I need to combine them into one and place it inside
my VB code, but am stumped. I can't seem to get the right syntax for
embedding the 1st query inside the second. Help!

SELECT A.Post AS Post, A.AssignmentEndDate, A.AssignedEmployeeSS AS SS
FROM Assignments AS A
WHERE (((A.Post)=[Forms]![TurnoverRecordsByPostCrew].[cmbPost].[value])
AND ((A.AssignmentEndDate) Is Not Null));

SELECT AssignmentEndQuery.SS, AssignmentEndQuery.AssignmentEndDate,
Employees.QuitReason, Employees_1.[Employee Name]
FROM (Employees RIGHT JOIN AssignmentEndQuery ON
(Employees.EndDate=AssignmentEndQuery.AssignmentEndDate)
AND (Employees.SS=AssignmentEndQuery.SS)) INNER JOIN Employees AS
Employees_1 ON AssignmentEndQuery.SS=Employees_1.SS;
Click to expand...

Well, we don't know anything about your data model, or what you're trying to
accomplish.

What do these two queries return?

What do you mean by "combine"? What result do you want? Is the first query
saved as AssignmentEndQuery, or is that something else?

John W. Vinson [MVP]
 
J

John Spencer

PERHAPS something like the following is waht you are looking for.

SELECT AssignmentEndQuery.SS
, AssignmentEndQuery.AssignmentEndDate
, Employees.QuitReason
, Employees_1.[Employee Name]
FROM (Employees RIGHT JOIN
(SELECT A.Post AS Post
, A.AssignmentEndDate
, A.AssignedEmployeeSS AS SS
FROM Assignments AS A) as Q ON
(Employees.EndDate=Q.AssignmentEndDate)
AND (Employees.SS=Q.SS)) INNER JOIN Employees AS
Employees_1 ON Q.SS=Employees_1.SS
WHERE Q.Post=[Forms]![TurnoverRecordsByPostCrew]![cmbPost]
AND Q.AssignmentEndDate Is Not Null

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
P

Pato-chan

I have two tables, one called Employees and the other called Assignments.
The Employees table consists of the following field:
SS
Name
EndDate
QuitReason

The Assignments table consists of:
Post
AssignedEmployeeSS
AssignmentEndDate

I first select the Post, AssignmentEndDate and AssignedEmployeeSS from the
Assignments table where the post is X and the assignment has ended, i.e.
AssignmentEndDate Is Not Null.

This query looks as follows:
SELECT Assignments.AssignmentEndDate, Assignments.AssignedEmployeeSS,
Assignments.Post
FROM Assignments
WHERE (((Assignments.AssignmentEndDate) Is Not Null) AND
((Assignments.Post)="X"));

I saved it and called it "AssignmentEndQuery."

From this data, I need to check and see if the employee actually quit the
company or just got transferred off the post. So if the employee's EndDate
is equal to the AssignmentEndDate, then he/she quit the post when they quit
the company and I need to show his/her Name as well as the QuitReason. If
the EndDate is not equal to the AssignmentEndDate, then the employee did not
quit and I just need to show the Name.

The following successfully retrieves the data I need:
SELECT AssignmentEndQuery.SS, AssignmentEndQuery.AssignmentEndDate,
Employees.QuitReason, Employees_1.[Employee Name]
FROM (Employees RIGHT JOIN AssignmentEndQuery ON (Employees.EndDate =
AssignmentEndQuery.AssignmentEndDate) AND (Employees.SS =
AssignmentEndQuery.SS)) INNER JOIN Employees AS Employees_1 ON
AssignmentEndQuery.SS = Employees_1.SS;

However, I can't use it in that form. I need one query, not two separate
ones.

Your help is much appreciated.





John Spencer said:
PERHAPS something like the following is waht you are looking for.

SELECT AssignmentEndQuery.SS
, AssignmentEndQuery.AssignmentEndDate
, Employees.QuitReason
, Employees_1.[Employee Name]
FROM (Employees RIGHT JOIN
(SELECT A.Post AS Post
, A.AssignmentEndDate
, A.AssignedEmployeeSS AS SS
FROM Assignments AS A) as Q ON
(Employees.EndDate=Q.AssignmentEndDate)
AND (Employees.SS=Q.SS)) INNER JOIN Employees AS
Employees_1 ON Q.SS=Employees_1.SS
WHERE Q.Post=[Forms]![TurnoverRecordsByPostCrew]![cmbPost]
AND Q.AssignmentEndDate Is Not Null

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Pato-chan said:
I've created two separate queries and called the first one
AssignmentEndQuery. Now I need to combine them into one and place it inside
my VB code, but am stumped. I can't seem to get the right syntax for
embedding the 1st query inside the second. Help!

SELECT A.Post AS Post, A.AssignmentEndDate, A.AssignedEmployeeSS AS SS
FROM Assignments AS A
WHERE (((A.Post)=[Forms]![TurnoverRecordsByPostCrew].[cmbPost].[value])
AND ((A.AssignmentEndDate) Is Not Null));

SELECT AssignmentEndQuery.SS, AssignmentEndQuery.AssignmentEndDate,
Employees.QuitReason, Employees_1.[Employee Name]
FROM (Employees RIGHT JOIN AssignmentEndQuery ON
(Employees.EndDate=AssignmentEndQuery.AssignmentEndDate)
AND (Employees.SS=AssignmentEndQuery.SS)) INNER JOIN Employees AS
Employees_1 ON AssignmentEndQuery.SS=Employees_1.SS;
Click to expand...
Click to expand...
 
M

Michael Gramelspacher

I have two tables, one called Employees and the other called Assignments.
The Employees table consists of the following field:
SS
Name
EndDate
QuitReason

The Assignments table consists of:
Post
AssignedEmployeeSS
AssignmentEndDate

I first select the Post, AssignmentEndDate and AssignedEmployeeSS from the
Assignments table where the post is X and the assignment has ended, i.e.
AssignmentEndDate Is Not Null.

This query looks as follows:
SELECT Assignments.AssignmentEndDate, Assignments.AssignedEmployeeSS,
Assignments.Post
FROM Assignments
WHERE (((Assignments.AssignmentEndDate) Is Not Null) AND
((Assignments.Post)="X"));

I saved it and called it "AssignmentEndQuery."

From this data, I need to check and see if the employee actually quit the
company or just got transferred off the post. So if the employee's EndDate
is equal to the AssignmentEndDate, then he/she quit the post when they quit
the company and I need to show his/her Name as well as the QuitReason. If
the EndDate is not equal to the AssignmentEndDate, then the employee did not
quit and I just need to show the Name.

The following successfully retrieves the data I need:
SELECT AssignmentEndQuery.SS, AssignmentEndQuery.AssignmentEndDate,
Employees.QuitReason, Employees_1.[Employee Name]
FROM (Employees RIGHT JOIN AssignmentEndQuery ON (Employees.EndDate =
AssignmentEndQuery.AssignmentEndDate) AND (Employees.SS =
AssignmentEndQuery.SS)) INNER JOIN Employees AS Employees_1 ON
AssignmentEndQuery.SS = Employees_1.SS;

However, I can't use it in that form. I need one query, not two separate
ones.

Your help is much appreciated.
Click to expand...

Maybe consider a different database design. Note that the CHECK constraints
would be better as validation rules in table design. This is merely a
suggestion. It takes time to develop a good database design, but without it you
are begging for problems.

Sub CreateTables()

With CurrentProject.Connection

..Execute _
"CREATE TABLE Employees" & _
" (EmployeeSS CHAR (9) NOT NULL PRIMARY KEY" & _
", EmployeeName VARCHAR (50) NOT NULL);"

..Execute _
"CREATE TABLE EmploymentHistory" & _
" (EmployeeSS CHAR (9) NOT NULL" & _
" REFERENCES Employees(EmployeeSS)" & _
", HireDate DATETIME NOT NULL" & _
", TerminateDate DATETIME NULL" & _
", CONSTRAINT ck_terminate_after_hire_date" & _
" CHECK (TerminateDate >= HireDate)" & _
", TerminateReason VARCHAR (20) NULL" & _
", CONSTRAINT ck_valid_terminate_reason" & _
" CHECK (TerminateReason IN ('Quit', 'Fired', 'Died'))" & _
", PRIMARY KEY (EmployeeSS, HireDate));"

..Execute _
"CREATE TABLE PostNames" & _
"(PostName VARCHAR (20) NOT NULL PRIMARY KEY);"

..Execute _
"CREATE TABLE EmployeePostings" & _
"(EmployeeSS CHAR (9) NOT NULL" & _
", HireDate DATETIME NOT NULL" & _
", CONSTRAINT fk_employment_history" & _
" FOREIGN KEY (EmployeeSS, HireDate)" & _
" REFERENCES EmploymentHistory (EmployeeSS, HireDate)" & _
", PostName VARCHAR (30) NOT NULL" & _
" REFERENCES PostNames (PostName)" & _
", AssignDate DATETIME NOT NULL" & _
", CONSTRAINT ck_assign_date_not_before_hire_date" & _
" CHECK (AssignDate >= HireDate)" & _
", DropDate DATETIME NULL" & _
", CONSTRAINT ck_drop_date_after_last_assign_date" & _
" CHECK (DropDate >= AssignDate)" & _
", DropReason VARCHAR (20) NULL" & _
", CONSTRAINT ck_valid_drop_reason" & _
" CHECK (DropReason IN ('Reassigned','Quit','Fired','Died'))" & _
", PRIMARY KEY (EmployeeSS, HireDate, PostName,AssignDate));"
End With

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Jet finds a syntax error in my SQL statement 7
How to combine two queries into one? 2
How could I combine these 2 queries into one in Access 2007? 2
HOW DO I COMBINE two spreadsheets into one by names in column a 3
how would i combine three queries into one table 4
how can I combine two serials into one 2

Top