Queries Charts and Forms, Oh My!

[Jeffreyb723]

I have a table with Sales Dates and Sales Price and I am using a Running Total Query to chart our Monthly Sales by Day.
I've placed the chart on a form and linked it with a Combo Box for Month and text box for year to view each Month individually.

My boss wants me to add a target line to this chart.
So I added a Table (tblMonthlyTargets) with TargetMonth, TargetYear, and TargetTotal fields

I was thinking of doing a separate Running Total Query using tblMonthlyTargets, my Month Combo Box and Year text box from the form. Each working day for the month would add to the total target for that month until we get the target we want i.e. There is 19 working days in December and our goal is $143k so our Targets would be December 2nd $7526, December 3rd $15,053...December 30th $143,000.

1. Is doing the calculation at runtime based on the forms controls an acceptable idea? (I'm more worried about space at this point than performance)
2. How would I add the running total as a separate line to the chart? (I just thought about having a Yes/No Column in a Union Query for the Combined Running Totals Flagging Yes for Sales and No for Target but haven't tried it yet so I don't even know how I could use that to separate them on the chart)

Any Ideas would be appreciated, thanks

 

[JHB]

Are you thinking of something like below?

 

[Jeffreyb723]

Yes just like that, could I get that so I can dissect how you did it?

 

[JHB]

I'll see if I can find the database, I've made some new years cleaning.
Else I'll build a new one tomorrow.

 

[JHB]

Didn't find it but created a new one, the database is attached, open the only form in it, choose the year and month from the combo box, and click the button.

 

[Jeffreyb723]

Actually got back to finishing that part of the database today. Took me a minute to tweek it into working but it does work like expected it to. Thank you so much.