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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |