Distinct Count in Access 2007

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];
rustyrooAsked:
Who is Participating?
 
als315Connect With a Mentor Commented:
If you have multiple entries for [Invoice Number] for each [Sales Rep], and you like to count only unique Invoice numbers, you should at first create query, where you will Group by Invoice Number and Sales Rep. In second query you will be able to count unique Invoice Numbers. Queries could be combined in one.
Result could be:

SELECT Count(Q1.[Invoice Number]) AS [CountOfInvoice Number], Q1.[Sales Rep]
FROM (SELECT [Working-rep+acc].[Invoice Number], [Working-rep+acc].[Sales Rep]
FROM [Working-rep+acc]
GROUP BY [Working-rep+acc].[Invoice Number], [Working-rep+acc].[Sales Rep]) As Q1
GROUP BY Q1.[Sales Rep];
0
 
IrogSintaCommented:
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];
0
 
FarWestCommented:
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];
0
 
rustyrooAuthor Commented:
worked great thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.