Query to show only last entry

[kroenc17]

Yes, that helps .. I believe I have the right fields now. I didn't mean to say I CANT use the date, unless of course there's a reason that I couldn't use the date instead of the ID. As for now I'm using ID's.



What I'm still unsure of is:
1.

AS MaxID, ProjectNumberField

2.Should I have a join in my first query (see attachment)
3.Did I match up anything wrong?

Here's your first query

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

so for me its (I think):
VehicleMiles = Weekly Status Call
DorDate = Weekly Status ID
AS MaxDate, VehicleMiles.CarNum = ???
CarNum= Projects.[Project ID]

Here's your second query:
SELECT VehicleMiles.*
FROM VehicleMiles INNER JOIN qryMaxDates
ON (VehicleMiles.CarNum = qryMaxDates.CarNum)
AND (VehicleMiles.DorDate = qryMaxDates.MaxDate)

for me:
Vehicle Miles = Weekly Status Call.*

yours: (VehicleMiles.CarNum = qryMaxDates.CarNum)
mine: Projects.ProjectID = query 1 ProjectID

yours: (VehicleMiles.DorDate = qryMaxDates.MaxDate)
mine: Weekly Status Call.Weekly Status ID = query 1 Max Weekly Status ID

attached are my signature MS paint print screens.. thanks!

[pbaldy]

Can you post the db?

[kroenc17]

sent you a PM

[pbaldy]

For starters, I'm not a fan of the lookup field. It disguises what's really going on:

http://www.mvps.org/access/lookupfields.htm

Save this as qryPaulBase:

SELECT Max([Weekly Status Call].[Weekly Status ID]) AS [MaxOfWeekly Status ID], [Weekly Status Call].[Project Name]
FROM [Weekly Status Call]
GROUP BY [Weekly Status Call].[Project Name];

Then see if this gets you what you want:

SELECT [Weekly Status Call].[Entered By], Projects.[Project Name], [Weekly Status Call].[Entry Date], [Weekly Status Call].[Weekly Status], Projects.[Project Manager]
FROM Projects INNER JOIN ([Weekly Status Call] INNER JOIN qryPaulBase ON [Weekly Status Call].[Weekly Status ID] = qryPaulBase.[MaxOfWeekly Status ID]) ON Projects.[Project ID] = [Weekly Status Call].[Project Name];

If so, you can add the other fields you need.

[kroenc17]

Just looked over this quick as I'm due to leave for today so I'll get more into it tomorrow AM. Few Q's at first glance..

What in my DB is the lookup field .. the ID's in the tables?

-When you say 'AS' I'm still confused as to what that is or how I do it.
-Am I grouping every column in the query, or do I only need to add the GROUP BY to the Project Name column?
-The second query is what will be the record source for the query, and as such I need to add the other fields to it, correct?

Thanks again!

[pbaldy]

If you look at the weekly status call table in design view, click on the project name field, then the lookup tab. That's a lookup field. The tip-off is that while that field is a number data type, when you look at the data in the table you see the name.

"AS" is simply giving the field an alias name. In design view, you'd see:

Whatever: FieldName

which in SQL view would look like

FieldName AS Whatever

In the first query, you'd only group on project. Those are the only two fields you want in that first query. Yes, you'd add your fields to the second query.