Query to show only last entry

[kroenc17]

Morning ladies & gents..

Got a pretty simple question. I think.
I have a report that shows status entries from the last 7 days. The idea when this was made is that it SHOULD show 1 entry per project assuming the employee entered the status on the right day. Which doesn't always happen. So I want to change the report to show the last status entered for a specific project - regardless of the date. Attached are some pictures of the query that generates the report. As you can see, most of the data is from one table, and only one field has a condition on it. Not sure which field would be the best to filter to show only the most recent entry. Each status entry has it's own unique ID.

too long, didn't read version:
I need to change a query
from: show entry from last 7 days


to: show only the most recent entry per project

[weekend00]

create a sub query to get most recentely project id, the show the project with this id. something like:
query1: maxdate
select projectID,max(pdate) as maxpDate from project group by projectID
Query2ataSourceForReport
select project.* from project inner join maxdate on project.projectID=maxdate.projectID and project.pdate=maxdate.maxpdate

[kroenc17]

that was a little confusing.. any chance I could get some more step by step instructions?

thanks

[kroenc17]

the date doesn't have to matter - I just want to show the last entry created

[weekend00]

How do we identify the most recent (the last created ) if not by date?

[kroenc17]

Not sure - I was wondering if there was a criteria for 'last created'

[weekend00]

no if you don't have a field like "created time"

[pbaldy]

If your ID field is an autonumber, you can use that to find the last record entered, as it should be numerical order. Here's an example of the technique weekend described:

http://www.baldyweb.com/LastValue.htm

In your case, try with the ID instead of the date and see if that gets you what you want.

[weekend00]

": D" Is this a Big Grin? ": )" ": ( "

[kroenc17]

Thanks for the reply!

Yes, the ID field is an autonumber.

I'm looking at my query design and a little confused by what to do with your 'select', 'group', 'on' and 'and' commands..

SELECT Max(VehicleMiles.DorDate) AS MaxDate, VehicleMiles.CarNum

What do I do with the Max, AS? Sorry I'm really lost.. should I not be in Query Design View?

The relative fields for me are.. I think:
Table: Weekly Status Call (fields: Entry Date, Weekly Status ID)

[pbaldy]

Query design view is fine, just work with the field names corresponding to the Max and Group By. Along the lines of:

SELECT Max([Weekly Status ID]) AS MaxID, ProjectNumberField
FROM TableName
GROUP BY ProjectNumberField

Then the second query joins on the ID field rather than the date field.

[kroenc17]

Er. So I've added what I think is supposed to be on the query.. I'm just really at a loss for how I put in these criteria to select something as MaxID and how to group them... sorry I've been doing Access for three weeks

Yeah I'm pretty much just looking at a blank screen

[pbaldy]

Well, I had the ID field and the project field, you've got the date field. The Totals button on the ribbon is how you see the row that will let you set the max and group by fields.

[kroenc17]

Baldy, I hadn't used the totals button before thanks I was completely clueless as to where those items mentioned were coming from lol

SELECT Max(VehicleMiles.DorDate) AS MaxDate, VehicleMiles.CarNum
FROM VehicleMiles
GROUP BY VehicleMiles.CarNum

Alright, I've got Entry Date set to Max, instead of your DorDate. I don't know what or where I'm supposed to do about

AS MaxDate

. I'm Group By'ing by Project Name. Sound right so far?

Second query has the activities table, projects table, and my first query. The Project table is linked so the Project Name works (I don't know if that needs to be there). And I've linked MaxOf Entry Date to Activity ID...

[pbaldy]

You said that the date field couldn't be used to determine the last entry, which was what you wanted. Do these help?

If your ID field is an autonumber, you can use that to find the last record entered, as it should be numerical order.
...
In your case, try with the ID instead of the date and see if that gets you what you want.

SELECT Max([Weekly Status ID]) AS MaxID, ProjectNumberField
FROM TableName
GROUP BY ProjectNumberField

Then the second query joins on the ID field rather than the date field.

Well, I had the ID field and the project field, you've got the date field

In other words, if the date field isn't appropriate for your needs, use the ID field. In any case, the join must be consistent (date field to date field or ID field to ID field).