Trending Data on Line Chart

[Processor]

How can I format data on a line chart that i have trending 4 times a second? It seems that MS only allows me to select day, month, year for the X axis. I only need to plot my measurements for a single axis and i would like for my timestamp to display across the bottom of my chart. I actually would prefer to get just the elapsed time in seconds if there is a way for the query to interpolate the current 4hz date/time stamp for that. The trending may last for .5 to 10 minutes. I am trying to place this chart in my existing report. This is not an attempt to log live data, but rather plot the logged data.

I am sure i left a number of details out. Please let me know if someone can assist.

[June7]

Provide sample data and perhaps even a mockup of the desired chart output.

If you want to provide database, follow instructions at bottom of my post.

[Processor]

Here is an example of the data i have...


Here is a sample of what the chart could look like....


And, here is the desired results...

[June7]

Neither one of those graphs will be simple. And not understanding how the x-axis values in the second are derived.

Would prefer to have a table (in post or attached file) so I could copy/paste/import data instead of having to build table and do data input.

Is there a unique record ID field, such as autonumber type, in the table?

[Processor]

Sorry. Trying to attach file, but i am so new to access that i do not know how to change the field IDs (as i don't want it on the web). It keeps giving me an error when i try to export the table to a new BD file with changed headers. Any thoughts would be much appreciated.
As for the second screenshot, i simply exported the data to excel (whiuch i am much better at) and created the table within. For that one, i was able to make a simple calculation (timestamp minus the initial timestamp) that resulted in the elapsed time. It is not imperative that i end up with this result, but it would be more appealing.

[Processor]

Oh, Sorry, there is no unique record ID in the table. I wondered if there were a way to create that. Autonumber????

[June7]

Yes, an autonumber type field should serve.

And now I see it is cumulative elapsed time.

Import or copy/paste table unchanged then change design in the new file as you see fit.

[Processor]

Yep. Tried that. When i try to change the field after creating the copy, i get the error "Could not find field "******" " upon saving the file. Any other suggestions?

[Processor]

Figured it out. I didnt realize there were more properties to the table. Once opening that, i found a validation rule that included the old title. I have attached the file.

[June7]

I seldom set properties like that in table. I do it on form.

I have attempted to recreate the example graphs. I filtered the data to show only records for 10am.

I do wonder why example Graph2 shows only 2 data points for each elapsed time.

Turns out a unique record ID was not needed, although I did modify table to add field.

I did not attempt to display the elapsed time as 00:00:00. Review https://access-programmers.co.uk/for...ecimal+degrees

Here is alternative query for Graph2 RowSource:
SELECT DateDiff("s",(SELECT Min(Time_Stamp) FROM TREND002),[Time_Stamp]) AS ElapsedTime, Act_val FROM TREND002 WHERE DatePart("h",[Time_Stamp])=10;

[Processor]

Merry Christmas!

Thank you for the sample. I am a bit confused as to why i see much more verbiage about your response in my email alert than what i found when i logged on this morning. In the email, you mention several queries needed and it raking a long time to produce the report as a result??? Perhaps, that is no longer applicable?

Nevertheless, i'm trying to figure out how you did this. I assume you've created the chat in a different manner than i was trying to. When i did it, I would get a "Chart Settings" and the regular "Property Sheet" dialogs every time. When i look at the properties on your chart, i only get the regular properties window. I don't know if that is what limits my ability to change things like "Row Source" where your formatting script resides. In my chart, it's called "transformed row source" and i cannot edit it.

Sorry, i know excel like the back of my hand and have been able to avoid access my whole life until now. It's so much more complicated than i ever dreamed.

I am not sure why the the example only shows two references for four datapoints. It seems like it is still plotting all of them though. This was a simple chart produced in excel and the the plot references were autonomous for me. I would like to know what controls that feature though as it becomes unreadable once it tries to reference say 1,000 points on a single graph.

Thank you for all the help.

[Processor]

I seldom set properties like that in table. I do it on form.

I have attempted to recreate the example graphs. I filtered the data to show only records for 10am.

I do wonder why example Graph2 shows only 2 data points for each elapsed time.

Turns out a unique record ID was not needed, although I did modify table to add field.

I did not attempt to display the elapsed time as 00:00:00. Review https://access-programmers.co.uk/for...ecimal+degrees

Here is alternative query for Graph2 RowSource:
SELECT DateDiff("s",(SELECT Min(Time_Stamp) FROM TREND002),[Time_Stamp]) AS ElapsedTime, Act_val FROM TREND002 WHERE DatePart("h",[Time_Stamp])=10;

Any ideas why i cannot seem to get the x axis labels to show on my graph? See the attached for example. The attached script screenshot shows the parts i changed highlighted to make it fit my application.

[June7]

Yes, I edited my post because I went down the wrong path in building Graph2.

I think I know why your example only shows 2 of the data points in labels but I expect all the data are on the line. It's likely just that displaying all the labels would be too crowded. I did observe that when I tried to show the entire table of data without filtering to a single hour's worth.

You open my db and run the report and X-axis labels do not show? I have no idea why that would happen. Nor why the line looks different and the points are not showing. That SQL looks exactly like what I did for Graph1.

Make sure you downloaded latest version of the db. I replaced it a couple of times.

[Processor]

I am sorry as i perhaps didn't do a good job explaining what i did.
I am trying to apply the script (or i guess I should call it the query) to my existing DB. To do this, i have had to make some changes to what data is being filtered and the field headers. I have succeeded in getting the plot correct, but the graph does not show the x axis labels. So, to be clear, "yes", your file worked and was formatted correctly when i tried it. But i'm glad you mentioned versions.... I decided to take a look because the version of the file i am having to use here is older. My existing file is .mdb and the new file you and i have been collaborating with is a .accdb file. Could this be my issue? I am honestly not sure why I am using the older version, but the existing file is such.

[June7]

I have not used mdb for a very long time and when I did, it wasn't much as we upgraded to 2007. So can't say if graphing in mdb is different.

Any reason not to upgrade the db? Any users still running 2003 or older Access?