Question on Creating a Chart with Total Value on a Particular Date

Hi:
I am trying to create a simple 2-D bar chart in Access 2010.  There are several thousand records with the following fields:  [Effective_Date], [Cancel_Date], and [Amount] ($).  Records represent contracts that may span 1 to 30 years between Effective_Date and Cancel_Date.  What I am looking to do is show on December 31 of each year (from 1986 to current year)  the total dollar value of contracts in effect on that day using the chart tool.

I have created charts for gains & losses in any one year but can't seem to put my head around this.

Any help is appreciated.
Charlie
cwbarrettAsked:
Who is Participating?
 
Dale FyeCommented:
Rather than actually creating a table (tbl_Years).  I use a table (tbl_Numbers) which contains a single column (lngNumber) and 10 rows, containing the values 0-9.  with this table I can create a query (qry_Years) which contain any number of dates, similar to:

SELECT 1986 + Tens.lngNumber * 10 + Ones.lngNumber
FROM tbl_Numbers as Tens, tbl_Numbers as Ones
WHERE 1986 + Tens.lngNumber * 10 + Ones.lngNumber <= Year(Date())

This would give you the years 1986 - 2018

Using this technique, with a single table (tbl_Numbers), I can create numeric values starting at zero and going to 99.  Or, by adding another number to it, you can make that range pretty much anything you want.

HTH
Dale
0
 
Dale FyeCommented:
The way I would address this is with what is called a Tally Table.  Although I would do it slightly differently, you need a table which has the years 1986 - 2018 (or later), we will call this tbl_Years, with field "yr".  Then you create a query that looks like:

SELECT tbl_Years.yr, sum(yourTable.Amount) as AsOfYrEnd
FROM tbl_Years, yourTable
WHERE yourTable.Effective_Date <= DateSerial(tbl_Years.yr, 12, 31)
AND yourTable.Cancel_Date >= DateSerial(tbl_Years.yr, 12, 31)
GROUP BY tbl_Years.yr
0
 
Gustav BrockCIOCommented:
This will take some steps:

First, create a query that will list your contract years:

SELECT DISTINCT 
    Year(DMin("[Effective_Date]","[Contract]"))+10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10) AS [Year]
FROM 
    MSysObjects AS Uno, 
    MSysObjects AS Deca
WHERE 
    Year(DMin("[Effective_Date]","[Contract]"))+10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10) <= 
        Year(DMax("[Cancel_Date]","[Contract]"));

Open in new window

Save this as ContractYear.

Then, create and save a helper-function, that will calculate the yearly share of the total budget:

Public Function AmountShare( _
    ByVal StartDate As Date, _
    ByVal EndDate As Date, _
    ByVal BudgetYear As Integer, _
    ByVal Amount As Currency) _
    As Currency

    Dim Budget      As Currency
    Dim Primo       As Date
    Dim Ultimo      As Date
    Dim Days        As Integer
    Dim AllDays     As Integer
    
    Primo = DateSerial(BudgetYear, 1, 1)
    Ultimo = DateSerial(BudgetYear, 12, 31)
    
    If Primo <= StartDate And EndDate <= Ultimo Then
        ' One year or less.
        Budget = Amount
    ElseIf StartDate > Ultimo Or EndDate < Primo Then
        ' Outside this year.
        Budget = 0
    Else
        AllDays = DateDiff("d", StartDate, EndDate)
        Days = DatePart("y", Ultimo)
        
        If StartDate < Primo And Ultimo < EndDate Then
            ' Full year.
        ElseIf StartDate >= Primo And Ultimo < EndDate Then
            ' Partial year.
            Days = Days - DatePart("y", StartDate)
        ElseIf StartDate < Primo And EndDate < Ultimo Then
            ' Partial year.
            Days = DatePart("y", EndDate)
        End If
        
        Budget = Amount * Days / AllDays
    End If
    
    AmountShare = Budget
    
End Function

Open in new window

Next, create a query that will list the yearly budget share for each contract using the function above:

SELECT 
    Contract.ID, 
    ContractYear.Year, 
    Contract.Effective_Date, 
    Contract.Cancel_Date, 
    Contract.Amount, 
    AmountShare([Effective_Date],[Cancel_Date],[Year],[Amount]) AS AmountYear
FROM 
    ContractYear, 
    Contract
WHERE 
    ContractYear.Year Between Year([Effective_Date]) And Year([Cancel_Date])
ORDER BY 
    Contract.ID, 
    ContractYear.Year;

Open in new window

Save this as ContractYearBudget.

Finally, create a crosstab query that will display the yearly budgets for all contracts:

TRANSFORM 
    Sum(ContractYearBudget.AmountYear) AS SumOfAmountYear
SELECT 
    ContractYearBudget.ID, 
    ContractYearBudget.Amount
FROM 
    ContractYearBudget
GROUP BY 
    ContractYearBudget.ID, 
    ContractYearBudget.Amount
PIVOT 
    ContractYearBudget.Year;

Open in new window

This will output data similar to:

Sample
Alternatively, if that is what your graph requires, simply list the sum of the yearly budgets:

SELECT 
    ContractYearBudget.Year, 
    Sum(ContractYearBudget.AmountYear) AS YearTotal
FROM 
    ContractYearBudget
GROUP BY 
    ContractYearBudget.Year;

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
cwbarrettAuthor Commented:
Thank you everyone.  Dale, your solution worked great especially with the 2nd post using date().  There was one bug however.  The Effective_Date field is never null but the Cancel_date has many Null values so the query needed "Cancel_Date Or Is Null" in that part of the SQL to get the correct totals.

Thank for your help.
Charlie
0
 
Dale FyeCommented:
Glad I could point you in the right direction, and you were able to get it to work from there.

Don't forget to close out the question.
0
 
cwbarrettAuthor Commented:
Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.