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 |
#11
|
|||
|
|||
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; |
#12
|
|||
|
|||
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; |
#13
|
|||
|
|||
Use first record found in expression?
Dear CASJAS:
I posted this, but I don't see where you responded to it: 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. I suggest you add such a criterion to choose the specific row you want. Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Thu, 22 Jul 2004 08:31:30 -0700, CASJAS wrote: 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; |
#14
|
|||
|
|||
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 MIN(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 MIN(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: I posted this, but I don't see where you responded to it: 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. I suggest you add such a criterion to choose the specific row you want. Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Thu, 22 Jul 2004 08:31:30 -0700, CASJAS wrote: 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; |
#15
|
|||
|
|||
Use first record found in expression?
Dear CASJAS:
What you have is moderately complex, and I do not know the details of what it is supposed to accomplish. Here's how I'd suggest you can work toward figuring it out yourself. Save a copy of the SQL in a text editor. Remove one of the three long creteria at a time and find out which one(s) is/are keeping the query from returning any rows. Look at the data and make sure there are rows that you should be seeing. If there are, then the clause that, when removed, begins to show results, is the one that is in error. I was a bit surprised that the line you added most recently is based on values, not dates. But, if that's what you intend, then that's what it should be. Now this new line does something peculiar to my eye. The WHERE clause of the subquery reference the table aliased "B". Was that supposed to be an X? Just a guess. Well, likely it's this new line where the problem is, right? Let me know if this gets you closer, OK? Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Thu, 22 Jul 2004 10:02:04 -0700, CASJAS wrote: I added the following to the SQL but it's taking forever to load and coming up blank: AND ((B.[DM VAL])=(Select MIN(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 MIN(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: I posted this, but I don't see where you responded to it: 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. I suggest you add such a criterion to choose the specific row you want. Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Thu, 22 Jul 2004 08:31:30 -0700, CASJAS wrote: 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; |
#16
|
|||
|
|||
Use first record found in expression?
What about implementing a "Top 1" function?
"Tom Ellison" wrote: Dear CASJAS: What you have is moderately complex, and I do not know the details of what it is supposed to accomplish. Here's how I'd suggest you can work toward figuring it out yourself. Save a copy of the SQL in a text editor. Remove one of the three long creteria at a time and find out which one(s) is/are keeping the query from returning any rows. Look at the data and make sure there are rows that you should be seeing. If there are, then the clause that, when removed, begins to show results, is the one that is in error. I was a bit surprised that the line you added most recently is based on values, not dates. But, if that's what you intend, then that's what it should be. Now this new line does something peculiar to my eye. The WHERE clause of the subquery reference the table aliased "B". Was that supposed to be an X? Just a guess. Well, likely it's this new line where the problem is, right? Let me know if this gets you closer, OK? Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Thu, 22 Jul 2004 10:02:04 -0700, CASJAS wrote: I added the following to the SQL but it's taking forever to load and coming up blank: AND ((B.[DM VAL])=(Select MIN(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 MIN(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: I posted this, but I don't see where you responded to it: 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. I suggest you add such a criterion to choose the specific row you want. Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Thu, 22 Jul 2004 08:31:30 -0700, CASJAS wrote: 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; |
#17
|
|||
|
|||
Use first record found in expression?
What about using "Top 1" function in combination with the MAX function?
"Tom Ellison" wrote: Dear CASJAS: What you have is moderately complex, and I do not know the details of what it is supposed to accomplish. Here's how I'd suggest you can work toward figuring it out yourself. Save a copy of the SQL in a text editor. Remove one of the three long creteria at a time and find out which one(s) is/are keeping the query from returning any rows. Look at the data and make sure there are rows that you should be seeing. If there are, then the clause that, when removed, begins to show results, is the one that is in error. I was a bit surprised that the line you added most recently is based on values, not dates. But, if that's what you intend, then that's what it should be. Now this new line does something peculiar to my eye. The WHERE clause of the subquery reference the table aliased "B". Was that supposed to be an X? Just a guess. Well, likely it's this new line where the problem is, right? Let me know if this gets you closer, OK? Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Thu, 22 Jul 2004 10:02:04 -0700, CASJAS wrote: I added the following to the SQL but it's taking forever to load and coming up blank: AND ((B.[DM VAL])=(Select MIN(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 MIN(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: I posted this, but I don't see where you responded to it: 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. I suggest you add such a criterion to choose the specific row you want. Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Thu, 22 Jul 2004 08:31:30 -0700, CASJAS wrote: 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; |
#18
|
|||
|
|||
Use first record found in expression?
Dear CASJAS:
TOP will not arbitrate between ties. If you ask for TOP 1 where there is a 3 way tie, you'll still get all 3 rows. This is a fundamental principle of database engines. They will not resolve a conflict arbitrarily. The results they give are predictable, repeatable, unambiguous, and never arbitrary. This is a good thing! But it makes it very diffucult to implement fuzzy logic. And that is on purpose. Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Thu, 22 Jul 2004 12:03:04 -0700, CASJAS wrote: What about using "Top 1" function in combination with the MAX function? "Tom Ellison" wrote: Dear CASJAS: What you have is moderately complex, and I do not know the details of what it is supposed to accomplish. Here's how I'd suggest you can work toward figuring it out yourself. Save a copy of the SQL in a text editor. Remove one of the three long creteria at a time and find out which one(s) is/are keeping the query from returning any rows. Look at the data and make sure there are rows that you should be seeing. If there are, then the clause that, when removed, begins to show results, is the one that is in error. I was a bit surprised that the line you added most recently is based on values, not dates. But, if that's what you intend, then that's what it should be. Now this new line does something peculiar to my eye. The WHERE clause of the subquery reference the table aliased "B". Was that supposed to be an X? Just a guess. Well, likely it's this new line where the problem is, right? Let me know if this gets you closer, OK? Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Thu, 22 Jul 2004 10:02:04 -0700, CASJAS wrote: I added the following to the SQL but it's taking forever to load and coming up blank: AND ((B.[DM VAL])=(Select MIN(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 MIN(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: I posted this, but I don't see where you responded to it: 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. I suggest you add such a criterion to choose the specific row you want. Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Thu, 22 Jul 2004 08:31:30 -0700, CASJAS wrote: 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 |