How to get only the most recent date per job number?

[Will]

I have a database that tracks projects for a given customer.

Each project has a unique number- for each project we list dated status
entries.

For instance, for job numer 101, we will make regular entries sorted by
date,

To (hopefully) further clarify:

Job #101 --------- 10/01/05 Project Start
--- 10/05/05 Estimate to customer
--- 10/10/05 Customer planning conference


Job #102 --------- 10/03/05 Project Start
--- 10/15/05 Field Work started
--- 10/21/05 Field work complete

The Question: How do I end up with only the latest status entry per job
i.e.
Job #101 ---------10/10/05 Customer planning conference
Job #102 ---------10/21/05 Field work complete
Job #103 --------- etc.,

 

[Arslan]

Assuming you have the date in a separate field, why don't you use "max"
in your query for the date field so only the latest date shows. You can
also use "max" in your report "max(date)" if you do not want to change
your query (in case you are using that query for other reports)

 

[Will]

Assuming you have the date in a separate field, why don't you use "max"
in your query for the date field so only the latest date shows. You can
also use "max" in your report "max(date)" if you do not want to change
your query (in case you are using that query for other reports)

What I have ended up with is the single Max date from all of the records
instead of the Max for each individual job.

 

[John Spencer]

SQL would look something like the following. This assumes that you have a
Status table that has three fields.
Job - The Job Id
StatusDate - the date of the status
Status = the description of the status


SELECT JOB, StatusDate, Status
FROM StatusTable
WHERE StatusDate =
(SELECT Max(StatusDate)
FROM JobTable as JT
WHERE JT.Job = JobTable.Job)

 

[Will]

SQL would look something like the following. This assumes that you have a
Status table that has three fields.
Job - The Job Id
StatusDate - the date of the status
Status = the description of the status


SELECT JOB, StatusDate, Status
FROM StatusTable
WHERE StatusDate =
(SELECT Max(StatusDate)
FROM JobTable as JT
WHERE JT.Job = JobTable.Job)

Thanks for your response John. You'll have to bear with me as I'm new
at this. The last two lines I'm not clear on. Does JobTable represent
another table that contains the job numbers? What are you trying to
accomplish with the last two lines in the statement?

Regards,
William

 

[John Spencer]

Whoops, I started out with one tablename and switched to another. Sorry.

JobTable should have been StatusTable. The query uses two instances of the
same table. The second instance is given another name so that the SQL
parser can distinguish which instance to use when it is building the query.

Revised (and I hope correct) SQL statement.

SELECT JOB, StatusDate, Status
FROM StatusTable
WHERE StatusDate =
(SELECT Max(StatusDate)
FROM StatusTable as JT
WHERE JT.Job = StatusTable.Job)

 

[Will]

Whoops, I started out with one tablename and switched to another. Sorry.

JobTable should have been StatusTable. The query uses two instances of the
same table. The second instance is given another name so that the SQL
parser can distinguish which instance to use when it is building the query.

Revised (and I hope correct) SQL statement.

SELECT JOB, StatusDate, Status
FROM StatusTable
WHERE StatusDate =
(SELECT Max(StatusDate)
FROM StatusTable as JT
WHERE JT.Job = StatusTable.Job)

This still doesn't work. First I get an error on the missing bracket,
After correcting that I get a syntax error.

William

 

[John Spencer]

I'm assuming that you mean the following (privately posted) version did not
work

SELECT [Date], Information, JobNumber
FROM tblStatus
WHERE tblStatus.[Date] =
(SELECT Max(T.[Date] )
FROM tblStatus as T
WHERE T.JobNumber = tblStatus.JobNumber)

If that is the case, I am stuck. I don't see my error, but we can try one
other option. Remove the [] around the Date in the subquery.

SELECT [Date], Information, JobNumber
FROM tblStatus
WHERE tblStatus.[Date] =
(SELECT Max(T.Date )
FROM tblStatus as T
WHERE T.JobNumber = tblStatus.JobNumber)

I put the brackets there since Date is a reserved word in Access - meaning
get the system date. Prefixing the table alias to the word "Date" should be
sufficient for Access to know this is a field name and not the Date
function.

If this still doesn't work, I would suggest the two query approach which I
outlined in the private post.