Find last record in date field for each device in the device field

[sbradley-WTI]

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.

 

[eselk2003]

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.

 

[KARL DEWEY]

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

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.

 

[John Spencer]

Actually get the MAX of the date not the Last. Last will give you unexpected
results - often it will give you the last record added to the table, but often
it will not. It gives you the last record of an ordered set, but you don't
have an ordered set of records in the query.



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

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.

 

[sbradley-WTI]

(e-mail address removed),

Thanks for your reply. You were right, I did not realize the TOTALS could
be used that way. However, that did not fix my problem when I tried it. I
will provide the table info below with relationships to help you answer my
question better. BTW, I don't know SQL language or VBA (taking VBA for Apps
next week), so for now am relegated to copy/paste of SQL examples given to me
or just what Access 2007 writes automatically for me.

TABLES: Light Source Calibrations, Light Sources
FIELDS in Light Source Calibrations Table: (KEY)ID, Light Serial Numbers,
Date Checked, Test Meter Used, Reading in fc's, Next Due Date, Tested by,
Apprv'd?, Modified?, Comments
FIELDS in Light Sources Table: (KEY) Light Serial Number, Technician, Lab
Location, Manufacturer, Model
db RELATIONSHIPS: ONE [Light Sources].[Light Serial Numbers] to MANY [Light
Source Calibrations].[Light Serial Numbers]
QUERY JOIN PROPERTIES: >>Left tbl/Left Column--Light Sources/Light Serial
Numbers; >>Right tbl/Right Column--Light Source Calibrations/Light Serial
Numbers; >>Option 1 is selected; Only include rows where the joined fiedls
from both tables are equal (this was auto-selected for me)

Hope this helps!

Scott

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.

 

[sbradley-WTI]

Thanks, John. Your tip got me what I needed.

Actually get the MAX of the date not the Last. Last will give you unexpected
results - often it will give you the last record added to the table, but often
it will not. It gives you the last record of an ordered set, but you don't
have an ordered set of records in the query.



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

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.