Summing data between two dates (when the dates are different per record)

[Gooford]

Hi,



I have a master table which shows all transactions per record (person) over a financial year.

Each record person has a seperate package period over which their spend needs to be measured. Therefore although I have all their transactions for the year, I only want to sum their transactions between their given [start date] and [end date] which are in columns.

I need to be able to create a field which sums all expenditure per record between the start and end dates

Name Start Date End Date Invoice Date Amount

Matt 15/5/11 15/9/11 1/11/11 £100
Matt 15/5/11 15/9/11 7/7/11 £200
Matt 15/5/11 15/9/11 12/12/11 £200

In this case I would only want to sum 7/7/11 as this is between the start and end dates

I want to write something like sumif([Invoice Date] is between [start date] and [end date] - not sure where or how exactly



(the start date and end date will always be the same per person)

Is this possible in access?

Thanks and regards

[hertfordkc]

How about putting a calculated field in a query. The calculated value is zero if outside the date range and the actual value if within the date range. Then you could run a sum on the calculated field.

[Gooford]

How about putting a calculated field in a query. The calculated value is zero if outside the date range and the actual value if within the date range. Then you could run a sum on the calculated field.

Hi,

Thanks. How would I do that? (I'm fine with Excel but Access is fairly new to me)

I've worked out how to do simple calculated fields but not what syntax to use to put in, if statements etc

[hertfordkc]

I'm not on my Access computer, so this is unchecked. I use the query design view and datasheet view to check on results as I get into more complicated calculations.
Say your query has fields, D1,D2,DT, Val1
In the design view, show those fields and add field NewV.
In the NewV field, NewV:iff(([DT]>=[D1]) AND [(DT]<=[D2]),Val1,0)
That should give you a zero if outside the date range or a value if in the date range.
Once you get that, add a sum on the NewV column and you are done.

[Gooford]

I'm not on my Access computer, so this is unchecked. I use the query design view and datasheet view to check on results as I get into more complicated calculations.
Say your query has fields, D1,D2,DT, Val1
In the design view, show those fields and add field NewV.
In the NewV field, NewV:iff(([DT]>=[D1]) AND [(DT]<=[D2]),Val1,0)
That should give you a zero if outside the date range or a value if in the date range.
Once you get that, add a sum on the NewV column and you are done.

Thanks for helping. For some reason I am getting an #error message in the calculated field when I run the query

[Gooford]

Hi,

The Column I create is returning an error -

Expr1: IIf(([PAYMENT_TIME]>[Review Date]),0,[Amount])


The Review Date comes from one table (Alphalist) and the rest of the info comes from Invoices table. They are linked by and ID number.

The calculated field is returning #error and I cannot work out why. I can get other expressions to work, e.g if amount is less than 1000 then return 0, or if greater than then return 1.

Any help much appreciated

[RayMilhon]

What are the Data types for PAYMENT_TIME and [Review Date]? Is there a [Review Date] field in the Invoices table? Can you put the Data structure for the 2 tables here?

[Gooford]

PAYMENT_TIME had a date and time:-

04/04/2011 15:57:17


Review Date is just a date:-

14/4/12

I changed both fields in the properties to be SHORT DATE format but this hasnt stopped the error

[Gooford]

No there isnt a Review Date field in the Invoices table

[RayMilhon]

temporarily Add the 2 fields to the query and make sure that the data matches what you think it should. Other than double checking the data I don't see anything wrong with your formula.

[hertfordkc]

Hi,

The Column I create is returning an error -

Expr1: IIf(([PAYMENT_TIME]>[Review Date]),0,[Amount])


The Review Date comes from one table (Alphalist) and the rest of the info comes from Invoices table. They are linked by and ID number.

The calculated field is returning #error and I cannot work out why. I can get other expressions to work, e.g if amount is less than 1000 then return 0, or if greater than then return 1.

Any help much appreciated

--------------------------------------------------------------------------------------------------------------------------
that 1) you qualify the field names with their table names, and
2) if there is any chance of any of the fields being null, use the NZ function, e.g.
iif((NZ(Tables![Invoices]![Payment_Time])>NZ(Tables![AlphaList]![Review Date]),0,NZ(Tables![Invoices]![Amount]))
(Careful, I may have butchered the matching parenthesis)