Dear CASJAS:

Some of your terminology suggests a misunderstanding of how queries
work. "How can I tell it to look at just the first one ahead & no
others?" For all practical purposes, it finds all the rows of data
simultaneously.

However, you explain that there is a unique date column, and so what
you mean is the row which has the minimum date/time value from among
those rows to be considered.

I see you have:

C.[Opt Time Final] = (Select
MIN(Y.[Opt Time Final])
FROM [System tbl 0064 VFH qry] AS Y
WHERE A.[Opt Time Final] Y.[Opt Time Final]
AND Y.[Est VFH Conc1] 0)

This will limit what is returned from table C to those rows with one
specific value in [Opt Time Final]. I suggest you temporarily add
C.[Opt Time Final] and C.[Est VFH Conc1] to the SELECT clause to make
sure the two rows it is returning have the same value for C.[Opt Time
Final], but I expect this is the case. But do they have different
values for [Est VFH Conc1]? If so, this suggests that [Opt Time
Final] is not unique, and hence causes the problem you're having.

You can use a similar technique to test B.[Est VFH Conc1] and B.[Date
Monitored]. Again, if [Date Monitored] is not unique, this could
cause your problem.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Wed, 21 Jul 2004 09:11:01 -0700, CASJAS
wrote:

My calculation is coming up with two different values for the following SQL:

SELECT
A.ID,
A.Site,
A.[Date Monitored],
A.[Opt Time Final],
A.[TSFR VAL],
((C.[Est VFH Conc1]-B.[Est VFH Conc1])
/(C.[Opt Time Final]-B.[Opt Time Final]))

When it "looks ahead" for which C.[Opt Time Final] value to use, its calculating two different results because when it looks at:

AND C.[Opt Time Final]=(Select
MIN(Y.[Opt Time Final])
FROM
[System tbl 0064 VFH qry] AS Y
WHERE
A.[Opt Time Final] Y.[Opt Time Final]
AND Y.[Est VFH Conc1] 0)

It finds two (or more) records that fit this expression. How can I tell it to look at just the first one ahead of it & no others? I do have a date field in the table that's unique. I tried adding this to the SQL but it didn't seem to produce the right results:

AND B.[Date Monitored]=(SELECT
MAX(X.[Date Monitored])
FROM
[System tbl 0064 VFH qry] AS X
WHERE
X.[Date Monitored] A.[Date Monitored])

*(A.[Opt Time Final]-B.[Opt Time Final])
+(B.[Est VFH Conc1]) AS Calc1
FROM
[System tbl 0064 VFH qry] AS A,
[System tbl 0064 VFH qry] AS B,
[System tbl 0064 VFH qry] AS C
WHERE
A.[Opt Time Final]=[b].[Opt Time Final]
AND A.[Opt Time Final]=[C].[Opt Time Final]
AND A.[Est VFH Conc1] = 0
AND B.[Est VFH Conc1] 0
AND C.[Est VFH Conc1] 0
AND B.[Opt Time Final]=(SELECT
MAX(X.[Opt Time Final])
FROM
[System tbl 0064 VFH qry] AS X
WHERE
X.[Opt Time Final]= A.[Opt Time Final]
AND X.[Est VFH Conc1]0)
AND C.[Opt Time Final]=(Select
MIN(Y.[Opt Time Final])
FROM
[System tbl 0064 VFH qry] AS Y
WHERE
A.[Opt Time Final] Y.[Opt Time Final]
AND Y.[Est VFH Conc1] 0)

UNION
SELECT
Z.[ID],
Z.[Site],
Z.[Date Monitored],
Z.[Opt Time Final],
Z.[TSFR Val],
0 As Calc1
FROM
[system tbl 0064 VFH qry] AS Z
WHERE
Z.[Est VFH Conc1] 0;