Why do records disappear?

[dfoster]

This seems simple, but I'm stumped. I have a query. Here is the design and resulting datasheet:

The problem arises when I add Totals. Here is the updated design and resulting datasheet:

In the datasheet with Total added, why aren't X3613 and X3991 appearing?

[June7]

Date is a reserved word. Should not use reserved words as names for anything. Unexpected things can happen. May or may not be cause here.

Otherwise, see nothing wrong. Post SQL statement. If you want to provide db for analysis, follow instructions at bottom of my post.

[dfoster]

Thank you. I updated two fields that were named using "reserved words." Query still not working. Attached (as per your instructions) for analysis. The query in question is "MAXodo_FY19_Q3".

[moke123]

re-read question and deleted.

[isladogs]

First & Last don't always produce what you expect. Max/Min are more reliable
In this case, you should use Where instead of Last for the Date field in the aggregate query.

SELECT Maintenance.License, Max(Maintenance.Odometer) AS MaxOfOdometer FROM Maintenance
WHERE (((Maintenance.DateOfService) Between #1/1/2019# And #3/31/2019#))
GROUP BY Maintenance.License
ORDER BY Maintenance.License;

Doing so gives the expected 9 records instead of the 7 you had

[June7]

Ooops, case of seeing what you expect - I did not notice the Last. I agree, Last and First are not reliable in most situations.

[dfoster]

Wow. Yes, that works, thank you very much!

I suppose as a side note.... As a person just learning this stuff, how common is it that "X doesn't work like you expect"? I feel like that's going to be a real stumbling block in learning to use Access, if I can expect to keep running into things that logically seem as though they'd work a certain way, but turn out not to....

[Micron]

If I may, when you're presented with something that you've never reviewed for its usage, meaning, intent, etc. in Access, don't just use it - research it. While doing so with Last may not have revealed what's been told here, so many times people grab code, functions and methods and try to use them with no understanding of what those methods are supposed to do. However, even something as inconspicuous as Last ought to be researched. In other words, assume nothing and you will learn more and learn it faster than stumbling through an exercise.

[dfoster]

If I may, when you're presented with something that you've never reviewed for its usage, meaning, intent, etc. in Access, don't just use it - research it. While doing so with Last may not have revealed what's been told here, so many times people grab code, functions and methods and try to use them with no understanding of what those methods are supposed to do. However, even something as inconspicuous as Last ought to be researched. In other words, assume nothing and you will learn more and learn it faster than stumbling through an exercise.

Good advice, thank you.