Make a cash flow

[George-Bowyer]

I have a table of data for donations given by people to an organisation

The fields are ContactNumber, Amount, Frequency, StartDate and FinishDate.

Frequency can be 1 (Monthly), 2 (Annually) or 3 (One-off).

I want to generate a cash-flow forecast by month from a given start date to a given end date.

So really I want to generate a snapshot "spreadsheet" report with a column for each month and a row for each donor, with totals for rows and columns.

I have no idea how to start - in fact I don't even know if it is possible from the data I have...

I don't particularly want to keep a massive table. I don't need historic data, I just want to generate future predictions.

Can anyone point me in the right direction, please?

Thanks.

 

[plog]

A query cannot generate data it does not have access to. That means if you want data for every month, then you need a table to store that. For example, suppose you had 1 record, and it was a monthly contribution starting 1/1/2016 and going until 4/30/2016, to generate a you would need a datasource that told the query all the months from January to April. Do you have that?

Actually, a better way to start is from the end. Can you show me what you hope the final product looks like?

 

[MarkK]

How do you account monthly for an annual gift? How does that math work? Do you take the annual gift, and divide by 12? if so, why have an annual gift for forecasting purposes at all?

 

[George-Bowyer]

How do you account monthly for an annual gift? How does that math work? Do you take the annual gift, and divide by 12? if so, why have an annual gift for forecasting purposes at all?

The data is imported so I have to work with what I have got.

I am just looking for cashflow - so a monthly gift shows up every month, an annual gift shows up only in the relevant month, a one-off shows up once...

Thanks

 

[George-Bowyer]

A query cannot generate data it does not have access to.

Yeah, that's what I was worried about. I thought it might not be possible.

That means if you want data for every month, then you need a table to store that. For example, suppose you had 1 record, and it was a monthly contribution starting 1/1/2016 and going until 4/30/2016, to generate a you would need a datasource that told the query all the months from January to April. Do you have that?

No. All I have is the information as above, which is imported from elsewhere. I have no control over the format.

Actually, a better way to start is from the end. Can you show me what you hope the final product looks like?

The best way I can describe it is as a spreadsheet with 12 or 24 months along the top, a row for every donor and a cash entry for each month that the donor makes a donation (so a monthly donor will have an entry in every month, and annual donor once every 12 months and a one-off only once).

I am not being asked to keep records of actual money taken (just forecasts on promises that may or may not happen in reality).

I suppose I could use code to generate and then populate a table every time I want a forecast, but it seems awful clunky...

 

[plog]

Can you provide actual data as an example? I would need 2 sets:

A. Starting data from your table. Include table and field names and enough data to cover all cases.

B. Expected results based on A. Show me what the resulting data would be based on the data you start with in A.

You can do this in excel and post it here.

 

[George-Bowyer]

Okey doke.

Example attached

many thanks

 

[plog]

Think I got it--or as close as you are going to get. Attached is a database with your solution. Here's some issues:

1. You didn't provide your table name, so I used 'YourTableNameHere'. To use your actual table name you will need to replace 'YourTableNameHere' with your table's name in the CashFlow Module--it appears once.

2. To get field values as column names in Access you need a cross-tab query. A cross-tab query is a horrible thing to make an actual Access Report off of (because its field names are subject to change).

The closest you are going to get to your desired ouput is via Excel. You would run the CashFlow_sub1 query, paste it into Excel, then generate a pivot table and format that to what you want.

Let me know if you have any question

 

[George-Bowyer]

Hi, Plog,

This is awesome. You are very kind to go to all this trouble.

Being very aware of not looking gift horses in the mouth, the module does not appear to be working completely as I think you intended.

It is returning all of the annual payments in January, instead of in the actual months projected.

The one-off payment is not showing up at all.

Many thanks,

George

 

[arnelgp]

another alternative version. please run qryCashFlow.

 

[arnelgp]

i think there is problem with the first, please test again.

 

[plog]

My version of the CashFlow database is working. Are you running the CashFlow query?

For PatronID=9 (the annual one), I show a 300 amount for 4/1/2016 and 4/1/2017, the rest of the months are 0.

For PatronID=7 (the one-off), I show a 1000 amount in 3/1/2016 and the rest are 0.

Is that not what you are getting? Can you post a screenshot?

 

[George-Bowyer]

My version of the CashFlow database is working. Are you running the CashFlow query?

For PatronID=9 (the annual one), I show a 300 amount for 4/1/2016 and 4/1/2017, the rest of the months are 0.

For PatronID=7 (the one-off), I show a 1000 amount in 3/1/2016 and the rest are 0.

Is that not what you are getting? Can you post a screenshot?

I think we might have an "across-the-pond" error here.

My dates are dd/mm/yyyy - I suspect you might be using mm/dd/yyyy...?

 

[George-Bowyer]

i think there is problem with the first, please test again.

Hi, arnelgp,

Yep this is awesome, thanks.

I thought I knew a bit of code, but I have read the code and SQL thoroughly - and I have absolutely not clue what you have done there or how it works...

I'm putting it down to magic... (same goes for Plog's solution, too...)

 

[plog]

I suspect you might be using mm/dd/yyyy...?

Correct. I don't know what to do to fix it though. Does arnelgp's solution work for you? Is this resolved?

 

[George-Bowyer]

Correct. I don't know what to do to fix it though. Does arnelgp's solution work for you? Is this resolved?

Yes and yes.

Thank you both very much indeed.

 

[George-Bowyer]

Hi, arnelgp,

i think there is problem with the first, please test again.

This issue isn't as solved as I thought it was.

My application is in an access front end linked to sharepoint tables.

When I run the code as is, I get "Runtime error 3219: Invalid Operation" on the line:

Set rs = db.OpenRecordset("tblPatrons", dbOpenTable)

I am aware that there is a problem of some sort with linked tables and dbOpenTable.

I've tried changing it to dbOpenDynaset, I get "Runtime error 3251: Operation is not supported for this type of object" on the line:

rs.Index = "PatronID"

I think that this is because you can't use seek on a dynaset, but I don't know what to do about it?

 

[George-Bowyer]

I resolved this by changing opentable to opendynaset and using findfirst instead of seek.

Takes a heck of a lot longer to run the query, though...