calculations on records

[jamesd]

I have a table with many 1000's of records.
Each record has a time field.
Can I calculate in a query record 1 minus record 2 .....record 2 minus record 3 ......record 3 minus record 4 ect

 

[Joe4]

That kind of task is usually more of a kind of Excel task than an Access task. Relational databases really don't have that kind of relationship within records in the same table.

That being said, I know it can be done using ADO or DAO in VBA and looping through RecordSets and storing the result in a table. However, that wouldn't be dynamic, and if the any records are deleted, inserted, or re-sorted, your numbers probably won't look right.

Might I ask what exactly you need to do this for? If I understand your end goal, I may have some other suggestions for you.

If you are interested in a VBA solution, please let me know, and let me know how your records are ordered.

 

[skaas]

Of course you can do this

you should find this explanation of how to form calculations using dates in an Access query useful

http://www.fontstuff.com/access/acctut03.htm

This site also has some excellent Tips on certain coding requirements

The home page is http://www.fontstuff.com/

If there is a will, there is a way

Though generally it is correct, using excel would be more efficient,

 

[Joe4]

skaas,

Based on the links your provided, I think you may have misread the question.

Within one table, I believe he wants to do a calculation where he subtracts a date field in a record from the date field in the previous record. The links you provided are useful for doing date calculations across fields in a single record, not across records.

 

[jamesd]

dead right
the reason I am trying to do it in access is the amount of records.
The table is the User pick table from a large warhouse and it records each user and pick and the time picked.
I need to work out units picked per hour and down time over a certain time ie more than 10mins.
there are in nealy a million records per week.

 

[Joe4]

there are in nealy a million records per week.

Have you done anything with it in Access yet? At that kind of size, it could potentially cause problems for Access too (especially if you are not deleting all the old records first).

If it gets too big for Access, it might require a more powerful database program like SQL or Oracle.

BTW, I believe that Excel 2007 can hold up to something like one million rows.

 

[skaas]

My apologies

I did miss-read - That is what I get for trying to do too many things at once

Over to you Joe ---->