How to Analyze a field

[mart]

I have been tasked to do this program that should compare
data that is on the same table. On the following example
it would be shown ideally what the answer should be for
the Time Frame field:

Purchase Time Time Frame (minutes)
---------------------------------------
1 1:22AM 0
2 1:23AM 1
3 1:26AM 3

I need to compare the time frame between Purchase number
1 with Purchase number 2, Time Frame between Purchase
number 2 with Purchase number 3, etc. I have no idea how
to write the expression to calculate the field.

How can I obtaine the answer in the field named Time
Frame? What is the expression that needs to be
used in Microsoft Access 2000 for the field named Time
Frame?. Please I need help on this matter urgently. I am
really desperate I have over 80,000 entries that I have
to analyze.

 

[Allen Browne]

Answered in the post by "RITRAM" in m.p.a.queries

 

[John Vinson]

I have been tasked to do this program that should compare
data that is on the same table. On the following example
it would be shown ideally what the answer should be for
the Time Frame field:

Purchase Time Time Frame (minutes)
---------------------------------------
1 1:22AM 0
2 1:23AM 1
3 1:26AM 3

I need to compare the time frame between Purchase number
1 with Purchase number 2, Time Frame between Purchase
number 2 with Purchase number 3, etc. I have no idea how
to write the expression to calculate the field.

I *think* what you want is the elapsed minutes since the previous
value of Time... right?

Try putting this in an empty Field cell in the query design grid:

TimeFrame: DateDiff("n", NZ(DMax("[Time]", "[your-table-name]",
"[Time] < " & [Time]), [Time]), [Time])

As you stated the problem, this will give you a TimeFrame value for
every record in the table. If you want to do it for a subset of the
table, e.g. the records for a particular day or a particular sales
station, please explicate.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps