Sum based on < and > values

[bibbyd01]

Hi

I'm busy learning access but I've come up against a bit of a problem when constructing a report.

I have a query which my report links to with the following columns that I draw through to my report; Value, Currency, AdminOwner, Arrears

At the moment, the report is split by AdminOwner, then Currency, summing the currency per admin owner. I want to add in several columns based on the arrears days. So, for instance, I want to keep the current layout, but, after the total, I'd like to have a less than 31, 31 to 60, 61 to 90 etc.

How can I go about doing this? I can do this all in Excel before uploading the table, but the table then needs at extra 12 columns, which is something I don't want to have to create.

 

[DCrake]

You would do this via a query.

In your query you would add the first new column that calculates the age of the debt. Then in the subsequent columns you would add the calculation.

AgeOfDebt: DateDiff("d",DateLower,DateUpper)


Aged A:IIF(AgeOfDebt<31,Arrears,0)

Aged B:IIF(AgeOfDebt >=31 Or AgeOfDebt <=60,Arrears,0)

Aged C:IIF(AgeOfDebt >=61 Or AgeOfDebt <=90,Arrears,0)

etc

etc

This way you would end up with a column for each aged debt range with the value appearing if it matches the True condition otherwise it returns 0.

David

 

[bibbyd01]

hi

Not to sound too stupid, but What parts of the column would I put those expressions into? Would AgeofDebt be in the Field name, and DateDiff("d",DateLower,DateUpper) be in the criteria? Or is it all in the Field name/criteria?

 

[DCrake]

When you want to add an unbound column to a query you need to go to the next blank column and in the field name row you enter the expression there.

So in this example

AgeOfDebt: DateDiff("d",DateLower,DateUpper)

would appear in the field name row (top)

Whereby DateLower and DateUpper are the two parts of the equasion that calculates the difference in days between them. This could be Invoice Date, Payment Due Date, whatever you decide.

In the next blank column you would "query" the previous column and return the answer.

Aged B:IIF(AgeOfDebt >=31 Or AgeOfDebt <=60,Arrears,0)




David

 

[bibbyd01]

Ok, I'm understanding this, I think, but I'm still puzzled over this particular query. I already know what the arrears days are, and this solution would let you work out the arrears days based on two dates (unless I have this completely wrong).

So, if I already had a column for arreas, would I write an expression like; UpTo30: IIF(Arrears <=30,Arrears,0)

 

[DCrake]

Got it in a nutshell

This however would return the number of arrear days not the value of the arrears. I assume you are trying to create an Aged debtors report.

To return the value of the invoice or balance

UpTo30:IIF(Arrears<30,Amount,0)

Where amount is the field that contains the value outstanding.

David

 

[bibbyd01]

I figured this out about three seconds after I posted (and noticed the arrearsdays appeared where the value should have).

I've also replaced the or with an and to get the figures I wanted (I didn't explain what I was trying to do too well earlier) so all is working as it should, and I can finally go and build the report I want now! Thanskf or your help!