Selecting additional data/values to display as column in query or in form

I have an employee index, that I need to run queries on for each employee, and display that output along with the original employee.

So say the employee index has an ID, batch, status, and multiple other columns. I have a table where I keep track of every time a column in the employee index changes. I want to display and later export dates of when certain columns use to equal other values, right along side the original employee row.

Naturally I tried creating a form to display multiple value, added a text box to hold my extra information, and changed the value of the text box for each Form_Current event in VBA.

Private Sub Form_Current()
    Me.txtPhaseOne = SimpleLookup("SELECT TOP 1 ChangeDate FROM EmployeeVariables WHERE VariableName = ""Batch"" AND EmployeeID = " & Me.Recordset("ID Number") & " ORDER BY ChangeDate ASC", "ChangeDate")
End Sub

It seemed to work at first…

Until I realized the dates were set to whatever the current record date should be [ second example3

So then I tried a join:

SELECT
  EmployeeIndex.[ID Number],
  EmployeeIndex.Batch,
  EmployeeIndex.Status,
  EmployeeIndex.[First name],
  EmployeeIndex.[Last name],
  EVA.ChangeDate as Phase1
FROM
  EmployeeIndex
  (SELECT TOP 1 ChangeDate FROM EmployeeVariables WHERE EmployeeID = EmployeeIndex.[ID Number] ORDER BY CHangeDate) EVA

Which would work, if I could some how prefetch EmployeeIndex.[ID Number]. (I didn’t name these columns) Except I haven’t got the slightest clue and I’m running on fumes.

SELECT
  EmployeeIndex.[ID Number],
  EmployeeIndex.Batch,
  EmployeeIndex.Status,
  EmployeeIndex.[First name],
  EmployeeIndex.[Last name],
  EVA.ChangeDate as Phase1
FROM
  EmployeeIndex
INNER JOIN
  (SELECT TOP 1 EmployeeID, ChangeDate FROM EmployeeVariables WHERE ORDER BY ChangeDate) EVA
    ON EmployeeIndex.[ID Number] = EVA.EmployeeID

Answer

Try with a subquery:

SELECT
    EmployeeIndex.[ID Number],
    EmployeeIndex.Batch,
    EmployeeIndex.Status,
    EmployeeIndex.[First name],
    EmployeeIndex.[Last name],
        (SELECT Max(EVA.ChangeDate)
        FROM EmployeeVariables AS EVA
        WHERE EVA.EmployeeID = EmployeeIndex.[ID Number]) AS Phase1
FROM
    EmployeeIndex

Advertisement

Answer