Bringing together two separate sets of data into one chart?

[Messier57]

I have two tables, one which records monthly billing expenses, the other stores budget levels at different points through the year. An example might look something like the following:

ExpenseTable:
Date Cost
3/28/03 $20,000
4/25/03 $25,000
5/30/03 $17,000
6/27/03 $22,000
7/25/03 $30,000

BudgetTable:
Date Budget
3/01/03 $0
4/15/03 $38,000
6/01/03 $70,000
9/15/03 $120,000

As you can see, the dates do not necessarily coincide between the two tables.
I then have a query which generates a running sum on expenses, giving me a cumulative burn for each billing date.
What I'd like to do is graph the cumulative burn data and the budget data on the same chart to create a visual comparison of monthly burn against projected budget. I don't know how to do that however. Any attempt I make at merging the data using a query just mangles the data and the Chart Wizard doesn't appear to permit drawing from two separate sources using different x-axis values (like an XY chart in Excel).
Any thoughts?

 

[DougP]

One option "MAY" be to use a "Make Table" query and then your chart will look at one table with the results from the other two tables ?

DougP, MCP

 

[lespaul]

what do you want your final data to look like (based on the examples above)?

 

[Messier57]

The final data would be two lines on a single chart with the following data points (based on the above example):

Line 1, Cumulative Expenditures:
3/28/03 $20,000
4/25/03 $45,000
5/30/03 $62,000
6/27/03 $84,000
7/25/03 $114,000

Line 2, Budgeted Expenditures:
3/01/03 $0
4/15/03 $38,000
6/01/03 $70,000
9/15/03 $120,000

 

[Titan5]

I assume you have used total queries to get these aggregate total amounts for expense and budget. This is handy if you have two or more lines of records for any given months. You can use a union query (use SQL statement) to joint these two queries into one, called "UnionExpenseBudget".

Select *, "EX" as qryType from queryExpense
Union all
Select *, "BG" from queryBudget;

Then you have 9 records in the resulting recordset. Any record with the last field qryType as "EX" will be from queryExpense. You then use the UnionExpenseBudget query to construct your chart. BTW, I couldn't figure out how to get the cumulative amount over time from the queries off-hand. Maybe somebody else can help.