Query

[paradigm]

I have a table with fields ID, ProjectNo and IssueDate and a few others
There are multiple records with different dates for each project
I need to find the minumum date for each project which I can do with
SELECT INVOICES.ProjectNo, Min(INVOICES.IssueDate) AS MinOfIssuedate
FROM INVOICES
GROUP BY INVOICES.ProjectNo
ORDER BY INVOICES.ProjectNo;

This will return a single record for each project with the minimum date
However, I need the ID of the records. If I add the ID into the query as a
groupby then each record is now unique and all records are returned for each
project.
How can I find the record ID of the records returned in the first query.
Alex

 

[Bob Quintal]

I have a table with fields ID, ProjectNo and IssueDate and a few
others There are multiple records with different dates for each
project I need to find the minumum date for each project which I
can do with SELECT INVOICES.ProjectNo, Min(INVOICES.IssueDate) AS
MinOfIssuedate FROM INVOICES
GROUP BY INVOICES.ProjectNo
ORDER BY INVOICES.ProjectNo;

This will return a single record for each project with the minimum
date However, I need the ID of the records. If I add the ID into
the query as a groupby then each record is now unique and all
records are returned for each project.
How can I find the record ID of the records returned in the first
query. Alex

You've got the first of two queries. The second query will join back
on the table to return the ID of the records matching the ProjectNo
and issueDate

 

[Douglas J. Steele]

Try:

SELECT Invoices.ID, Invoices.ProjectNo, Invoices.IssueDate
FROM Invoices
INNER JOIN
(SELECT ProjectNo, Min(IssueDate) AS MinOfIssuedate
FROM Invoices
GROUP BY ProjectNo) AS SubSelect
ON Invoices.ProjectNo = SubSelect.ProjectNo
AND Invoices.IssueDate = SubSelect.MinOfIssueDate
ORDER BY Invoices.ProjectNo

 

[rquintal]

I have a table with fields ID, ProjectNo and IssueDate and a few others
There are multiple records with different dates for each project
I need to find the minumum date for each project which I can do with
SELECT INVOICES.ProjectNo, Min(INVOICES.IssueDate) AS MinOfIssuedate
FROM INVOICES
GROUP BY INVOICES.ProjectNo
ORDER BY INVOICES.ProjectNo;

This will return a single record for each project with the minimum date
However, I need the ID of the records. If I add the ID into the query as a
groupby then each record is now unique and all records are returned for each
project.
How can I find the record ID of the records returned in the first query.
Alex

You've got the first of two queries. The second query will join back
on the table to return the ID of the records matching the ProjectNo
and issueDate

Mr Steele's post even merges the two queries into a single QueryDef
Object.