CODEWITHSUNDEEP
sqlms-accessgraphpivot
Awais
Awais

Reputation: 11

MS ACCESS -> Dynamic pivot chart

I'm trying to build a database for a small company to record its expenses and then generate reports to analyse the expenditure. I'm having trouble with one report that I want to do. I'd like the report to compare time periods as specified by the user and present the data on a graph.

For example, the data is structured as follows:

Date -- Expense Classification -- Amount

Now, I'd like the user to be able to select two time periods through a query for comparison purposes and show the total amounts spent on each expense classification during those two periods and graph them on a pivot chart.

So, for example the query will allow the user to say, choose to compare the following period (Period 1) 01-04-12 to 15-04-12 and (period 2) 15-04-12 to 30-04-12. The report should now arrange the data so it is grouped by expense classification (examples include utility bills, stationery expenses, petty cash expenses, general expenses etc.) and provide the total amount spent as per each classification in both time periods.

Can someone please help me out with this. I've tried using cross-tab queries but I can't limit the data to show only the periods I want. I've tried Union queries and still don't get the result I need. I've tried building report based on several different queries as well and can't get the graph to plot the data properly.

Any help on this would be appreciated.

Thanks

Upvotes: 1

Views: 903

Answers (1)

Fionnuala
Fionnuala

Reputation: 91376

The easiest thing to do is to create the chart as you wish and then change the sql of the query on which it is based according to the required period.

So say it is:

SELECT stuff FROM Table

It becomes

sSQL= "SELECT stuff FROM Table WHERE SDate =#" & sDate & "#"
Set qdf = CurrentDB.QueryDefs("MyChart").SQL = sSQL

Upvotes: 1

Related Questions