Comparing Rows in a table

[Matt]

I have a table that keeps track of growth data for some plants. I'd like to be able to produce a growth rate. Its easy enough to do in Excel, the equation is LN(Measuremnt A/Measurment B)/Delta Time. But I'd like my access database to do this automatically.

How can I subtract the time from record 1 to record 2 (and 3 to 2, 4 to 3 etc, to get the delta time? And similarly how do I divide the mass in row 1 by the mass in row 2?

Many thanks!!

 

[dkinley]

Allen Browne has a how-to that should be able to get you going in the right direction here.

Scroll down to the "Get the value in another record" section. The example he uses is gas meter reading which sounds like what you want to do.

HTH
-dK

 

[Matt]

Thanks! I'll have a look.

 

[The_Doc_Man]

Be aware that Access recordsets don't necessarily have a "next" or "previous." There are tricks one can play, true, but remember that you are stretching the model on which Access and other SQL-based utilities are built.

Order is an arbitrarily imposed property of most tables. I.e. you can sort as you wish, on one field or another. The order of presentation must therefore be considered arbitrary. Keep that in mind when trying to figure out what Allen does.

 

[gemma-the-husky]

there are various inbuilt statistical analysis tools - available in totals queries - standard deviations, and stuff like that - maybe some of those will help.

the best way is to try and hold all the data you need in each row - so that you dont have to refer to other rows. however the usefullness of this idea depends on yuor data model, and your data capture methods.

===========
this all comes back to the fact that access (really) handles unordered sets of data - indeed it is axiomatic that the order of rows is irrelevant in a database

however, access does handle stuff sequentially in graphs and reports - so it is probably easier to generate this stuff by producing a report, or a line graph

 

[DCrake]

Another solution to try is to get Access to collate the data sequentially then use the CopyToRecordset to export the data along with the necessary forumula to Excel and present the results in an Excel spreadsheet.

 

[gemma-the-husky]

thats a good idea, David

Matt

you will probably find it difficult to get this resolved in Access - since this isnt Access's forte. David's idea (of exporting to excel) is a good one.

Access is good at managing homogeneous data - but is not good at applications where data has to be compared sequentially. You have to write code to do this, and come up with both an algorithm and a mechanism to get the answers you want - and although the algorithm may be clear, the methodology in access may still be quite tortuous.

 

[Matt]

After messing around with this for several hours, and reading the suggestions I'm thinking it probably is best to just export this one to excel. I could change the table structure, but that would make things unworkable for the growth curve data that I'm using it for. (chart date vs. mass)

thanks for all the advice

 

[dkinley]

I was thinking I put together a method for something like this on another post. It is located here.

The code isn't optimized but it does work and you should be able to modify it for your purpose. I know that I have never tested in an operating environment. It uses a global variable to store the previous row datum and applies it to make a decision about the next row.

The key bit is to apply a sort to your data in the QBE so the rows will be in desired order for the operation.

HTH,
-dK

 

[Matt]

Hi all,

I found another way around. I built a query that asked the user which dates they wish to compare to get the growth rate. After that the math flows easily. It doesn't create a canned list of daily growth rates, but it is actually more versatile because my users can get growth rates calculated over days, weeks, months or whatever interval they choose.