Re: Find last record in date field for each device in the device f

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Last may not return what you want.
The function returns the value of a specified field in the first or last
record, respectively, of the result set returned by a query. If the query
does not include an ORDER BY clause, the values returned by these functions
will be arbitrary because records are usually returned in no particular order.

Use Max function.
--
KARL DEWEY
Build a little - Test a little


"eselk2003@xxxxxxxxx" wrote:

On Oct 6, 8:16 am, sbradley-WTI
<sbradley-...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
I have an Access 2007 db that tracks testing dates for devices. Records are
entered each time a test is done. Each record records the date tested, and
90 days out as the next due date for the next test. Each record is
associated with a device. I want to query for the last record for each
device so I can create a report based on the query to tell me when the
devices are due for their next test without seeing all the history for each
device.

You would be more likely to get a more specific answer if you provide
your table layout and relationships... but, if you don't already know
about "totals", right-click in the query designer and select the
"totals" option. This adds a totals row, and you can set your date
column to Total = "Last" to show the most recent.

Here is an example of the "Last" usage in SQL format:

SELECT [DEVICE #], Last([TEST DATE]) AS [LastOfTEST DATE]
FROM TESTS
GROUP BY [DEVICE #];

Sounds like your query will have a join in it, but I can't show you
the exact SQL statement since I don't know your table names, field
names, or what field ties them together.

.

Relevant Pages

  • Re: Report is making too many numbers
    ... from the $'s being listed more than once in the query. ... And you want to display the sum of in the Vendor header or footer ... My report is based on a query. ... those records--you could create a Totals query by depressing the ...
    (microsoft.public.access.reports)
  • Re: a query to produce sales activity totals
    ... In query design view, depress the Total button on the toolbar Access adds a Total row to the query design grid. ... number of contacts and the varous steps they have taken in the sales process. ... put these totals into a spread sheet that shows each salesperson's numbers... ...
    (microsoft.public.access.queries)
  • Re: a query to produce sales activity totals
    ... The query generates a row for each salesperson, ... Allen Browne - Microsoft MVP. ... of the sales process, including the number of sold and delivered vehicles. ... put these totals into a spread sheet that shows each salesperson's ...
    (microsoft.public.access.queries)
  • RE: Updating the 1st Instance of a Record
    ... As the others have pointed out you shouldn't store the totals but compute ... In a query you can return the total per order provided you don't ... This second query will return the total amount per order in every row of its ... and an unbound control in the group footer with a ControlSource ...
    (microsoft.public.access.gettingstarted)
  • Re: a query to produce sales activity totals
    ... Access adds a Total row to the query design grid. ... In the Total row under this field, ... Allen Browne - Microsoft MVP. ... put these totals into a spread sheet that shows each salesperson's ...
    (microsoft.public.access.queries)