Help! Monthly Summary Query (1 Viewer)

[khyeok]

Hi.

How to configure the query?
Please Help.

Table1

( Date Format : mm/dd/yyyy )

StartDate LastDate Value
01/17/2012 01/18/2012 $600
03/26/2012 04/01/2012 $700

I want to Query1.

Query1

Month SumValue
1 $600
3 $600 (3/26 ~ 4/1 = 7Day 3Month = 6Day , 4Month = 1Day)
4 $100

 

[jzwp22]

It sounds like you basically want to prorate the value if the start and last dates are in different months, but by showing the amount attributable to the fourth month, you are essentially creating a new record. The problem is a SELECT query cannot create new records, it can only select existing records.

I see two possible approaches.

First, use code that accummulates each sum by month and splitting as necessary if the start and last dates fall in different months. Will the difference in the number of months EVER exceed 1 (i.e. 3/29/2012-5/14/2012)?

Second, create a query that compares the start value to the end value. If the end value is not the same month, substitute the last day of the same month as the start value and prorate the value. Next create a query that does the same comparison but prorates based on the beginning day of the same month as the last date value. Then create a UNION query that brings the two queries' result sets together. This will only work if the difference in months between the start and last date values is less than or equal to 1.

 

[khawar]

Below is a sample attached for you

 

[recyan]

Below is a sample attached for you

Was wondering, whether we need to further extend it to take care of, for example - overlapping months in Startdate & Lastdate (the OP's data does not reflect this - but just a possibility).

eg : table1
ID StartDate LastDate Amount
1 1/17/2012 1/18/2012 600
2 3/26/2012 4/1/2012 700
3 4/2/2012 4/10/2012 500

Thanks

 

[khyeok]

Thank you.
But Query results are wrong...

MonthlySummary.mdb File Required query.

Example

StartDate LastDate Amount
01/17/2012 01/18/2012 600
03/26/2012 04/01/2012 700
04/02/2012 06/10/2012 7000
12/31/2012 01/01/2013 400

I want to Result.

Month SumValue
1 $800 (01/01/2013 = $200 + (01/17/2012~01/18/2012) = $600)
3 $600 (03/26/2012 ~ 03/31 = $600)
4 $3000 (4/1 ~ 4/30)
5 $3100 (5/1 ~ 5/31)
6 $1000 (6/1 ~ 6/10)
12 $200 (12/31 = 200)

Please Help ..

 

[khawar]

Query works on only two month but in your case it spans over more than 2 months for this you have to write vba code

 

[khyeok]

Ah.. Thank you.

perhaps without VBA the above using query have result???