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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
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
Dale FyeOwner, Developing Solutions LLCCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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 FyeOwner, Developing Solutions LLCCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.