Need to find latest date in a query
in [Queries]
|
From: DPCpresto via AccessMonster.com on 27 Jun 2008 18:35 My query has two fields [prop_id] and [comsdate] where the comsdate field is a date that periodic, recurring maintenance was done on a certain property. In some instances, the property is listed twice because I had used a criteria of >= 1/1/2008. My problem is that i want the query to list all properties that I manage, but, in those instances where the property may show twice, I only want the LATEST date to show in the query. I then use these results to schedule the next time maintenance needs to be done by using [comsdate]+120. I just can't seem to get the multiple listings out. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200806/1
From: Jeff Boyce on 27 Jun 2008 19:08 Have you looked at using a "Totals" query, Grouping By [prop_id] and using Max for [comsdate]? Regards Jeff Boyce Microsoft Office/Access MVP "DPCpresto via AccessMonster.com" <u44264(a)uwe> wrote in message news:8652057466112(a)uwe... > My query has two fields [prop_id] and [comsdate] where the comsdate field > is > a date that periodic, recurring maintenance was done on a certain > property. > In some instances, the property is listed twice because I had used a > criteria > of >= 1/1/2008. My problem is that i want the query to list all properties > that I manage, but, in those instances where the property may show twice, > I > only want the LATEST date to show in the query. I then use these results > to > schedule the next time maintenance needs to be done by using > [comsdate]+120. > I just can't seem to get the multiple listings out. > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200806/1 >
From: KARL DEWEY on 27 Jun 2008 19:09 Use a Totals query and change the group by below the [comsdate] field to Max. -- KARL DEWEY Build a little - Test a little "DPCpresto via AccessMonster.com" wrote: > My query has two fields [prop_id] and [comsdate] where the comsdate field is > a date that periodic, recurring maintenance was done on a certain property. > In some instances, the property is listed twice because I had used a criteria > of >= 1/1/2008. My problem is that i want the query to list all properties > that I manage, but, in those instances where the property may show twice, I > only want the LATEST date to show in the query. I then use these results to > schedule the next time maintenance needs to be done by using [comsdate]+120. > I just can't seem to get the multiple listings out. > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200806/1 > >
|
Pages: 1 Prev: Converting text to a number in query Next: Running Sum Error in Query |