Software

Ask something
Discussion Locked

Access 2000 query mechanics

0 helpful
5 replies

by bridgit

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I'm trying to find a way to subtract consecutive record values in Access 2000. I have a time value in the first record and I need to subtract it from the time value in the second record. The time values are in the same field. Is this possible? And i need to generate a graph of data from the resulting query. Is this possible? The graph generated needs to be based on a value that the user selects. Thanks!

5 replies

by kees.valkenswaard

Actually I would export the data to Excel. To calculate differences between consecutive records is very simple. To export to Excel is also easy. Just use Tools, office links. For your graphs this is again the best solution.
However if you insist doing it in Access, here is a first hint.
Suppose you have a very simple table named serie.
It has two fields, auto as auto-number field and a field time with a time notation.
Make a new query with two copies of this table. You will see a table serie and a table serie_1.
That is the trick.
The first field would be:
Diff: [serie]![time]-[serie_1]![time]
In properties show as time, with e,g. hh:mm
If you want to check your values, add the field time from serie and next time from serie_1.
The criteria for the file diff [which only exsists in your query] would be:
[serie]![auto]=[serie_1]![auto]-1
This will do the final trick.
Diff serie.time serie_1.time
0:43 12:00 12:43
0:27 12:43 13:10
0:45 13:10 13:55
The original table would hold:
auto time
1 12:00
2 12:43
3 13:10
4 13:55
Good luck.

by bridgit

This didn't help. It is possible to manipulate the date, but not to grab the time from one field in one record in Access and then subtract it from another field in another record in Access. Thanks for the answer though!

by donq

Sure, Include both Date/Time fields in your query and apply a "DateDiff" Function to them in your report. There is much very easy on-line Help under DateDiff for "h" (Hour, "n" (Minute), and "s" (Second). Note Minute ia sn n - not an m!

by bridgit

The same response as above. It turns out that it indeed is not possible and we ended up doing this in Excel with VBScripts. Thank you for your answer.

This discussion is closed to new replies.