SQL SELECT filtering out combinations where another column contains empty cells, then returning records based on max date

Question

I have run into an issue I don't know how to solve. I'm working with a MS Access DB.

I have this data:

I want to write a SELECT statement, that gives the following result:

For each combination of Project and Invoice, I want to return the record containing the maximum date, conditional on all records for that combination of Project and Invoice being Signed (i.e. Signed or Date column not empty).

In my head, first I would sort the irrelevant records out, and then return the max date for the remaining records. I'm stuck on the first part.

Could anyone point me in the right direction?

Thanks, Hulu

Answer 1

Start with an initial query which fetches the combinations of Project, Invoice, Date from the rows you want returned by your final query.

SELECT
    y0.Project,
    y0.Invoice,
    Max(y0.Date) AS MaxOfDate
FROM YourTable AS y0
GROUP BY y0.Project, y0.Invoice
HAVING Sum(IIf(y0.Signed Is Null,1,0))=0;

The HAVING clause discards any Project/Invoice groups which include a row with a Null in the Signed column.

If you save that query as qryTargetRows, you can then join it back to your original table to select the matching rows.

SELECT
    y1.Project,
    y1.Invoice,
    y1.Desc,
    y1.Value,
    y1.Signed,
    y1.Date
FROM
    YourTable AS y1
    INNER JOIN qryTargetRows AS sub
    ON (y1.Project = sub.Project)
    AND (y1.Invoice = sub.Invoice)
    AND (y1.Date = sub.MaxOfDate);

Or you can do it without the saved query by directly including its SQL as a subquery.

SELECT
    y1.Project,
    y1.Invoice,
    y1.Desc,
    y1.Value,
    y1.Signed,
    y1.Date
FROM
    YourTable AS y1
    INNER JOIN
    (
        SELECT y0.Project, y0.Invoice, Max(y0.Date) AS MaxOfDate
        FROM YourTable AS y0
        GROUP BY y0.Project, y0.Invoice
        HAVING Sum(IIf(y0.Signed Is Null,1,0))=0
    ) AS sub
    ON (y1.Project = sub.Project)
    AND (y1.Invoice = sub.Invoice)
    AND (y1.Date = sub.MaxOfDate);