Recursive Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Badg3r - 28 Jul 2006 20:11 GMT

Hello I'm fairly new to Access but remember just enough programming
from school to be frustrated that I can't figure this out.

I have a table with AccountNum and MonthNum (as a number from 1 to 12).

I need to design a query (maybe a macro?) that will give me the Count
of Accounts in Month 1 and then the Count of those Accounts that were
also in Month 2, 3, 4 and so on. The tricky part is that I then need
to do the same thing for those accounts from Month 1 that were also in
Month 2, etc.

Eventually ending up with Month as both my axis, with the count of
Accounts as the data.

"Mo1" "Mo2" "Mo3" "Mo4"
"Mo1" 3* null null null
"Mo2" 2~ 2* null null
"Mo3" 2 1~ 1* null

So you could read this as 3 accounts were present in Month 1, and of
those three, two were present in Month 2 and 3.
Of the two Accounts that were present in Month 1 and 2 (goto the 2*) 1
account was present in Month 3.
Then we take that 1 account (goto 1* and show if he was present in
Month 4, etc.

If anyone could offer any tips or links I'd really appreciate it!

Thank you,
Austin

Reply to this Message

MGFoster - 29 Jul 2006 03:34 GMT

Actually, you need a cross-tab query that gives results like this:

Month1 Month2 Month3 . . . etc.
AccountNo
1 1 2 0
2 0 1 3
3 0 0 2

Read up on cross-tab queries (aka Pivot Tables) to see how to do this.
At the least, you need 3 columns in your query: one for the x-axis (the
months), one for the y-axis (the AccountNos) and the 3rd for the
intersection of the x/y axii, which is the Count() of the accounts.
Like this (untested):

TRANSFORM Count(*) As theValue; <- the intersection value
SELECT AccountNum <- the y-axis
FROM the_table
WHERE ... criteria ...
GROUP BY AccountNum
PIVOT MonthNum <- the x-axis

HTH

Signature

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

> Hello I'm fairly new to Access but remember just enough programming
> from school to be frustrated that I can't figure this out.
[quoted text clipped - 26 lines]
> Thank you,
> Austin

Reply to this Message

Badg3r - 31 Jul 2006 19:53 GMT

Thank you for your reply!

I've given Pivot Tables a shot but can't seem to get the end result I
need.

To give some more background, I have about 30,000 account numbers.
Simplified, my data is like this:

Month Account
1 456
2 456
1 777
3 777
1 866
2 866
4 866
6 866

ending up with about 70,000 records.

This tells me that account # 456 was "active" in Month 1 and 2.
Account #866 was active in Months 1, 2, 4, and 6. etc.

I can take the active accounts from Month 1 and then get the Count of
those accounts that were active in every other month:

SELECT Count(ALLDATA.[ACCT #]) AS [CountOfACCT #], ALLDATA_1.Date
FROM ALLDATA LEFT JOIN ALLDATA AS ALLDATA_1 ON ALLDATA.[ACCT #] =
ALLDATA_1.[ACCT #]
WHERE (((ALLDATA.Month)=[Enter Month]))
GROUP BY ALLDATA_1.Month;

This gives me:

CountOfACCT #    Month
6071    1
2978    2
2720    3
2580    4
2261    5
2111    6
2292    7

The problem is that I want to get a lot more detailed than this. I
want to take that 2978 set (called 1+2) of accounts and find out how
many of them were active in the following months. Then I'd do the same
for 1+2+3, and so on up to 1+...+7. I ended up doing it the long way
with about a dozen queries, but there must be a smarter way to do it.

    1    1+2    1+2+3    1+2+3+4
1    6071
2    2978    2978
3    2720    2023    2023
4    2580    1913    1555    1555
5    2261    1688    1397    1226
6    2111    1605    1326    1174
7    2292    1639    1319    1153

Is this still something a pivot table can do for me? I'd like to be
able to start with any month, in the example above I started with Month
1.

Thank you,
Austin

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
[quoted text clipped - 65 lines]
> > Thank you,
> > Austin

Reply to this Message