How can I return multiple columns in a column subquery in Access 2003 JET SQL?

Question

I'm working on a project which records performance metrics of employees on a daily basis.

The records table holds one row per day of the employees productivity; each column holds a count of items completed in that category, plus total shift time and the date as a Short Date.

The metrics table holds an effective date (Short Date) and columns which match the productivity columns in the records table which keep track of the expected hourly rate of production.

The employees table simply contains employee names and phone extensions.

The Employee Report can be set to variable date ranges to show performance over time, and the GroupLevel(1).GroupOn is set via VBA as well (creates sections by week, month, quarter, etc).

The goal here is to create a query which returns all rows of the records table (which will later be filtered based on the whereCondition passed when opening the report) along with the row of the metrics table which is the highest date less than or equal to the records.date. So far, I've only been able to get the ID (or any other single field) of the metrics row:

SELECT records.*, employees.first, employees.last, employees.ext,  
   (
    SELECT TOP 1 metrics.id 
    FROM metrics 
    WHERE metrics.date<=records.date 
    ORDER BY metrics.date DESC
   ) AS MetricsID
FROM employees 
INNER JOIN records ON employees.id = records.employee
ORDER BY employees.last;

What I am looking for is a way to write this query so I have all columns in each row - when I run my calculation function during GroupFooter2_Format it will mean I won't have to query against metrics for each row in the date range.

Bonus gratitude if you can tell me how to write a query which will automatically divide the column in records by the corresponding one in metrics, so I can just populate the fields directly.

Answer 1

Something on these lines may suit:

SELECT Records.*, Metrics.*, 
    Round([Records].[Productivity]/[Metrics].[ExpectedRate],2) AS Calc, 
    Employees.First, Employees.Last
FROM ((Records 
INNER JOIN 
    (SELECT records.ID, 
       (SELECT TOP 1 metrics.id 
        FROM metrics 
        WHERE metrics.date<=record.date 
        ORDER BY metrics.date DESC ) AS MetricsID 
     FROM records)  AS j 
ON Records.ID = j.ID) 
INNER JOIN Metrics ON j.MetricsID = Metrics.ID) 
INNER JOIN Employees ON Records.Employee = Employees.ID;

However, I am a little suspicious that the data presented has been simplified in that you mention "items completed in that category" but do not mention a category id in either the records table or the metrics table.

In addition, date is a reserved word and therefore a terrible name for almost anything.

It is very rarely a good idea to select *, you should use the field (column) names.