Queries

  • Thread starter Thread starter paradigm
  • Start date Start date
P

paradigm

I have a table with fields ID, ProjectNo and IssueDate and a few others.
There are multiple entries for each ProjectNo
I want to find the earliest issuedate for each project.
I can do this with a query
SELECT INVOICES.ProjectNo, Min(INVOICES.IssueDate ) AS MinOfIssueDate
FROM INVOICES GROUP BY INVOICES.ProjectNo;
However I need to find the IDs of the records returned. If I add the ID to
the query then each record is unique and it returns all the records instead
of just the earliest dates.
How can I find the IDs of the records?
Alex
 
Try something like

If proj number type is numeric:

SELECT INVOICES.ProjectNo, INVOICES.IssueDate
FROM INVOICES
WHERE INVOICES.IssueDate = DMax("IssueDate","INVOICES","ProjectNo=" &
[ProjectNo])

If proj number type is text:

SELECT INVOICES.ProjectNo, INVOICES.IssueDate
FROM INVOICES
WHERE INVOICES.IssueDate = DMax("IssueDate","INVOICES","ProjectNo='" &
[ProjectNo] & "'")
 
My projectno is a string.
Thank you. The query does work but it takes a long time to run.
Alec

Ofer Cohen said:
Try something like

If proj number type is numeric:

SELECT INVOICES.ProjectNo, INVOICES.IssueDate
FROM INVOICES
WHERE INVOICES.IssueDate = DMax("IssueDate","INVOICES","ProjectNo=" &
[ProjectNo])

If proj number type is text:

SELECT INVOICES.ProjectNo, INVOICES.IssueDate
FROM INVOICES
WHERE INVOICES.IssueDate = DMax("IssueDate","INVOICES","ProjectNo='" &
[ProjectNo] & "'")


--
Good Luck
BS"D


paradigm said:
I have a table with fields ID, ProjectNo and IssueDate and a few others.
There are multiple entries for each ProjectNo
I want to find the earliest issuedate for each project.
I can do this with a query
SELECT INVOICES.ProjectNo, Min(INVOICES.IssueDate ) AS MinOfIssueDate
FROM INVOICES GROUP BY INVOICES.ProjectNo;
However I need to find the IDs of the records returned. If I add the ID
to
the query then each record is unique and it returns all the records
instead
of just the earliest dates.
How can I find the IDs of the records?
Alex
Click to expand...
Click to expand...
 
Try another way

SELECT T1.ProjectNo, T1.IssueDate
FROM INVOICES As T1
WHERE T1.IssueDate In ( Select TOP 1 T2.IssueDate From INVOICES As T2 WHERE
T2.ProjectNo = T1.ProjectNo Order By T2.IssueDate Desc)

--
Good Luck
BS"D


paradigm said:
My projectno is a string.
Thank you. The query does work but it takes a long time to run.
Alec

Ofer Cohen said:
Try something like

If proj number type is numeric:

SELECT INVOICES.ProjectNo, INVOICES.IssueDate
FROM INVOICES
WHERE INVOICES.IssueDate = DMax("IssueDate","INVOICES","ProjectNo=" &
[ProjectNo])

If proj number type is text:

SELECT INVOICES.ProjectNo, INVOICES.IssueDate
FROM INVOICES
WHERE INVOICES.IssueDate = DMax("IssueDate","INVOICES","ProjectNo='" &
[ProjectNo] & "'")


--
Good Luck
BS"D


paradigm said:
I have a table with fields ID, ProjectNo and IssueDate and a few others.
There are multiple entries for each ProjectNo
I want to find the earliest issuedate for each project.
I can do this with a query
SELECT INVOICES.ProjectNo, Min(INVOICES.IssueDate ) AS MinOfIssueDate
FROM INVOICES GROUP BY INVOICES.ProjectNo;
However I need to find the IDs of the records returned. If I add the ID
to
the query then each record is unique and it returns all the records
instead
of just the earliest dates.
How can I find the IDs of the records?
Alex
Click to expand...
Click to expand...
Click to expand...
 
Back
Top