Query to display a historic balance based on multiple fields

[diberlee]

Hi,

I have a database that contains details of payments returned to a bank account without sufficient information to identify who the money belongs to.

The DB is used by a team of people to retrieve details of an individual entry and when they have figured out who the money should be sent to they can mark that the payment has been "located". We can only send the refund at certain times of the month, so there is another activity "applied" where we have entered details on internal systems for the refund to be processed. Finally, once a month we physically transfer the money out of the bank account and add a "completed" date to all entries that were "applied" before that date.

My manager would like to be able to look back at a certain date to see what the balance was so he can make charts and whatever else in Excel. The balance will be all payments that were not marked as "completed" on that particular date.

I'm having trouble making a query that will give him the balance for each day in the past.

The important tables/fields here are

TBL_MAIN - dateReceived (date) and amountRefunded (currency)

TLK_LOCATED - appliedDate (date/Time) completedDate (date/Time)

TBL_DATES - activityDate - just a list of dates, which I use for a similar query of the amount we located/applied each day

So I'm looking for a query that will list all dates in tbl_dates against the sum of amountReceived up until that date, with the sum of amountReceived where completedDate is less than the date listed subtracted.

Anybody able to help with that? I haven't got a clue where to start

Cheers
Duane

 

[spikepl]

You need to supply sample data, with properly named tables and columns, and then data showing the desired output derived from the sample data. Somebody will then hopefully help you.

 

[diberlee]

You need to supply sample data, with properly named tables and columns, and then data showing the desired output derived from the sample data. Somebody will then hopefully help you.

Hi,

That's a good call, I've stripped out all unnecessary stuff from my backend and attached a sample of what's left.

So tbl_main has the amount we received and the date we received it

tlk_located has the dates that we performed various actions, the important one being the date we "completed"

So if I were to run the query from 01/07/2014 until 01/11/2014 I would expect a list of about 90 results showing the following columns:

Column 1 - Date: each date in the period selected

Column 2 - Balance: "Total Received" - "Total Completed" at that particular date

• Total Received: the sum of amountRefunded from tbl_main where the dateReceived is <= the date in column 1 (e.g on 10/09/2014 the sum of trust2PK 10,000 to 12,357)
• Total Completed: the total amount where completedDate in tlk_located is <= the date in column 1 (e.g on 10/09/2014 the sum of the 583 entries that were completed on 09/10/2014)

Hopefully that makes it easier to make sense of this.

Thanks

 

[JHB]

I can see at least 3 ways to it, but the query would be very time consuming to run I think.
Hopeful you'll get some ideas from the below.

1. With a sub query
2. With a UDF - (user defined function)
3. With DSum

 

[diberlee]

I can see at least 3 ways to it, but the query would be very time consuming to run I think.
Hopeful you'll get some ideas from the below.

1. With a sub query
2. With a UDF - (user defined function)
3. With DSum

Thanks for the tips, I'll take a look at those. A UDF is probably the one I'll focus on... I never thought of doing that. For now I've outsourced the work to Excel as I had an impatient manager pestering me. I'm sure you know how they can be...

I did try a DSum to add up all amountRefunded from tbl_main where dateReceived is <= activityDate and that worked quite nicely, but a bit slow.

I got stuck trying to use the same method for the amount marked as "completed" at a certain date though. Doesn't a DSum have to be on a table? Would I need to merge the info from tbl_main and tlk_located into a temporary table to use DSum?

In the end I made a couple of queries to tell me how much was received/completed on each date, then used some SUMIFs in Excel to add up the amounts prior to a certain date.

Regards