Help Needed with Sub Query

[dsuslow]

Hello,

I am trying to figure out how best to merge these two queries together:

#1 - this is pretty basic:

SELECT DISTINCT [vtebase].[EbLastName],
                [vtebase].[EbFirstName],
                [vtecomp].[EmAnnual],
                Str(vtecomp.[EmAnnual]
                      * 1.03,9,2) AS [3% Increase],
                Str(vtecomp.[EmAnnual]
                      * 1.05,9,2) AS [5% Increase],
                Str(vtecomp.[EmAnnual]
                      * 1.07,9,2) AS [7% Increase],

	  [vtEqbase].[EqDateNext] AS [Next Review Date],
	[vtEqbase].[EqReviewerName] AS [Reviewed By],
	[vtEqbase].[EqEffectiveDate] AS [Effective Date],
[vtEqbase].[EqOverallRating] AS [Overall Rating],
[vtEqbase].[EqDateCompleted] AS [Date Completed],
                [vtEjob].[EjDivision]               AS [Division],
                [vtEjob].[EjDepartment]             AS [Department],
                [vtEjob].[EjTitle]                  AS [Job Title],
                [vtEEmploy].[EeStatus]              AS [Employment Status],
                [vtEEmploy].[EeCategory]            AS [Employment Category],
                [vtEEmploy].[EeDateLastHire]        AS [Date of Hire],
                [vtecomp].[empaygrade]              AS [Pay Grade]

FROM   vtEBase
       INNER JOIN vtEComp
         ON vtEBase.[EbFlxid] = vtEComp.[EmFlxideb]
       INNER JOIN vtEJob
         ON vtEBase.[EbFlxid] = vtEJob.[EjFlxideb]
       INNER JOIN vtEEmploy
         ON vtEBase.[EbFlxid] = vtEEmploy.[EeFlxideb]
INNER JOIN vtEQbase
	On vtEBase.[Ebflxid] = vtEqbase.[eqflxideb]
WHERE 
 ((((([EbFlagemp] = 'Y'
     AND [EmKind] = 'BASE'
     AND [EmPayType] = 'S'
     AND ([EmDateBeg] <= '#STARTDATE#'
          AND ([EmDateEnd] >= '#STARTDATE#'
                OR [EmDateEnd] IS NULL)))
    AND ([EjDateBeg] <= '#STARTDATE#'
         AND ([EjDateEnd] >= '#STARTDATE#'
               OR [EjDateEnd] IS NULL))
    AND ([EEDateBeg] <= '#STARTDATE#'
         AND ([EEDateEnd] >= '#STARTDATE#'
               OR [EEDateEnd] IS NULL))
    AND [EmKind] = 'BASE')
   AND [EmKind] = 'BASE')
  AND [EmKind] = 'BASE')
 AND [EmKind] = 'BASE')
AND EmKind = 'BASE'

#2 - this is where I am identifying the most record record in eqbase. I am trying to combine the resulting record from this query (#2) with the record returned in #1.

SELECT [eq1].[EqDateNext]      AS [Next Review Date],
       [eq1].[EqReviewerName]  AS [Reviewed By],
       [eq1].[EqEffectiveDate] AS [Effective Date],
       [eq1].[EqOverallRating] AS [Overall Rating],
       [eq1].[eqdatecompleted] AS [Date Completed]
FROM   vteqbase AS eq1
WHERE  ((((((eq1.[eqdatecompleted] = (SELECT MAX(eq2.[eqdatecompleted])
                               FROM   vteqbase AS eq2
                               WHERE  eq2.[eqflxideb] = eq1.[eqflxideb])))))))

Where I'm stumped is where do I put #2 so it executes correctly with #1. I'm really stumped. Thanks in advance for any help!!

[dickersonka]

to number one and two add the id fields, looks like its vteqbase.eqflxideb

then you can join them from the selects