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

CodePudding user response：

Write A SQL query, which should be possible in MS-Access too, like this:

SELECT
   Project,
   Invoice,
   MIN([Desc]) Descriptions,
   SUM(Value) Value,
   MIN(Signed) Signed,
   MAX([Date]) "Date"
FROM data
WHERE Signed<>'' AND [Date]<>''
GROUP BY 
   Project,
   Invoice

output:

Note: for invoice 1 on project A, you will see a value of 300, which is the total for that invoice (when grouping on Project='A' and Invoice=1).

Maybe I should have used DCONCAT (see: Concatenation in between records in Access Query ) for the Description, to include 'TV' in it. But I am unable to test that so I am only referring to this answer.

CodePudding user response：

Try joining a second query:

Select *
From YourTable As T
Inner Join
    (Select Project, Invoice, Max([Date]) As MaxDate
    From YourTable 
    Group By Project, Invoice) As S
    On T.Project = S.Project And T.Invoice = S.Invoice And T.Date = S.MaxDate

CodePudding user response：

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);