Compare value to previous record

  • Thread starter Thread starter CLR
  • Start date Start date
C

CLR

Hi All.........

I'm real new to Access............trying to write a Query that will check
value in ActualTime field and compare it to equivelent value in the previous
record to see if they are the same, then "do my stuff".........I know how to
create the "IF" statement, but do not know how to identify a specific cell
like I can in Excel...........any help would be appreciated..........

Vaya con Dios,
Chuck, CABGx3
 
Hi All.........

I'm real new to Access............trying to write a Query that will check
value in ActualTime field and compare it to equivelent value in the previous
record to see if they are the same, then "do my stuff".........I know how to
create the "IF" statement, but do not know how to identify a specific cell
like I can in Excel...........any help would be appreciated..........
Click to expand...

Well, Excel is a spreadsheet; Access is a relational database. THEY
ARE VERY DIFFERENT. The similarity in appearance between the Datasheet
view of a Table and a Spreadsheet is deceptive. Trying to apply
spreadsheet logic to databases won't work, and will give you endless
frustration! You can drive nails with a crescent wrench, but that
doesn't make it a hammer!

For one thing, a Table does not have "cells"; it has Records which
consist of Fields. And it does not have *any* usable order; a Table is
an unsorted heap of records.

What you'll probably need to do (if the ActualTime increases
chronologically with added records) is use

DMax("[ActualTime]", "[YourTableName]", "[ActualTime] < #" &
[ActualTime] & "#")

to look up the most recent ActualTime value earlier than this record's
ActualTime. To calculate the time elapsed, use the DateDiff function
(see the VBA online help for details).

John W. Vinson[MVP]
 
Thanks John, I appreciate your response and enlightenment.

I hope I didn't confuze things by naming that field ActualTime.........it's
not really a "time" but an amount of time, as in "ActualSetupTime".........I
hope that doesn't change your solution. I do have columns (excuse, I mean
fields) representing "date" and "shift" which together would give
chronological time........would that require modification of your solution?

Again, many thanks,
Vaya con Dios,
Chuck, CABGx3

John Vinson said:
Hi All.........

I'm real new to Access............trying to write a Query that will check
value in ActualTime field and compare it to equivelent value in the previous
record to see if they are the same, then "do my stuff".........I know how to
create the "IF" statement, but do not know how to identify a specific cell
like I can in Excel...........any help would be appreciated..........
Click to expand...

Well, Excel is a spreadsheet; Access is a relational database. THEY
ARE VERY DIFFERENT. The similarity in appearance between the Datasheet
view of a Table and a Spreadsheet is deceptive. Trying to apply
spreadsheet logic to databases won't work, and will give you endless
frustration! You can drive nails with a crescent wrench, but that
doesn't make it a hammer!

For one thing, a Table does not have "cells"; it has Records which
consist of Fields. And it does not have *any* usable order; a Table is
an unsorted heap of records.

What you'll probably need to do (if the ActualTime increases
chronologically with added records) is use

DMax("[ActualTime]", "[YourTableName]", "[ActualTime] < #" &
[ActualTime] & "#")

to look up the most recent ActualTime value earlier than this record's
ActualTime. To calculate the time elapsed, use the DateDiff function
(see the VBA online help for details).

John W. Vinson[MVP]
Click to expand...
 
Thanks John, I appreciate your response and enlightenment.

I hope I didn't confuze things by naming that field ActualTime.........it's
not really a "time" but an amount of time, as in "ActualSetupTime".........I
hope that doesn't change your solution. I do have columns (excuse, I mean
fields) representing "date" and "shift" which together would give
chronological time........would that require modification of your solution?
Click to expand...

Then you'll need to use the date and shift fields in the criteria to
locate the most recent record. You might want to rename the field
"date" - that's a reserved word, for the Date() function which returns
the system clock date; using it as a field or control name *will* get
Access confused. What's the datatype and contents of the Shift field?

John W. Vinson[MVP]
 
The present "Date" is indeed the date, like 1/4/2005, but I can of course
re-name it to "DayOfYear" or whatever.

The "Shift" field is numerical, and is either 1,2, or 3, for FirstShift,
SecondShift, or ThirdShift. Incidently tho, if that field is to be sorted
on, then chronologically, the 3 comes first, then the 1 then the
2...........in Excel I can define a special list to sort on, can that be
done in Access?

Thanks again for your time and help on this John,
Vaya con Dios,
Chuck, CABGx3
 
I've searched "help" as best I can and can find no other way to sort a field
except by Ascending or Decending.........so, for my special need, I suppose
I will have to add another field with codes to make one of those come out
the way I want..............anybody know of another way?...........

Vaya con Dios,
Chuck, CABGx3
 
I've searched "help" as best I can and can find no other way to sort a field
except by Ascending or Decending.........so, for my special need, I suppose
I will have to add another field with codes to make one of those come out
the way I want..............anybody know of another way?...........
Click to expand...

Nope. That's the way to do it.

John W. Vinson[MVP]
 
Okey Doke..............thank you kind Sir, I appreciate the help and guidance.

Vaya con Dios,
Chuck, CABGx3


I've searched "help" as best I can and can find no other way to sort a field
except by Ascending or Decending.........so, for my special need, I suppose
I will have to add another field with codes to make one of those come out
the way I want..............anybody know of another way?...........
Click to expand...

Nope. That's the way to do it.

John W. Vinson[MVP]
 
Back
Top