Query Design: Using a date range and manipulating numbers

[Danglez]

I have a query with these particular fields that are of interest to me:
CounterParty, Amount, interest, Value Date, Maturity Date, & Cashflow(Amount * Interest)

I need to be able to generate a 30 day report outlining the the positive and negative cashflow for each day.

On the Value date, I have money coming in or out which requires the use of the 'amount' field. On the Maturity date same thing but uses the 'cashflow' field.

I want a query that just list the next 30 days from now and based on whether the maturity date or the value date match that day, it will give me the numbers for it.

Any ideas? PLease let me know if you need more specific information

 

[JonFer]

Please provide some sample records and the desired output for the two situations based on the dates.

 

[Danglez]

Here is a sample of what the data looks like.
Counterparty ValueDate Maturity Date Amount Cashflow
BankA 8/9/04 8/10/04 $100 -$110
BankB 8/9/04 8/10/04 -$200 $220
BankC 8/10/04 8/11/04 $300 -$330
BankD 8/10/04 8/11/04 -$400 $440

What I need is a report that outlines the positive and negative cashflow for the next 30days.

On 8/10/04:
Based on the maturity dates of deals with Banks A & B I have negative cashflow of $110 and positive of $220 (Using the Cashflow field)

Based on the Value Dates of deals with Banks C & D I have negative cashflow of $400 and Positive of $300 (Using the Amount field)

In total my positive cashflow is: 300+220= $520
my negative cashflow is: 110+400= $510
my net cashflow is hence: 520-510= $10

On 8/11/04 Based on the Maturity Dates of deals with Banks C & D my positive cashflow is 440 and negative is 330 (using Cashflow field). SO my net on that day would be $110.


The basic format of the report/Query I want is this:

Date PositiveCashflow NegativeCashflow NetCashflow
8/10/04 520 510 10
8/11/04 440 330 110
etc...

I must warn that I am an access newbie so I don't know too much about VBA coding, I'm just doing this as a project for a summer internship, but I am willing to try and figure things out.