Is it possible to have two "trendlines"?

[dilbert]

I am building a weight loss report for a Biggest Loser competition I am running at work. I just started it and am an Access beginner. I have two tables, one for the user that has their age, height and goal weight, then another table that has their weights over time. I added a trendline to the graph, but I'd also like to add their goal weight so they can see how much more they need to go.

I attached the first draft of the report.



Access 2016

test.pdf

Can the graph have a trendline and a goal line? If not, can I add the goal line?

[June7]

A graph can have multiple lines.

I analyse graph issues best when I can work with data. If you want to provide, follow instructions at bottom of my post.

[dilbert]

You are very kind. Clearly I started with the contacts database (free template from Microsoft) and have been editing it for what I need. One item that is perplexing is I created the report, it needs the form to stay open because the query is basing the report on a field in the form. The report opens under the form. Maybe I'll try to close the form after the report opens?

Ultimately what I want is for it to send the report to the user through my work Outlook. I'll work on that later.

If you can offer some guidance on how to get the Goal weight on the graph, that would be terrific.

I attached the database, again, thank you.

Biggest Loser.zip

[June7]

Include the Contacts table in q_Weights.

graph RowSource:
SELECT (Format([DateOfWeight],"Short Date")) AS Expr1, q_Weights.[Weight], q_Weights.[Goal Weight] FROM q_Weights WHERE Contacts_ID=[ID];

I've never set trend line so am exploring this now. Well, that was simple but can't specify color.


I really don't like dynamic parameterized queries. I prefer to apply filter to form or report. In VBA (there is macro equivalent):

DoCmd.OpenReport "r_contacts", , , "Contacts_ID=" & Me!ID


Could really do this as a single report and no subreport. Either way, the BMI expression should reference report not form and with the revision to q_Weights don't need report name prefix: =([Weight]*703)/([Height]^2)

[dilbert]

Include the Contacts table in q_Weights.

graph RowSource:
SELECT (Format([DateOfWeight],"Short Date")) AS Expr1, q_Weights.[Weight], q_Weights.[Goal Weight] FROM q_Weights WHERE Contacts_ID=[ID];

I've never set trend line so am exploring this now. Well, that was simple but can't specify color.


I really don't like dynamic parameterized queries. I prefer to apply filter to form or report. In VBA (there is macro equivalent):

DoCmd.OpenReport "r_contacts", , , "Contacts_ID=" & Me!ID


Could really do this as a single report and no subreport. Either way, the BMI expression should reference report not form and with the revision to q_Weights don't need report name prefix: =([Weight]*703)/([Height]^2)

Thank you, I really should have emphasized more how much of a beginner I am.

As you may recall, my q_Weights query is already complicated (for me). I am not sure where I should add the addition to the query, nor how I would refer to it in the graph.

SELECT Weights.Weight, Weights.DateOfWeight, Weight-(SELECT TOP 1 Dupe.Weight
FROM Weights AS Dupe
WHERE Dupe.Contacts_ID = Weights.Contacts_ID
AND Dupe.DateOfWeight < Weights.DateOfWeight
ORDER BY Dupe.DateOfWeight DESC, Dupe.Contacts_ID) AS Change, Weights.Contacts_ID
FROM Weights
WHERE (((Weights.Contacts_ID)=[Forms]![Contact Details]![ID]))
ORDER BY Weights.DateOfWeight;


As for the subreport, I'm not positive how I ended up doing that. The report won't get too much more complicated, so I'm not going to try to fix it.

The contest starts on February 1, I'm excited.

[June7]

Use query builder. In design view pull in the Contacts table. It will automatically link on the PK/FK fields. Then add fields to the grid. Basic Access functionality.

Then the adjustments to report and graph will be simple.

[dilbert]

Yes, I agree it is easy enough to add that table and since I already built the relationship, you are correct, they are linked.
After that I'm afraid I am lost. I am now getting an error when I try to close the form saying a macro can't run. I fear the harder I try to add items, the more problems I am creating for myself.

I appreciate you trying to help, but I think I have asked for options that are beyond what I can do.

Thank you

[June7]

Don't give up yet. I will get you there.

Why are you grouping by date in the graph RowSource? Will there be multiple weights for each date for each contact?

[dilbert]

Don't give up yet. I will get you there.

Why are you grouping by date in the graph RowSource? Will there be multiple weights for each date for each contact?

Thank you.

There will only be one date for each person for each date. Ideally one a week or so for 3 months.

[June7]

Okay, with that in mind, what I did with your posted db:

1. modify q_Weights
SELECT Contacts.*, Weights.Weight, Weights.DateOfWeight, Weight-(SELECT TOP 1 Dupe.Weight
FROM Weights AS Dupe
WHERE Dupe.Contacts_ID = Weights.Contacts_ID
AND Dupe.DateOfWeight < Weights.DateOfWeight
ORDER BY Dupe.DateOfWeight DESC, Dupe.Contacts_ID) AS Change, Weights.Contacts_ID
FROM Contacts INNER JOIN Weights ON Contacts.ID = Weights.Contacts_ID
WHERE (((Weights.Contacts_ID)=[Forms]![Contact Details]![ID]));

2. modify textbox expression in q_Weights subreport1
=([Weight]*703)/([Height]^2)

3. modify graph RowSource
SELECT [DateOfWeight], [Weight], [Goal Weight] FROM [q_Weights] WHERE Contacts_ID=[ID];

4. while report is open in Preview, save it then close and reopen in Design view
if you want, modify graph to add trend line for the Goal Weight - it doesn't show up very well because of the Y-Axis horizontal lines - might want to change the axis lines to different style or weight or color

[dilbert]

1. Even I can copy and paste that.
2. The result is now listed for all BMI rows as #Type!
3. Done
4. I fiddled with it and it looks pretty good. (I made another edit since I uploaded the image. I made the goal bars white so they don't show up.)

[June7]

Well, the BMI calculates perfectly for me. At least no #Type! error. But a BMI of 1.4 sounds odd in light of the Categories. Is there a misplaced decimal point?

How did you get the trend line to be green? Wait, I figured it out - right click on trend line.

[dilbert]

Well, the BMI calculates perfectly for me.

I suspect it's because I am opening it incorrectly still. You wanted me to remove this from the q_Weights query right?
[Forms]![Contact Details]![ID]
I just removed it.

Then I need to add this to the Macro that opens the report?
Contacts_ID= & Me!ID

I'm not sure where to add that.

[June7]

I did not do that but it is what I recommended earlier. Actually, I don't use macros, only VBA. The Me. reference will not work in macro.

The BMI calc has nothing to do with how the report is opened.

The BMI number should be much higher http://www.whathealth.com/bmi/formula.html

[dilbert]

I did not do that but it is what I recommended earlier. Actually, I don't use macros, only VBA. The Me. reference will not work in macro.

The BMI calc has nothing to do with how the report is opened.

I am not doubting you, clearly the error is mine. I double checked, the BMI formula is correct.
Since I removed that parameter from the query, does it need to be added somewhere else? If that why I am getting the #Type! error?