Doug, Does this work? CREATE VIEW TrailerMaxDates (TrailerID, MaxDate) AS + SELECT TrailerID,MAX(TrailerDate) FROM TrailerInfo GROUP BY TrailerID
SELECT t1.*, t2.TrailerLoc + FROM TrailerMaxDates t1, TrailerInfo t2 + WHERE t1.TrailerID = t2.TrailerID + AND t1.MaxDate = t2.TrailerDate Bill On Tue, Sep 14, 2021 at 1:32 PM Doug Hamilton <[email protected]> wrote: > I have a table of trailers, dates and locations. > I'm trying to create a view that shows the trailer ID and the most recent > location. > > > > From the above, the view would display: > TrailerID TrailerLocation > 2706 MAD > 531002 MKE > > A trailer will be at only one location on any given date. > Multiple trailers can be at a location on a date, e.g. on 8/1/21, both > trailers were in MKE. The view would display: > TrailerID TrailerLocation > 2706 MKE > 531002 MKE > > TrailerInfoID is an autonumber column to uniquely identify each row. > > This SELECT gives me the expected TrailerID and TrailerDate: > SELECT TrailerID,MAX(TrailerDate) FROM TrailerInfo GROUP BY TrailerID > TrailerID MAX (Trail > ---------- ---------- > 2706 09/13/2021 > 531002 08/01/2021 > > But I'm having trouble getting the location. > > TIA, > Doug > > > ------------------------------ > [image: Avast logo] <https://www.avast.com/antivirus> > > This email has been checked for viruses by Avast antivirus software. > www.avast.com <https://www.avast.com/antivirus> > > <#m_-3840436472341346786_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2> > > -- > For group guidelines, visit > http://www.rbase.com/support/usersgroup_guidelines.php > --- > You received this message because you are subscribed to the Google Groups > "RBASE-L" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/rbase-l/ed5bcad6-62c8-c38c-8725-3a886327dcbe%40wi.rr.com > <https://groups.google.com/d/msgid/rbase-l/ed5bcad6-62c8-c38c-8725-3a886327dcbe%40wi.rr.com?utm_medium=email&utm_source=footer> > . > -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/CAKfk%3DFpK976sZT6foi2vd%3DmdJAazWj9sDbU%3DgAFiceq41xXzSw%40mail.gmail.com.
