Querying Asset Availability

[SuddathAL]

I have a table that, among other things, has trailer numbers, destinations and ETA dates. For example, trailer 1234 could arrive in Atlanta on 9/2, NY on 9/18, and finally LA on 10/1. Is there a way for me to query and display only the "trailer 1234 will be available in LA on 10/1"? I have been able to do the "last of ETA" query, but I cannot figure out how to filter out the duplicates from the trailer number column. (which is what I assume I need to do).

Is there a "display only latest" formula? You guys have been a big help on other things, so I though I would start here.

Thanks in advance

 

[Norie]

What duplicates?

Does item 1234 have LA as a destination more than once?

 

[SuddathAL]

No, it will display:
Atlanta 1234 9/2
NY 1234 9/18
LA 1234 10/1

I only want the last row displayed. We have about 35 trailers that we want to track and have a report that will show where they will be empty and ready for a new load.

 

[SuddathAL]

oh, and it could have LA as a destination more than once.

 

[Norie]

One way you could do this would be to have a subquery that returns the last ETA date regardless of destination for each item.

That can be used as the criteria for the ETA field and should return what you want.

The following is pseudo SQL code, so I doubt you'll be able to copy it straight into Access:

SELECT TrailerID, Destination, ETA

FROM tblTrailerDelivery AS B

WHERE ETA = (SELECT MAX(ETA) FROM tblTrailerDelivery WHERE TrailerID = B.TrailerID)

I tested this with this table:

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>tblTrailerDelivery</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>TrailerID</TH><TH bgColor=#c0c0c0 borderColor=#000000>Destination</TH><TH bgColor=#c0c0c0 borderColor=#000000>ETA</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#eeece1>1234</TD><TD borderColor=#eeece1>Atlanta</TD><TD borderColor=#eeece1 align=right>02/09/2011</TD></TR><TR vAlign=top><TD borderColor=#eeece1>1234</TD><TD borderColor=#eeece1>New York</TD><TD borderColor=#eeece1 align=right>18/09/2011</TD></TR><TR vAlign=top><TD borderColor=#eeece1>1234</TD><TD borderColor=#eeece1>Albequerque</TD><TD borderColor=#eeece1 align=right>26/09/2011</TD></TR><TR vAlign=top><TD borderColor=#eeece1>1234</TD><TD borderColor=#eeece1>LA</TD><TD borderColor=#eeece1 align=right>01/10/2011</TD></TR><TR vAlign=top><TD borderColor=#eeece1>4567</TD><TD borderColor=#eeece1>Boston</TD><TD borderColor=#eeece1 align=right>01/10/2011</TD></TR><TR vAlign=top><TD borderColor=#eeece1>4567</TD><TD borderColor=#eeece1>Washington</TD><TD borderColor=#eeece1 align=right>02/10/2011</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

Here are the results:

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>Query9</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>TrailerID</TH><TH bgColor=#c0c0c0 borderColor=#000000>Destination</TH><TH bgColor=#c0c0c0 borderColor=#000000>ETA</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5>1234</TD><TD borderColor=#d0d7e5>LA</TD><TD borderColor=#d0d7e5 align=right>01/10/2011</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>4567</TD><TD borderColor=#d0d7e5>Washington</TD><TD borderColor=#d0d7e5 align=right>02/10/2011</TD></TR></TBODY><TFOOT></TFOOT></TABLE>