Sum of Values if in date range

[steve.mccall1]

Hi,

I've graduated from counting records to summing values now!!

I've got the data set that is shown below

caseNo  source       Amount   Vat       Total    Date Paid
1          ted          £50         £7.50   £57.50    01/01/08

I've actually around 3000 records going back to 2004. What I need to do is make a report that will show the following.

Source    Amount (financialYear) Vat(FY) Total(FY)  Amount(thismonth) Vat..

ted            £50,000                  £10,000 £60,000    £5,000                 
Bob .....

I've created a function in the module that returns the financial year that the record is in called getFN() which works fine.

I use 2 text boxes to input the start and end date of the reports and use one of the text boxes as the input for getFN()

Basically, I can get a report that shows the values for each source in the financial year ok, and I can do the same for the values for the month selected but I can't join the two queries up in a single report.

I'm not sure if I need to have a blanket query that joins them. I'm a little stuck.

Any help would be really appreciated.

Steve

 

[jdraw]

Hi,

I've graduated from counting records to summing values now!!

I've got the data set that is shown below

caseNo  source       Amount   Vat       Total    Date Paid
1          ted          £50         £7.50   £57.50    01/01/08

I've actually around 3000 records going back to 2004. What I need to do is make a report that will show the following.

Source    Amount (financialYear) Vat(FY) Total(FY)  Amount(thismonth) Vat..

ted            £50,000                  £10,000 £60,000    £5,000                 
Bob .....

I've created a function in the module that returns the financial year that the record is in called getFN() which works fine.

I use 2 text boxes to input the start and end date of the reports and use one of the text boxes as the input for getFN()

Basically, I can get a report that shows the values for each source in the financial year ok, and I can do the same for the values for the month selected but I can't join the two queries up in a single report.

I'm not sure if I need to have a blanket query that joins them. I'm a little stuck.

Any help would be really appreciated.

Steve

Without seeing your queries, it appears that you are trying to get Financial Year and monthly breakdowns.

I suggest you look at SUM and GROUP BY options for SQL queries against your data.