Help with aging column

[April15Hater]

Hi,
I am trying to create aging columns using a sub-select. The query is to list each account, then age the amounts via columns. My problem is that the column it creates with the subselect shows the total amount for all accounts rather than the single one I am trying to resolve. I think I need to add an additional criterion to the WHERE clause of the sub-select, but I'm just not sure what.

SELECT tblTLP_CRSP_Outstanding.TRecsAccount, tblTLP_CRSP_Outstanding.Amount, 
(SELECT sum(Amount) FROM tblTLP_CRSP_Outstanding WHERE datediff("d", EffectiveDate, now()) < 31) as 30Days
FROM tblTLP_CRSP_Outstanding;

 

[pbaldy]

Try

SELECT tblTLP_CRSP_Outstanding.TRecsAccount, tblTLP_CRSP_Outstanding.Amount,
(SELECT sum(Amount) FROM tblTLP_CRSP_Outstanding As Alias WHERE datediff("d", EffectiveDate, now()) < 31 And Alias.TRecsAccount = tblTLP_CRSP_Outstanding.TRecsAccount) as 30Days
FROM tblTLP_CRSP_Outstanding;

 

[pbaldy]

I should have added, I probably wouldn't use subqueries. You can get the data in one swipe like:

SELECT tblTLP_CRSP_Outstanding.TRecsAccount, tblTLP_CRSP_Outstanding.Amount, IIf(datediff("d", EffectiveDate, now()) < 31, Amount, 0) As Under30

 

[April15Hater]

Oh, I like your idea way better, forget the subselects! Thanks Paul!

Joe

 

[pbaldy]

Happy to help Joe. You must be getting busy!

 

[sparks80]

I think that the latter of pbaldy's suggestions will only show each individual amount for the last thirty days, rather than the sum of amounts for the last thirty days.

I think you may be looking for the answer given in his first message.

 

[pbaldy]

I think that the latter of pbaldy's suggestions will only show each individual amount for the last thirty days, rather than the sum of amounts for the last thirty days.

I think you may be looking for the answer given in his first message.

You can sum the IIf(), so I'd use that method either way. Subqueries would likely be much slower.