I am very new to using Access, and do not know many of the functions it is capable of, so my question may not be the clearest, but here goes...
I am trying to design a database to statistically monitor the values that are input. i.e. I have a Data Entry form, Control Charts, and the Tables are all full of Fields containing numbers.
One of the features I want is for it to be able to flag up trends in the data. For example: If the last 5 values in a certain Field are trending upwards (last value is higher than previous, which is higher than previous, which is higher...etc) or downwards, then I would like a coloured textbox to appear (or something).
I have had a think about how I would do it in Excel: I would reference specific values (cells) and have some code like:
If .Cells(iRow, 1).Value > .Cells(iRow - 1, 1) And .Cells(iRow - 1, 1).Value > .Cells(iRow - 2, 1) etc etc Then
Sheets("Box1").Range("AA15").Interior.ColorIndex = 44Trends = "3 consecutive points increasing"
I don't really know much about how Access works, but all I can seem to manage is to write a query that selects the latest 5 record entered... and then I'm stuck!
Is there a way of writing something in SQL that would do this? Could anyone give me some advice! Or point me in the right direction for some techniques that would make this possible?
Thanks.
The table of data I am working with has Fields listed: ID, Batch, Date, Field4, Field5, Field6.
All are numbers.
I would like to know if there are trends in the last 5 records for Fields 4, 5, 6 and 7.
Cross Posted at Stack Overflow : http://stackoverflow.com/questions/29489357/data-trends-in-an-access-database
I am trying to design a database to statistically monitor the values that are input. i.e. I have a Data Entry form, Control Charts, and the Tables are all full of Fields containing numbers.
One of the features I want is for it to be able to flag up trends in the data. For example: If the last 5 values in a certain Field are trending upwards (last value is higher than previous, which is higher than previous, which is higher...etc) or downwards, then I would like a coloured textbox to appear (or something).
I have had a think about how I would do it in Excel: I would reference specific values (cells) and have some code like:
If .Cells(iRow, 1).Value > .Cells(iRow - 1, 1) And .Cells(iRow - 1, 1).Value > .Cells(iRow - 2, 1) etc etc Then
Sheets("Box1").Range("AA15").Interior.ColorIndex = 44Trends = "3 consecutive points increasing"
I don't really know much about how Access works, but all I can seem to manage is to write a query that selects the latest 5 record entered... and then I'm stuck!
Is there a way of writing something in SQL that would do this? Could anyone give me some advice! Or point me in the right direction for some techniques that would make this possible?
Thanks.
The table of data I am working with has Fields listed: ID, Batch, Date, Field4, Field5, Field6.
All are numbers.
I would like to know if there are trends in the last 5 records for Fields 4, 5, 6 and 7.
Cross Posted at Stack Overflow : http://stackoverflow.com/questions/29489357/data-trends-in-an-access-database
Last edited by a moderator: