<div>
You just need to group by the Invoice Number as well.<br />
<br />
SELECT [Working-rep+acc].[Sales Rep], [Working-rep+acc].[Invoice<wbr /> Number], Count ([Working-rep+acc].[Invoic<wbr />e Number]) AS &nbsp;[CountOfInvoice Number]<br />
FROM [Working-rep+acc]<br />
GROUP BY [Working-rep+acc].[Sales Rep], [Working-rep+acc].[Invoice<wbr /> Number];
</div>


You just need to group by the Invoice Number as well.

SELECT [Working-rep+acc].[Sales Rep], [Working-rep+acc].[Invoice Number], Count ([Working-rep+acc].[Invoice Number]) AS  [CountOfInvoice Number]
FROM [Working-rep+acc]
GROUP BY [Working-rep+acc].[Sales Rep], [Working-rep+acc].[Invoice Number];

<div>
in access you should use sub-query for your select that contains the distinct select<br />
something like<br />
<br />
SELECT t.[Sales Rep], Count (t.[Invoice Number]) AS &nbsp;[CountOfInvoice Number]<br />
FROM (<br />
SELECT distinct [Working-rep+acc].[Sales Rep], [Working-rep+acc].[Invoice<wbr /> Number]) AS t <br />
GROUP BY [Working-rep+acc].[Sales Rep];
</div>


in access you should use sub-query for your select that contains the distinct select
something like

SELECT t.[Sales Rep], Count (t.[Invoice Number]) AS  [CountOfInvoice Number]
FROM (
SELECT distinct [Working-rep+acc].[Sales Rep], [Working-rep+acc].[Invoice Number]) AS t
GROUP BY [Working-rep+acc].[Sales Rep];

<div>
<div class="content wysiwyg-content">
Hey guys,<br />
<br />
Need to count distinct invoice numbers in the below query. &nbsp;Have tried lots of ways to no effect... can someone please assist.<br />
<br />
thanks<br />
<br />
Rusty<br />
<br />
<br />
SELECT [Working-rep+acc].[Sales Rep], Count ([Working-rep+acc].[Invoic<wbr />e Number]) AS &nbsp;[CountOfInvoice Number]<br />
FROM [Working-rep+acc]<br />
GROUP BY [Working-rep+acc].[Sales Rep];
</div>
</div>


Hey guys,

Need to count distinct invoice numbers in the below query.  Have tried lots of ways to no effect... can someone please assist.

thanks

Rusty


SELECT [Working-rep+acc].[Sales Rep], Count ([Working-rep+acc].[Invoice Number]) AS  [CountOfInvoice Number]
FROM [Working-rep+acc]
GROUP BY [Working-rep+acc].[Sales Rep];

Distinct Count in Access 2007

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.