Query Design help?

[K]

Good day,

I am not sure how to do this but here is what I would like to do based on a
query.

I have a table with the following information and field names
CNTRNUM CNTRDESC LastOfSTATUS DATE APPEND_TS MaxOfTIME_STAMP NOTE
321 CBIN 3230 Available 5/6/2008 5/7/2008 8:34:14 AM 5/6/2008 11:00:36 AM
322 CBIN 3230 Available 5/6/2008 5/7/2008 8:34:14 AM 5/6/2008 11:00:36 AM
323 CBIN 3231 Available 5/6/2008 5/7/2008 8:34:14 AM 5/6/2008 11:44:58 AM
323 CBIN 3230 Available 5/7/2008 5/7/2008 8:34:14 AM 5/7/2008 8:29:57 AM
323 CBIN 3230 Out 5/6/2008 5/7/2008 8:34:14 AM 5/6/2008 11:00:44 AM Delphi

So to summarize this information CBIN 3230 has a few records however the
last known status is "Available" as dictated by the Append Time Stamp
(APPEND_TS) how do I filter this record out to be showing in a report.
Basically I want to only show the latest status of a contianer by the
APPEND_TS.

Any help would be appreciated.

Thanks for your time.

 

[Jeff Boyce]

Have you looked into using the "TOTALS" query type (a button on the toolbar
that looks like a "sigma" character)?

You may be able to use the "Max" aggregation to get the most-recent
date/time.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[K]

Hi Jeff,

Yes this is what i did. in an append query to another table I set the Time
Stamp to Max of entry which is =Now() by default when the record is created
from the form into the table and appended it to another table where there is
no Duplicates allowed (same info). This way it only appends the latest status
of a specific item (323) and does not repeat. I had to set the warnings to no
so I did not get messages from the query that I cannot append all....blah
blah.

THis may not be the best way but it works.
Cheers Kyle.

 

[John Spencer]

You should be able to do this using a subquery in the FROM clause.

SELECT [YourTable].*
FROM [YourTable] INNER JOIN
(SELECT CntrDesc, Max(Append_TS) as LastOne
FROM YourTable
GROUP BY CntrDesc) as B
ON YourTable.CntrDesc = B.CntrDesc AND
YourTable.Append_TS = B.LastOne

This will work as long as your field and table names consist of ONLY Letters,
Numbers, and the underscore character (no spaces, punctuation, etc).

If your table and field names don't follow the rule outlined above, then you
can do this in TWO queries.

Query one - a totals query grouping on CntrDesc and getting the Max of Append_TS)

Query Two - your table and query one joined on the CntrDesc fields and the
date fields (Append_TS & MaxOfAppend_TS)


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County