By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,479 Members | 1,607 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,479 IT Pros & Developers. It's quick & easy.

Count Distinct in access while totaling up values

P: 9
I have two columns of data something like this:

A 11
A 12
B 14
C 15
C 42
C 94
D 12

I want my query to result in a distinct count of data in column 1 and sum of column 2. So result should be something like:
4 200

I think one way could be to first run a query to group by column 1 and sum of column 2 and then on resultant table run a count of column 1.

However I want to explore if there is a way within ms-acces of skipping above make a single query.

Attached Files
File Type: txt sample.txt (42 Bytes, 330 views)
Nov 16 '11 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 100+
P: 2,321
This is SQL using subqueris and it works for me.
Expand|Select|Wrap|Line Numbers
  1. SELECT Count(A) AS CountOfTxt, Sum(B) AS SumOfNo
  2. FROM (SELECT t.Txt as A,Sum(t.No) as B
  3. FROM tbl_Demo as t
  4. GROUP BY t.Txt);
Where txt is the text column and No is the number column.

If you have any questions please ask.
Nov 16 '11 #2

Post your reply

Sign in to post your reply or Sign up for a free account.