Creating "Baseline" on chart...

[Stanly222]

I have a query with 2 fields, Date and AvgTimeWkly. The query only pulls records with a date within the last 3 months.

I need the query to calculate the average of all AvgTimeWkly records that it pulls and put that same value in a new field called Baseline for all records.

This way a line chart can plot the baseline which will have to be the same in all records. Then the AvgTimeWkly line can move up and down related to the baseline.

Is there an expression I can add to a new field in the query to do this when the query is run?

Thanks,
Scott

 

[Len Boorman]

Maybe somebody knows better but I do not believe you can set a baseline in the manner you ask.

I think you are going to have to create the baseline yourself.

I have done this before and basically I decided that I would calculate the baseline figure and put it into a table named Temp.

I then constructed a query to be used as the source for my chart.

I believe in doing simple steps so

New query that queried the chart source query and table Temp. Because there was no relationship between these two I would automatically get a cartesian product. This query then gave me a source for my chart where every interval had not only the actual value but also my baseline value

Plot the chart no probs

HTH

Len B

If someone has a smart baseline function I would also be interested

 

[KKilfoil]

I agree with what Len had suggested, except:

You should be able to make a summary query to calculate a single average value for 'baseline' and use that as the subsequent source where he suggests using table 'temp'. Obviously, the resulting query will not be updateable, but I don't think that matters to you here.

 

[Stanly222]

So close I can smell it...

Wonderful Suggestions!!! Thanks guys-

I have created a summary query that calculates the single average of all of the averages from the original query. And it works- there is a single value in one field with the correct average, and it should be a dynamic value. That is my goal.

I then added the new query to the first query and got exactly what I wanted. A new field with the same value in every record. The overall average. When I ran the query it worked just as I had hoped.

But when I closed it and tried to open it later I got a "circular reference" error. My bubble burst- I should have known it was to good to be true.

I went ahead and created the chart without a baseline and used the "trend line" feature. It won't work as well, but it might keep the boss happy.

 

[Len Boorman]

Run the query to calculate the average first as a Make Table query.

Then your query to get the chart values. Query 2

Then a query that selects everything from query 2 and teh value from the table without any joins

You will get what you want I am sure.

The reason for the table is exactly to avoid the circular reference.

Do not give up. It is really straightforward but do not try to group steps together. Little steps are all you need to climb everest, just more of them.

Achieve what you set out to do

Len B

 

[Stanly222]

It works!

Thanks for the help-

I did what you suggested with the make table query and it worked. I also created a macro to run the query, then print the chart. The baseline will always reflect the most current data.

 

[KKilfoil]

I'm glad you found a way to solve your problem.

I didn't realize that you intended to take the 'average' value and relate it back into your 'first' query.

I expected that you would relate this value to a NEW query that also used your original query as source, as there would then have been no circular reference, and no new table required.

Its a personal preference, but I dislike creating any extra tables if I can avoid it. I admit that sometimes there is no workaround.