<div>
Try this for tbl (CustID, fld) to get maximum value of fld for each CustID.<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-42741931-1"><span>SELECT a.CustID, (SELECT MAX(b.fld)  FROM tbl AS b WHERE b.CustID = a.CustID)</span>
<span>FROM tbl AS a</span>
<span>GROUP BY a.CustID</span>
</code></pre>
</div>


<div>
Provide some sample data and expected result.
</div>


<div>
Hamed,<br />
<br />
it works very good.<br />
but it's running very slow.<br />
<br />
I have 5,630 records (test only)<br />
to add a running total it takes about 3 seconds (from Dale)<br />
But to the get the maximum value for each customer (the greatest amount), it takes about 9 minutes to run.<br />
Is it possible to make it faster?<br />
Thanks
</div>


frimy

<div>
If you have other needs, the ordinary Group By query can be part of the FROM clause of another (higher level) query, joined on the custid field.
</div>


<div>
I go with aikimark's comment, for this question.
</div>


<div>
aikimark, it works beautiful, it's funny I didn't think of it because I use it all the time.<br />
maybe because I was focusing on the Add Running total query.<br />
thanks
</div>


<div>
<div class="content wysiwyg-content">
I have list from all customers the daily open balance by date (I'm getting it from the Running Sum total).<br />
I need to get only the largest Open Balance for any date for each customer.<br />
<br />
I'm using created the following query.<br />
It only works with little records.<br />
But with a lot records I get an error &quot;At most one record can be returned by this subquery&quot;<br />
Thanks in advance<br />
<br />
MaxAmt: (SELECT top 1 [RunningSum ] FROM [qCustBal AddRunAmt] WHERE CustomerID=[Customer].[Cus<wbr />tomerID] ORDER BY RunningSum Desc)
</div>
</div>


I have list from all customers the daily open balance by date (I'm getting it from the Running Sum total).
I need to get only the largest Open Balance for any date for each customer.

I'm using created the following query.
It only works with little records.
But with a lot records I get an error "At most one record can be returned by this subquery"
Thanks in advance

MaxAmt: (SELECT top 1 [RunningSum ] FROM [qCustBal AddRunAmt] WHERE CustomerID=[Customer].[CustomerID] ORDER BY RunningSum Desc)

Select only the maximum amount for each customer from a list MS 2010

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.