S
Sieve
I'm currently working on a database where I need to spread a amount [Charge]
over many months.
The key Fields here are:
[JobCodeUnique] - Primary Key
[JobCode] - system code
[Charge] - Value in USD
[Chargebilledcurrency] - Value in Local Currency
[FixedFeeDate] - Date "Sold"
[Month] - Month Number
[Year] - Year
[Sub Start] - Date to start from
[Sub Period] - The number of periods to spread over
To keep it unique I have been doing this in Excel, but it's painful & time
consuming.
What I need as an output is a record for each month in the main database, so
that when I run revenue reports, I only pick up the relevant amount.
So for example if I have a job ABC1X1234 for £120,000, Starting on 1/1/2007
til 31/12/2007, what I need are records:
JobCodeUnique Local FixedFeeDate
ABC1X1234-0107 £10,000 01/01/2007
ABC1X1234-0207 £10,000 01/02/2007
.......
ABC1X1234-1207 £10,000 01/12/2007
I can work out the months and USD versions from there!
Many Thanks
over many months.
The key Fields here are:
[JobCodeUnique] - Primary Key
[JobCode] - system code
[Charge] - Value in USD
[Chargebilledcurrency] - Value in Local Currency
[FixedFeeDate] - Date "Sold"
[Month] - Month Number
[Year] - Year
[Sub Start] - Date to start from
[Sub Period] - The number of periods to spread over
To keep it unique I have been doing this in Excel, but it's painful & time
consuming.
What I need as an output is a record for each month in the main database, so
that when I run revenue reports, I only pick up the relevant amount.
So for example if I have a job ABC1X1234 for £120,000, Starting on 1/1/2007
til 31/12/2007, what I need are records:
JobCodeUnique Local FixedFeeDate
ABC1X1234-0107 £10,000 01/01/2007
ABC1X1234-0207 £10,000 01/02/2007
.......
ABC1X1234-1207 £10,000 01/12/2007
I can work out the months and USD versions from there!
Many Thanks