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

Help! - How do I summarize client transactions???

P: n/a
I can see why this doen't work in a simple query, but I don't know how
to fix it...
I have a table of client transactions, (ACCOUNT_NUMBER, DATE, VALUE)

eg:

12345, 01-12-2007, 30
12345, 01-12-2007, 20
12345, 02-12-2007, 30
45678, 01-12-2007, 30
45678, 01-12-2007, 30
45678, 02-12-2007, 10
45678, 02-12-2007, 40

and I want to compile a table showing the total count of transactions
per client per month.

e.g:

12345 3 80
45678 4 110

Can anyone explain how I should go about achieving this? My four
attempts have been dismal failure...

Dec 12 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
gc******@dawsonmedia.com.au wrote in
news:35b1f6d3-f6f1-4b5f-87d1-
b1**********@y5g2000hsf.googlegroups.com
:
I can see why this doen't work in a simple query, but I don't know
how to fix it...
I have a table of client transactions, (ACCOUNT_NUMBER, DATE,
VALUE)

eg:

12345, 01-12-2007, 30
12345, 01-12-2007, 20
12345, 02-12-2007, 30
45678, 01-12-2007, 30
45678, 01-12-2007, 30
45678, 02-12-2007, 10
45678, 02-12-2007, 40

and I want to compile a table showing the total count of
transactions per client per month.

e.g:

12345 3 80
45678 4 110

Can anyone explain how I should go about achieving this? My four
attempts have been dismal failure...

Your results are missing the year+month column.

SELECT [ACCOUNT_NUMBER], Format(DATE,"yyyymm"), count(VALUE) as
TheCount, sum(VALUE) as AccountSum from thetable GROUP BY [ACCOUNT
NUMBER], Format(DATE,"yyyymm"

Hopefully your real table doesn't use field names DATE and VALUE as
these are reserved words in Access, and Access may get confused if
you attempt to use them on a form, report or code.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Dec 12 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.