Help with totals function in query

  • Thread starter Thread starter Cam
  • Start date Start date
C

Cam

Hello,

I am trying to create a query from an appended table to take only the max
days from the same S/O# and Oper#, but does not know what to do. Here is the
sample of data and what I want to achieve.

Table data:
S/O# Oper# Days AppendDate
606 160 1 6/16/09
606 160 4 6/19/09
606 170 3 6/24/09
606 202 4 7/6/09
117 160 1 6/16/09
117 160 4 6/19/09
117 160 9 6/24/09
117 190 8 7/6/09
117 260 1 7/15/09
387 130 2 6/16/09
387 160 2 6/19/09
387 160 7 6/24/09
387 250 5 7/6/09

Result want to achieve:
S/O# Oper# Days AppendDate
606 160 4 6/19/09
606 170 3 6/24/09
606 202 4 7/6/09
117 160 9 6/24/09
117 190 8 7/6/09
117 260 1 7/15/09
387 130 2 6/16/09
387 160 7 6/24/09
387 250 5 7/6/09

S/O# 606 result in three records from 4 cause it is filtering and only take
the max days if the records have the same S/O# and Oper#. How to achieve
this? Thanks
 
In query design view, depress the Totals button on the Toolbar.
Access adds a Total row to the query design grid.

In the total row, accept Group By under your first 2 fields, and choose Max
under the Days field.
 
Two queries required.

First query identifies the Max days for each S/0# and Oper#
SELECT [S/0#], [Oper#], Max(Days) as MaxDays
FROM [SomeTable]
GROUP BY [S/0#], [Oper#]


Now save that query and use it to join to your table
SELECT [SomeTable].*
FROM [SomeTable] INNER JOIN theSavedQuery as Q
On [SomeTable].[S/o#] = q.[S/O#]
AND [SomeTable].[Oper#] = q.[Oper#]
AND [SomeTable].[Days] = q.[MaxDays]

It is possible that this will return more than one record if maximum number of
days for the combination of S/O# and Oper# is duplicated.n

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top