Aging Report

[Jblackbelt]

Hi all,

I'm pretty new to access. I would say my knowledge is basic level. I am trying to create an aging report where I could see the following fields:


Aging Balance #
0 - 30 days $1,200,425 42
31 - 60 days $2,300,425 32
61 - 90 days $2,300,425 14
91 - 120 days $2,300,425 32


121 - 180 days $2,300,425 32
181 - 365 days $2,300,425 32
365+ $2,300,425 32

I already have a query that pulls the data (acct #, note #, balance, and the date on exception since (which is the date it calculates the age by). I just do not know how to put it all together into a summary query.

My table is the Annual Review Table. My field for Balance is [Curr Princ Bal] and my field for the date is [On Exception Since]

Any help is much appreciated.

Thanks,

JB

[June7]

Bing: Access database aging balance

Review http://office.microsoft.com/en-us/ac...101877532.aspx

Crud! That's Excel.

This is not easy. I would not even try to build from scratch. I would buy OTS app. I have used QuickBooks (economical) and Great Plains (extremely expensive).

[Jblackbelt]

I did look around first and the only thing I found was this, but could not get the query to work correctly for some reason

http://www.ehow.com/how_5717463_crea...ts-access.html

[June7]

What happened - error message, wrong results, nothing?

Where the nested subquery shows FROM Orders o try FROM Orders AS o or drop the table alias from the subquery.

[Jblackbelt]

It gave me a syntax Union query at the AS

[June7]

Well, it isn't supposed to be a UNION query. Post your exact attempted sql.

[Jblackbelt]

I did what is below. Please excuse the errors, like I said, Access is not my strong suit, but I am trying to learn it.

(SELECT sum([Curr Princ Bal]) FROM [Annual Review] WHERE datediff("d", [On Exception Since], now) < 31) as [30 Days or Less]

[June7]

That web page has errors in the text. The &lt; probably should be <

[Jblackbelt]

Thanks I gave it a shot

SELECT sum([Annual Review].[ Curr Prin Bal Amt]) FROM [Annual Review] WHERE datediff("d", [Annual Review].[On Exception Since], now) <31 AS [30 Days or Less]

Gives me syntax error (missing operator) query expression 'datediff("d", [Annual Review].[On Exception Since], now) <31 AS [30 Days or less]'.

Highlights the AS in the statement

[June7]

Appears you dropped a paren or two. Try:

(SELECT Sum([Curr Prin Bal Amt]) FROM [Annual Review] WHERE DateDiff("d", [On Exception Since], Now) < 31) AS [30 Days or Less]

[Jblackbelt]

Thanks. gave me the union error query again. I have someone that I found that is good with access and that I will try to see if they can help me. if not I will come back around