Given a well normalized database design, it might be possible to do this
with a query alone, but with the design you've described (which is not in
First Normal Form) you'll need to combine some VBA with a query ...
Public Function FMin(ParamArray Values() As Variant) As Variant
Dim varLoop As Variant
Dim varWork As Variant
For Each varLoop In Values
If IsEmpty(varWork) Then
varWork = varLoop
Else
If varLoop < varWork Then
varWork = varLoop
End If
End If
Next varLoop
FMin = varWork
End Function
Public Function FMax(ParamArray Values() As Variant) As Variant
Dim varLoop As Variant
Dim varWork As Variant
For Each varLoop In Values
If IsEmpty(varWork) Then
varWork = varLoop
Else
If varLoop > varWork Then
varWork = varLoop
End If
End If
Next varLoop
FMax = varWork
End Function
SELECT FMax([E1],[E2],[E3],[E4])-FMin([E1],[E2],[E3],[E4]) AS Range
FROM Table1;
--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com
The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
Scott Duncan said:
We are a calibration company.
We store errors into separate fields, i.e. E1, E2, ect...
I need to come up with a query that calulates the Range of the error. Keep
in mind that they are in
different fields.
For Example
E1= 0
E2= 1
E3= 0
E4= -1
The Range would be 2.
Tried everything I can think of...
Any ideas ?
TIA,
SD
