Latest Date/Project Query

[justtrentjohnson]

Hello all,



I am attempting to build a query that Selects the latest date from Each project I have listed. Here's an example:

What I have:
Project 1 (date)
Project 1 (date)
Project 2 (date)
Project 3 (date)

What I want:
Project 1 (Latest Date)
Project 2 (Latest Date)
Project 3 (Latest Date)

How would I start?

[John_G]

Hi -

You could use a query with the "Totals" option. In the Total row, select "group by" for all the fields except the date; for that one you would select "Max".

When you run the query, you will get the maximum (latest) date for each project. In order for this to give you exactly what you want, you have to be sure that the columns you select have the same data for each project, except for the date, otherwise you won't get one line per project.

John

[justtrentjohnson]

John,
The columns are not the same data. The logical format of the table is

TABLE{Project,Task,DateCompleted}

I can get the query down to:
Project | MAX(DateCompleted)

However, I want the output to be The project, the latest date, and the task associated with that project/date, however I can't include task in the aggregate function because if I do, Project+Task are mostly unique, and I get a data dump.

I have a feeling I'll need to implement a subquery, however I'm having a hard time pinpointing how to do it.

[SMC]

This post might help you.

http://www.techonthenet.com/access/q...uery2_2007.php

[John_G]

There are other ways, but here's what I would do:

Once you have your project/max date query, create another new query.

In the new query, include the first query and the original table. Join these two on Project number and date.

When you run it, the task will now be the task associated with the latest date.

John

[justtrentjohnson]

After setting up the relationship with the Table and Query, I've gotten a even larger data dump consisting of multiple entries repeated. Attached is the SQL for both steps:

Step 1:
SELECT [Tasks Performed].Project, Max([Tasks Performed].DateCompleted) AS MaxOfDateCompleted
FROM [Tasks Performed]
GROUP BY [Tasks Performed].Project;
{This outputs all Projects and the last date something was done}

Step 2:
SELECT [Tasks Performed].*
FROM [Tasks Performed] INNER JOIN [Step 1] ON ([Tasks Performed].DateCompleted = [Step 1].MaxOfDateCompleted) AND ([Tasks Performed].Project = [Step 1].Project);

This produces a ton of unusuable data.

[justtrentjohnson]

UPDATE:
It works now. For some reason it didn't before, but now it does. Thank you!