A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Use first record found in expression?



 
 
Thread Tools Display Modes
  #1  
Old July 21st, 2004, 05:11 PM
CASJAS
external usenet poster
 
Posts: n/a
Default Use first record found in expression?

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;

  #2  
Old July 21st, 2004, 05:45 PM
Tom Ellison
external usenet poster
 
Posts: n/a
Default Use first record found in expression?

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;


  #3  
Old July 21st, 2004, 05:54 PM
CASJAS
external usenet poster
 
Posts: n/a
Default Use first record found in expression?

I've included my entire SQL below. C.[Opt Time Final] is part of an expression. The problem is, when the query returns the values, its running the calculation twice (or more) because it finds two Opt Time Final records that are identical where their [Est VFH Conc1] 0. I would like to have it perform the calculation on just the first C.[Opt time Final] value that it finds.

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]))
*(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;


"Tom Ellison" wrote:

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;



  #4  
Old July 21st, 2004, 06:09 PM
Tom Ellison
external usenet poster
 
Posts: n/a
Default Use first record found in expression?

Dear CASJAS:

As I tried to indicate before, a query doesn't find the rows it uses
in any particular order. It is best to think of it finding them all
at once.

Since there are two rows involved, it will not arbitrarily use one
rather than another. This results in two different calculated values.
If you want to choose one over the other, you must specify how you
want this done. The query will not randomly do this for you, unless
you build a random method into the query. How do you determine which
of the two different results you really want? If you have a method,
you could build that into the query so it will do what you want.

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


On Wed, 21 Jul 2004 09:54:03 -0700, CASJAS
wrote:

I've included my entire SQL below. C.[Opt Time Final] is part of an expression. The problem is, when the query returns the values, its running the calculation twice (or more) because it finds two Opt Time Final records that are identical where their [Est VFH Conc1] 0. I would like to have it perform the calculation on just the first C.[Opt time Final] value that it finds.

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]))
*(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;


"Tom Ellison" wrote:

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;




  #5  
Old July 21st, 2004, 06:35 PM
CASJAS
external usenet poster
 
Posts: n/a
Default Use first record found in expression?

Understood. I don't know how to go about doing that. The problem is, there isn't a consistent way of knowing which record it's going to need. It could be 3 fields ahead for one calculation and 5 the next. Here's an example of the data in the underlying table:

ID Site Date Monitored Opt Time Final EST VFH Conc1
1 999999 2/13/96 1 10
2 999999 2/14/96 24 6
3 999999 2/28/96 360 0
4 999999 3/13/96 696 50
5 999999 3/21/96 770 0
6 999999 3/22/96 770 0
7 999999 3/26/96 771 0
8 999999 4/9/96 1042 200
9 999999 4/25/96 1420 0
10 999999 5/7/96 1421 0
11 999999 5/21/96 1685 3500
12 999999 5/29/96 1686 0
13 999999 6/13/96 1732 260
14 999999 6/27/96 1732 0
15 999999 7/3/96 1732 0
16 999999 7/8/96 1852 0
17 999999 7/22/96 2188 500
18 999999 8/8/96 2596 0
19 999999 8/12/96 2692 0
20 999999 8/27/96 2860 0
21 999999 9/16/96 2860 800
22 999999 10/3/96 3268 0




"Tom Ellison" wrote:

Dear CASJAS:

As I tried to indicate before, a query doesn't find the rows it uses
in any particular order. It is best to think of it finding them all
at once.

Since there are two rows involved, it will not arbitrarily use one
rather than another. This results in two different calculated values.
If you want to choose one over the other, you must specify how you
want this done. The query will not randomly do this for you, unless
you build a random method into the query. How do you determine which
of the two different results you really want? If you have a method,
you could build that into the query so it will do what you want.

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


On Wed, 21 Jul 2004 09:54:03 -0700, CASJAS
wrote:

I've included my entire SQL below. C.[Opt Time Final] is part of an expression. The problem is, when the query returns the values, its running the calculation twice (or more) because it finds two Opt Time Final records that are identical where their [Est VFH Conc1] 0. I would like to have it perform the calculation on just the first C.[Opt time Final] value that it finds.

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]))
*(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;


"Tom Ellison" wrote:

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;




  #6  
Old July 21st, 2004, 06:50 PM
Tom Ellison
external usenet poster
 
Posts: n/a
Default Use first record found in expression?

Dear CASJAS:

Well, you said a mouthful. If "there isn't a consistent way of
knowing which record" then, assuming the query did pick one, it
certainly might be the wrong one, correct? So, wouldn't it be best if
the query picks both (or perhaps more than 2) and shows them to you?

So, let's work backward for a moment. Assuming the query remains as
is, how do you look at the data and say, "Aha, here's the one we
want." Is there an way you can know which row is the one? If not,
how did you expect the computer would know? Or do I still have some
misconception?

Remember, the computer is fast, but not very smart! It can only do
what you tell it to do, and it does that according to some rules
someone else made up, and which may contain bugs. Writing software
will always be an art. I'm not sure we are Computer Scientists. We
are often Computer Artists. Beauty (or correctness) is in the eye of
the beholder.

Sorry about the philosophy. It's really not my thing, but I enjoyed
trying!

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


On Wed, 21 Jul 2004 10:35:02 -0700, CASJAS
wrote:

Understood. I don't know how to go about doing that. The problem is, there isn't a consistent way of knowing which record it's going to need. It could be 3 fields ahead for one calculation and 5 the next. Here's an example of the data in the underlying table:

ID Site Date Monitored Opt Time Final EST VFH Conc1
1 999999 2/13/96 1 10
2 999999 2/14/96 24 6
3 999999 2/28/96 360 0
4 999999 3/13/96 696 50
5 999999 3/21/96 770 0
6 999999 3/22/96 770 0
7 999999 3/26/96 771 0
8 999999 4/9/96 1042 200
9 999999 4/25/96 1420 0
10 999999 5/7/96 1421 0
11 999999 5/21/96 1685 3500
12 999999 5/29/96 1686 0
13 999999 6/13/96 1732 260
14 999999 6/27/96 1732 0
15 999999 7/3/96 1732 0
16 999999 7/8/96 1852 0
17 999999 7/22/96 2188 500
18 999999 8/8/96 2596 0
19 999999 8/12/96 2692 0
20 999999 8/27/96 2860 0
21 999999 9/16/96 2860 800
22 999999 10/3/96 3268 0




"Tom Ellison" wrote:

Dear CASJAS:

As I tried to indicate before, a query doesn't find the rows it uses
in any particular order. It is best to think of it finding them all
at once.

Since there are two rows involved, it will not arbitrarily use one
rather than another. This results in two different calculated values.
If you want to choose one over the other, you must specify how you
want this done. The query will not randomly do this for you, unless
you build a random method into the query. How do you determine which
of the two different results you really want? If you have a method,
you could build that into the query so it will do what you want.

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


On Wed, 21 Jul 2004 09:54:03 -0700, CASJAS
wrote:

I've included my entire SQL below. C.[Opt Time Final] is part of an expression. The problem is, when the query returns the values, its running the calculation twice (or more) because it finds two Opt Time Final records that are identical where their [Est VFH Conc1] 0. I would like to have it perform the calculation on just the first C.[Opt time Final] value that it finds.

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]))
*(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;


"Tom Ellison" wrote:

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;





  #7  
Old July 21st, 2004, 07:05 PM
CASJAS
external usenet poster
 
Posts: n/a
Default Use first record found in expression?

I don't think I'm explaining myself very well. I know which value to use, its always going to be the next available value in the [Est VFH Conc1] field. I just don't know WHERE that value is going to lie in relation to the calculation.

It's always going to be the correlated with the next highest [Date Monitored] where [Est VFH Conc1] 0. I guess I need to add a line to so that it looks at the MAX [Date Monitored] that is higher than the current [Date Monitored] when choosing the C.[Opt Time Final] it is supposed to use. Any suggestions?

"Tom Ellison" wrote:

Dear CASJAS:

Well, you said a mouthful. If "there isn't a consistent way of
knowing which record" then, assuming the query did pick one, it
certainly might be the wrong one, correct? So, wouldn't it be best if
the query picks both (or perhaps more than 2) and shows them to you?

So, let's work backward for a moment. Assuming the query remains as
is, how do you look at the data and say, "Aha, here's the one we
want." Is there an way you can know which row is the one? If not,
how did you expect the computer would know? Or do I still have some
misconception?

Remember, the computer is fast, but not very smart! It can only do
what you tell it to do, and it does that according to some rules
someone else made up, and which may contain bugs. Writing software
will always be an art. I'm not sure we are Computer Scientists. We
are often Computer Artists. Beauty (or correctness) is in the eye of
the beholder.

Sorry about the philosophy. It's really not my thing, but I enjoyed
trying!

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


On Wed, 21 Jul 2004 10:35:02 -0700, CASJAS
wrote:

Understood. I don't know how to go about doing that. The problem is, there isn't a consistent way of knowing which record it's going to need. It could be 3 fields ahead for one calculation and 5 the next. Here's an example of the data in the underlying table:

ID Site Date Monitored Opt Time Final EST VFH Conc1
1 999999 2/13/96 1 10
2 999999 2/14/96 24 6
3 999999 2/28/96 360 0
4 999999 3/13/96 696 50
5 999999 3/21/96 770 0
6 999999 3/22/96 770 0
7 999999 3/26/96 771 0
8 999999 4/9/96 1042 200
9 999999 4/25/96 1420 0
10 999999 5/7/96 1421 0
11 999999 5/21/96 1685 3500
12 999999 5/29/96 1686 0
13 999999 6/13/96 1732 260
14 999999 6/27/96 1732 0
15 999999 7/3/96 1732 0
16 999999 7/8/96 1852 0
17 999999 7/22/96 2188 500
18 999999 8/8/96 2596 0
19 999999 8/12/96 2692 0
20 999999 8/27/96 2860 0
21 999999 9/16/96 2860 800
22 999999 10/3/96 3268 0




"Tom Ellison" wrote:

Dear CASJAS:

As I tried to indicate before, a query doesn't find the rows it uses
in any particular order. It is best to think of it finding them all
at once.

Since there are two rows involved, it will not arbitrarily use one
rather than another. This results in two different calculated values.
If you want to choose one over the other, you must specify how you
want this done. The query will not randomly do this for you, unless
you build a random method into the query. How do you determine which
of the two different results you really want? If you have a method,
you could build that into the query so it will do what you want.

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


On Wed, 21 Jul 2004 09:54:03 -0700, CASJAS
wrote:

I've included my entire SQL below. C.[Opt Time Final] is part of an expression. The problem is, when the query returns the values, its running the calculation twice (or more) because it finds two Opt Time Final records that are identical where their [Est VFH Conc1] 0. I would like to have it perform the calculation on just the first C.[Opt time Final] value that it finds.

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]))
*(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;


"Tom Ellison" wrote:

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;






  #8  
Old July 21st, 2004, 08:43 PM
CASJAS
external usenet poster
 
Posts: n/a
Default Use first record found in expression?

I added the following to the SQL but it's taking forever to load and coming up blank:

AND ((B.[DM VAL])=(Select MAX(X.[DM VAL]) FROM [System tbl 0064 VFH qry] AS X WHERE X.[DM VAL] A.[DM VAL] AND A.[DM VAL] B.[DM VAL]))

[DM VAL] is the value of the [Date Monitored] field. Here's the entire SQL:

SELECT DISTINCT A.ID, A.Site, A.[DM VAL], A.[Opt time final], ((C.[Est VFH Conc1]-B.[Est VFH Conc1])/((C.[Opt Time Final]-B.[Opt Time Final])))*(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.[Est VFH Conc1])=0)

AND ((A.[Opt time final])=[b].[Opt Time Final]

AND (A.[Opt time final])=[C].[Opt Time Final])

AND (C.[Est VFH Conc1]0)

AND (B.[Est VFH Conc1]0)

AND ((B.[DM VAL])=(Select MAX(X.[DM VAL]) FROM [System tbl 0064 VFH qry] AS X WHERE X.[DM VAL] A.[DM VAL] AND A.[DM VAL] B.[DM VAL]))

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)))
ORDER BY A.[ID];


"Tom Ellison" wrote:

Dear CASJAS:

Well, you said a mouthful. If "there isn't a consistent way of
knowing which record" then, assuming the query did pick one, it
certainly might be the wrong one, correct? So, wouldn't it be best if
the query picks both (or perhaps more than 2) and shows them to you?

So, let's work backward for a moment. Assuming the query remains as
is, how do you look at the data and say, "Aha, here's the one we
want." Is there an way you can know which row is the one? If not,
how did you expect the computer would know? Or do I still have some
misconception?

Remember, the computer is fast, but not very smart! It can only do
what you tell it to do, and it does that according to some rules
someone else made up, and which may contain bugs. Writing software
will always be an art. I'm not sure we are Computer Scientists. We
are often Computer Artists. Beauty (or correctness) is in the eye of
the beholder.

Sorry about the philosophy. It's really not my thing, but I enjoyed
trying!

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


On Wed, 21 Jul 2004 10:35:02 -0700, CASJAS
wrote:

Understood. I don't know how to go about doing that. The problem is, there isn't a consistent way of knowing which record it's going to need. It could be 3 fields ahead for one calculation and 5 the next. Here's an example of the data in the underlying table:

ID Site Date Monitored Opt Time Final EST VFH Conc1
1 999999 2/13/96 1 10
2 999999 2/14/96 24 6
3 999999 2/28/96 360 0
4 999999 3/13/96 696 50
5 999999 3/21/96 770 0
6 999999 3/22/96 770 0
7 999999 3/26/96 771 0
8 999999 4/9/96 1042 200
9 999999 4/25/96 1420 0
10 999999 5/7/96 1421 0
11 999999 5/21/96 1685 3500
12 999999 5/29/96 1686 0
13 999999 6/13/96 1732 260
14 999999 6/27/96 1732 0
15 999999 7/3/96 1732 0
16 999999 7/8/96 1852 0
17 999999 7/22/96 2188 500
18 999999 8/8/96 2596 0
19 999999 8/12/96 2692 0
20 999999 8/27/96 2860 0
21 999999 9/16/96 2860 800
22 999999 10/3/96 3268 0




"Tom Ellison" wrote:

Dear CASJAS:

As I tried to indicate before, a query doesn't find the rows it uses
in any particular order. It is best to think of it finding them all
at once.

Since there are two rows involved, it will not arbitrarily use one
rather than another. This results in two different calculated values.
If you want to choose one over the other, you must specify how you
want this done. The query will not randomly do this for you, unless
you build a random method into the query. How do you determine which
of the two different results you really want? If you have a method,
you could build that into the query so it will do what you want.

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


On Wed, 21 Jul 2004 09:54:03 -0700, CASJAS
wrote:

I've included my entire SQL below. C.[Opt Time Final] is part of an expression. The problem is, when the query returns the values, its running the calculation twice (or more) because it finds two Opt Time Final records that are identical where their [Est VFH Conc1] 0. I would like to have it perform the calculation on just the first C.[Opt time Final] value that it finds.

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]))
*(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;


"Tom Ellison" wrote:

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;






  #9  
Old July 21st, 2004, 08:58 PM
Tom Ellison
external usenet poster
 
Posts: n/a
Default Use first record found in expression?

Dear CASJAS:

I'm thinking you mean the MIN([Date Monitored]) where [Date Monitored]
C.[Opt Time Final] if you want the earliest [Date Monitored] that is

after C.[Opt Time Final] if you want the "next highest". But you're
the one who would know.

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


On Wed, 21 Jul 2004 11:05:02 -0700, CASJAS
wrote:

I don't think I'm explaining myself very well. I know which value to use, its always going to be the next available value in the [Est VFH Conc1] field. I just don't know WHERE that value is going to lie in relation to the calculation.

It's always going to be the correlated with the next highest [Date Monitored] where [Est VFH Conc1] 0. I guess I need to add a line to so that it looks at the MAX [Date Monitored] that is higher than the current [Date Monitored] when choosing the C.[Opt Time Final] it is supposed to use. Any suggestions?

"Tom Ellison" wrote:

Dear CASJAS:

Well, you said a mouthful. If "there isn't a consistent way of
knowing which record" then, assuming the query did pick one, it
certainly might be the wrong one, correct? So, wouldn't it be best if
the query picks both (or perhaps more than 2) and shows them to you?

So, let's work backward for a moment. Assuming the query remains as
is, how do you look at the data and say, "Aha, here's the one we
want." Is there an way you can know which row is the one? If not,
how did you expect the computer would know? Or do I still have some
misconception?

Remember, the computer is fast, but not very smart! It can only do
what you tell it to do, and it does that according to some rules
someone else made up, and which may contain bugs. Writing software
will always be an art. I'm not sure we are Computer Scientists. We
are often Computer Artists. Beauty (or correctness) is in the eye of
the beholder.

Sorry about the philosophy. It's really not my thing, but I enjoyed
trying!

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


On Wed, 21 Jul 2004 10:35:02 -0700, CASJAS
wrote:

Understood. I don't know how to go about doing that. The problem is, there isn't a consistent way of knowing which record it's going to need. It could be 3 fields ahead for one calculation and 5 the next. Here's an example of the data in the underlying table:

ID Site Date Monitored Opt Time Final EST VFH Conc1
1 999999 2/13/96 1 10
2 999999 2/14/96 24 6
3 999999 2/28/96 360 0
4 999999 3/13/96 696 50
5 999999 3/21/96 770 0
6 999999 3/22/96 770 0
7 999999 3/26/96 771 0
8 999999 4/9/96 1042 200
9 999999 4/25/96 1420 0
10 999999 5/7/96 1421 0
11 999999 5/21/96 1685 3500
12 999999 5/29/96 1686 0
13 999999 6/13/96 1732 260
14 999999 6/27/96 1732 0
15 999999 7/3/96 1732 0
16 999999 7/8/96 1852 0
17 999999 7/22/96 2188 500
18 999999 8/8/96 2596 0
19 999999 8/12/96 2692 0
20 999999 8/27/96 2860 0
21 999999 9/16/96 2860 800
22 999999 10/3/96 3268 0




"Tom Ellison" wrote:

Dear CASJAS:

As I tried to indicate before, a query doesn't find the rows it uses
in any particular order. It is best to think of it finding them all
at once.

Since there are two rows involved, it will not arbitrarily use one
rather than another. This results in two different calculated values.
If you want to choose one over the other, you must specify how you
want this done. The query will not randomly do this for you, unless
you build a random method into the query. How do you determine which
of the two different results you really want? If you have a method,
you could build that into the query so it will do what you want.

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


On Wed, 21 Jul 2004 09:54:03 -0700, CASJAS
wrote:

I've included my entire SQL below. C.[Opt Time Final] is part of an expression. The problem is, when the query returns the values, its running the calculation twice (or more) because it finds two Opt Time Final records that are identical where their [Est VFH Conc1] 0. I would like to have it perform the calculation on just the first C.[Opt time Final] value that it finds.

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]))
*(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;


"Tom Ellison" wrote:

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;







  #10  
Old July 22nd, 2004, 04:30 PM
CASJAS
external usenet poster
 
Posts: n/a
Default Use first record found in expression?

Hi,

Any suggestions?

"CASJAS" wrote:

I don't think I'm explaining myself very well. I know which value to use, its always going to be the next available value in the [Est VFH Conc1] field. I just don't know WHERE that value is going to lie in relation to the calculation.

It's always going to be the correlated with the next highest [Date Monitored] where [Est VFH Conc1] 0. I guess I need to add a line to so that it looks at the MAX [Date Monitored] that is higher than the current [Date Monitored] when choosing the C.[Opt Time Final] it is supposed to use. Any suggestions?

"Tom Ellison" wrote:

Dear CASJAS:

Well, you said a mouthful. If "there isn't a consistent way of
knowing which record" then, assuming the query did pick one, it
certainly might be the wrong one, correct? So, wouldn't it be best if
the query picks both (or perhaps more than 2) and shows them to you?

So, let's work backward for a moment. Assuming the query remains as
is, how do you look at the data and say, "Aha, here's the one we
want." Is there an way you can know which row is the one? If not,
how did you expect the computer would know? Or do I still have some
misconception?

Remember, the computer is fast, but not very smart! It can only do
what you tell it to do, and it does that according to some rules
someone else made up, and which may contain bugs. Writing software
will always be an art. I'm not sure we are Computer Scientists. We
are often Computer Artists. Beauty (or correctness) is in the eye of
the beholder.

Sorry about the philosophy. It's really not my thing, but I enjoyed
trying!

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


On Wed, 21 Jul 2004 10:35:02 -0700, CASJAS
wrote:

Understood. I don't know how to go about doing that. The problem is, there isn't a consistent way of knowing which record it's going to need. It could be 3 fields ahead for one calculation and 5 the next. Here's an example of the data in the underlying table:

ID Site Date Monitored Opt Time Final EST VFH Conc1
1 999999 2/13/96 1 10
2 999999 2/14/96 24 6
3 999999 2/28/96 360 0
4 999999 3/13/96 696 50
5 999999 3/21/96 770 0
6 999999 3/22/96 770 0
7 999999 3/26/96 771 0
8 999999 4/9/96 1042 200
9 999999 4/25/96 1420 0
10 999999 5/7/96 1421 0
11 999999 5/21/96 1685 3500
12 999999 5/29/96 1686 0
13 999999 6/13/96 1732 260
14 999999 6/27/96 1732 0
15 999999 7/3/96 1732 0
16 999999 7/8/96 1852 0
17 999999 7/22/96 2188 500
18 999999 8/8/96 2596 0
19 999999 8/12/96 2692 0
20 999999 8/27/96 2860 0
21 999999 9/16/96 2860 800
22 999999 10/3/96 3268 0




"Tom Ellison" wrote:

Dear CASJAS:

As I tried to indicate before, a query doesn't find the rows it uses
in any particular order. It is best to think of it finding them all
at once.

Since there are two rows involved, it will not arbitrarily use one
rather than another. This results in two different calculated values.
If you want to choose one over the other, you must specify how you
want this done. The query will not randomly do this for you, unless
you build a random method into the query. How do you determine which
of the two different results you really want? If you have a method,
you could build that into the query so it will do what you want.

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


On Wed, 21 Jul 2004 09:54:03 -0700, CASJAS
wrote:

I've included my entire SQL below. C.[Opt Time Final] is part of an expression. The problem is, when the query returns the values, its running the calculation twice (or more) because it finds two Opt Time Final records that are identical where their [Est VFH Conc1] 0. I would like to have it perform the calculation on just the first C.[Opt time Final] value that it finds.

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]))
*(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;


"Tom Ellison" wrote:

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;






 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Change and Save a Record sara Using Forms 0 July 17th, 2004 04:47 PM
Button to print current record only Fatz Using Forms 6 June 17th, 2004 01:58 PM
Form Doesn't Go To New Record Steve New Users 15 May 16th, 2004 04:33 PM
Form Does Not Go To New Record Steve New Users 1 May 12th, 2004 03:15 AM
Avoid Creating A Duplicate Record Mark New Users 4 May 11th, 2004 01:52 AM


All times are GMT +1. The time now is 06:38 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.