greatest value
in [Queries]
|
Prev: IFF statement Repost
Next: SQL vs. VBA
From: vanderghast on 25 Jan 2010 15:10 GROUP over the wellID and the date (your first and your second fields), MAX over the last field. Vanderghast, Access MVP "Delsa_SCCD" <u57761(a)uwe> wrote in message news:a2a71da9a326f(a)uwe... > well, that doesn't seem to be enough info to help me.... let me fill you > in a > bit more. Access 2003, the current query looks like this: Field #1: Well > ID, > Field #2: date, Field #3: Time. There are a handful of different times > recorded per day, per well id..... I need the query to show me just the > latest time per day, per well id. (there is also a number of dates entered > per well id) > > Jeff Boyce wrote: >>Change your query to a Totals query. >> >>Use "Maximum". >> >>Regards >> >>Jeff Boyce >>Microsoft Access MVP >> >>> How would the critera be wrote to get a greatest value? (Not greater >>> than, >>> but just the greatest - I don't want to see any other entries) >
From: Delsa_SCCD on 25 Jan 2010 15:21 k, now I get nothing when its run...... is there a way to show a screen shot in these posts? that would probably help vanderghast wrote: >GROUP over the wellID and the date (your first and your second fields), MAX >over the last field. > >Vanderghast, Access MVP > >> well, that doesn't seem to be enough info to help me.... let me fill you >> in a >[quoted text clipped - 17 lines] >>>> than, >>>> but just the greatest - I don't want to see any other entries)
From: John Spencer on 25 Jan 2010 16:05 Step by step == Create a new query == Add WellID, the Datefield, and the timeField to the list of fields == Select View: Totals from the menu == Change GROUP BY To Max under the TimeField If that does not give you what you want then post back and tell us in more detail what is missing or wrong with what that query returns. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Delsa_SCCD wrote: > k, now I get nothing when its run...... is there a way to show a screen shot > in these posts? that would probably help > > vanderghast wrote: >> GROUP over the wellID and the date (your first and your second fields), MAX >> over the last field. >> >> Vanderghast, Access MVP >> >>> well, that doesn't seem to be enough info to help me.... let me fill you >>> in a >> [quoted text clipped - 17 lines] >>>>> than, >>>>> but just the greatest - I don't want to see any other entries) >
From: vanderghast on 25 Jan 2010 16:06 In SQL view, you should have: SELECT [Well ID], [date], MAX([Time]) FROM [tableNameHere] GROUP BY [Well ID], [date] where you use your real table name. I assumed the field names are Well ID, date and Time. If you prefer, open a new query in design view, bring your table. Click on the Summation button (to get a new row, total, in the grid). Drag the field Well ID in the grid, keep the proposed Group By. Add the field Date in the grid, keep the Group By. Bring your field Time, change the proposed Group by to Maximum. Vanderghast, Access MVP "Delsa_SCCD" <u57761(a)uwe> wrote in message news:a2a76237e3e7a(a)uwe... > k, now I get nothing when its run...... is there a way to show a screen > shot > in these posts? that would probably help > > vanderghast wrote: >>GROUP over the wellID and the date (your first and your second fields), >>MAX >>over the last field. >> >>Vanderghast, Access MVP >> >>> well, that doesn't seem to be enough info to help me.... let me fill you >>> in a >>[quoted text clipped - 17 lines] >>>>> than, >>>>> but just the greatest - I don't want to see any other entries) >
From: Delsa_SCCD on 25 Jan 2010 16:41
Success! Thank you!!!!! vanderghast wrote: >In SQL view, you should have: > >SELECT [Well ID], [date], MAX([Time]) >FROM [tableNameHere] >GROUP BY [Well ID], [date] > >where you use your real table name. I assumed the field names are Well ID, >date and Time. > >If you prefer, open a new query in design view, bring your table. Click on >the Summation button (to get a new row, total, in the grid). Drag the field >Well ID in the grid, keep the proposed Group By. Add the field Date in the >grid, keep the Group By. Bring your field Time, change the proposed Group by >to Maximum. > >Vanderghast, Access MVP > >> k, now I get nothing when its run...... is there a way to show a screen >> shot >[quoted text clipped - 11 lines] >>>>>> than, >>>>>> but just the greatest - I don't want to see any other entries) |