I have a table that has the following data in it
"Deal Number" "Profile 1 Amount" "Profile 1 Date" "Profile 2 Amount" "Profile 2 Date" all the way through to "Profile 10 Amount" "Profile 10 Date"
The profile date could be anything from today to a date in 10 years in time and can be different for each deal number (so Profile Date 1 is not the same for all deals)
I want to create a query that can sum the (profile) amounts into half yearly buckets. So I would get the following:
Deal Number Half 2 2010 Amount Half 1 2011 Amount Half 2 2011 Amount
Where the Half 2 2010 Amount is a sum of all the Profile Amounts (from 1-10) where the Profile dates (from 1-10) are between #1/7/2010# and "31/12/2010"
I then want to do the same for each half year up to 2020
Are there any quick/clever ways of doing this - I am stumped!
Thanks for your help
"Deal Number" "Profile 1 Amount" "Profile 1 Date" "Profile 2 Amount" "Profile 2 Date" all the way through to "Profile 10 Amount" "Profile 10 Date"
The profile date could be anything from today to a date in 10 years in time and can be different for each deal number (so Profile Date 1 is not the same for all deals)
I want to create a query that can sum the (profile) amounts into half yearly buckets. So I would get the following:
Deal Number Half 2 2010 Amount Half 1 2011 Amount Half 2 2011 Amount
Where the Half 2 2010 Amount is a sum of all the Profile Amounts (from 1-10) where the Profile dates (from 1-10) are between #1/7/2010# and "31/12/2010"
I then want to do the same for each half year up to 2020
Are there any quick/clever ways of doing this - I am stumped!
Thanks for your help
