Chart Reference Line

[June7]

Did you try as suggested in my earlier post?

[isladogs]

@JonathanT
How about this?



As you can see, I've used a combo modern chart to do this
The column chart data is in one table and the fixed line in a separate table which I've combined in a cartesian query

Attached is your database with this modification

[isladogs]

In fact it can be done without a second table or caertesian query. Instead use a TempVar.

Change the CholesterolQry SQL to

SELECT BloodData.CurrDate, BloodData.CholesterolTotal, BloodData.HDLCholesterol, BloodData.[LDL Cholesteral], CInt([TempVars]![RefLine]) AS RefLine FROM BloodData;

I've removed the unnecessary grouping

Then use this code in the Form_Load event of the form containing the chart:

Private Sub Form_Load()  
     TempVars!RefLine = 200
End Sub

This sets the value for use by the chart. QED!

[JonathanT]

I have solved my problem. I added fields in the table that indicates the value of each reference range and the the value is the same for every record. I added that field to the query for the chart. This gives me a straight line to reference the results of each chart. In Chart Settings I edited the Display Name to show the value the line represents. I've attached the updated DB

BloodTestsUpdate.zip

[JonathanT]

Isladogs, I saw your post after I had come up with another solution. Your option does provide what I was looking for. I'll try your option and see how it works. Thanks for the input

[isladogs]

Your solution should work but both of my alternatives should achieve the same result with less effort. No need to add multiple values that are identical

[JonathanT]

isladogs, I tried your solution and it is much more efficient than mine. I will continue to use it. Thanks!

[isladogs]

Excellent.
I'll write a short web article explaining this approach and reference this thread
Out of interest, which of the two methods did you use?

[JonathanT]

I used the TempVar solution

[isladogs]

Thanks. Is it OK with you if I use your data and this chart in the web article?

[JonathanT]

Yes, that is OK. There is nothing personal or proprietary in the DB.

[isladogs]

Thanks for letting me know. I may write that page tomorrow or Friday

[isladogs]

Web article now published: Using Combo Charts in Access

Thanks for asking the question and allowing me to use your data

[JonathanT]

I have one more question regarding the reference line on the chart. Some of the values for the reference line are in tenths, such a 5.7 or 3.2. I set those values for the TempVars in the OnLoad section of the form. However when I run the query for the reference line the values always round either up or down to a whole number. Anyway to get the reference line to show as the exact value shown for the TempVars? I tried formatting the query field with Fixed or Standard and there was no change

[isladogs]

The query I provided cast that as an integer: CInt([TempVars]![RefLine]) AS RefLine for reasons i didn't originally explain in the article

Remove that and replace with e.g. Round([TempVars]![RefLine], 1) AS RefLine where 1 = number of decimal places required.

NOTE: Due to an Access bug, you MUST cast or format the TempVar field in the query.
You can't just use [TempVars]![RefLine] in the query as that will produce gibberish:



I've just added a comment to that effect in the web article