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 |
#21
|
|||
|
|||
Help with calculations in a query
Dear Tom,
I tried the query and it did work, however I am going to set up my table like you have said so that I have done it correctly. I am going to work on this for a while and I will get back to you later if that is okay. Will the same query work for the new table if I have set the table up like you suggested? Richard "Tom Ellison" wrote: Dear Richard: When it comes to being able to make an analysis like the one you mention, it really is much better to have 20 rows for 20 meters than just on row for 20 meters. But the real test is this. What would you have to do to add a new meter? If the answer is that you'd have to add a new column to a table and change all the forms and reports, then you have a severe normalization problem. For your more immediate needs, did you try the query I proposed? Tom Ellison "RWhittet" wrote in message ... Dear Tom, I'm not real sure if we are on the same page right now. I know that I have some normalization issues in this database, particuliarly with the naming, but I thought I had the tables set up correctly. We've been using the database for about three years and it's done more than anyone expected it to. I want to make sure I am understanding what you said correctly, so for 11/26/2005, I would have some 20 rows of data? The way I have understood it is that I should have one record for all of the day's meter readings, not 20. "Tom Ellison" wrote: Dear Richard: Looking at your data, I had already suspected you have a large amount of normalization problem. To be effective, your database should have one row for each combination of meter and date. There are excellent ways of doing this. Typically, you would have a table of all the meters involved. Each meter would be uniquely named. You could temprorarily fix this by creating a UNION query that creates this appearance. For the time being, then, all you comparisons are between a single record for all of today's readings and another record for all of yesterday's readings. The foundational query for this would be: SELECT * FROM [Meter Usage] MU0, [Meter Usage] MU1 WHERE MU0.Date = #11/26/05# AND MU1.Date = #11/25/05# This puts all the columns of 2 of your records together. You can then simply add a difference calculation between them. This could become complicated by the 255 column limitation for any table or query. Probably you wouldn't have much problem for now, but if you have more and more meters then it will become more and more of a problem. As it is, if you needed to add a meter you'd need to add a new column to the table for its data and then change all the reports and forms working with this. That's really the hard way of doing it. Tom Ellison "RWhittet" wrote in message ... Dear Tom, The relationship between the rows is determined by the date field. I apologize if the way I pasted the information is confusing. If you would prefer, I could figure out an easier way to get this information to you. Each row is the reading for one meter on one day. If you look to the far left column of the message, you will see the field names, such as:LT 24, LT 18, LT 16 and Filter 14-23. Each one of those represents a meter and the number that follows them is the meter reading for that day. Richard "Tom Ellison" wrote: Dear Richard: Looking at the two records you provided, the first question is this: On which column(s) is the relationship between the rows determined? How can I tell that these two rows are for the same meter? Is each row the reading for one meter on one day? Tom Ellison "RWhittet" wrote in message ... Dear Tom, I see that I forgot to give you some data. So I am reposting it, sorry. Table - Meter Usage ID # - Primary Key - 1719 Date - 8/17/05 Day - 230 Employee Name - Richard Whittet LT 24 - 0.49 LT 18 - 1.154 LT 16 - 2.592 CW 20 - 1.477 LT Mariana - 0.244 LTCW 42 - 11.396 LT 8 - 0.195 North Carter - * 499246 Comments 12 Inch RW - * 18635057 Turnout #1 - 0.98 North Recycle - * 488734 Filter 14 - * 137059 Filter 15 - * 122433 Filter 16 - * 3282807 Filter 17 - * 2755124 Filter 18 - * 2740611 Filter 19 - * 1275411 Filter 20 - * 1028674 Filter 21 - * 1136286 Filter 22 - * 1222160 Filter 23 - * 1072176 14 Inch BW - * 7540 South Recycle - * 1318149 South Recycle Pond Switch - yes South Recycle Pond - 1 North Recycle Pond Switch - no North Recycle Pond - 1 Demand High - 8.450 Demand Low AM - 5.550 CB-2 Minimum Level - 3.50 CB-3 Minimum Level - 4.21 3-4 Flowrate - 2.10 5-8 Flowrate - 4.56 Table - Meter Usage ID # - Primary Key - 1720 Date - 8/18/05 Day - 231 Employee Name - Richard Whittet LT 24 - 0.53 LT 18 - 1.235 LT 16 - 2.844 CW 20 - 1.427 LT Mariana - 0.315 LTCW 42 - 11.926 LT 8 - 0.213 North Carter - * 500049 Comments 12 Inch RW - * 18639524 Turnout #1 - 1.915 North Recycle - * 492855 Filter 14 - * 138449 Filter 15 - * 123823 Filter 16 - * 3284193 Filter 17 - * 2756494 Filter 18 - * 2741978 Filter 19 - * 1276836 Filter 20 - * 1030078 Filter 21 - * 1137684 Filter 22 - * 1223561 Filter 23 - * 1073563 14 Inch BW - * 7829 South Recycle - * 1319259 South Recycle Pond Switch - no South Recycle Pond - 3 North Recycle Pond Switch - no North Recycle Pond - 1 Demand High - 8.689 Demand Low AM - 4.689 CB-2 Minimum Level - 3.75 CB-3 Minimum Level - 4.15 3-4 Flowrate - 2.05 5-8 Flowrate - 4.50 Thanks again for your assistance. Richard "Tom Ellison" wrote: Dear Richard: Well, Richard, I'll tell you what I do know. I do know that I've tried various approaches to solving many database problems. From some successes and failures, I've come to the conclusion that the rules about how databases should be constructed are extremely valuable. And these rules say not to store any derived values in tables. The biggest reason for that is as follows: If you have a derived value, and any of the stored values that are components of that value are changed, then the derived value must immediately change as well. I have actually written the code that will make a derived value follow any database changes. It is about 5-10 times as much work as doing it correctly, and it tends to be unreliable. It is too easy to forget one of the things that might change and make the derived value incorrect. For example, when you do this, how will you handle the case that a user deletes the row containing the previous day's reading? How will you handle it if the user then re-enters that row, but with a different value? I'm telling you, it's a complex mess handling all these eventualities, if you write the system incorrectly. However, if you simply derive all the derived values at the moment you need them, then you won't have this problem. Everything in my 23 year's experience writing database software screams that this is a really bad approach. The kind of query you need is something I write several times a week, and have done so hundreds of times. In my own project, this would not take me 15 minutes. I say that not to brag (there are many who post answers here who have similar experience and capability) but to encourage you to learn the best skills and to apply them properly. Very soon you'll see that this becomes easy to handle, and that this is definitely the best way to handle the situation. As I see it, you're at a turning point in how you will develop as a database programmer. If you listen to good advise, and put it into practice, you'll be vastly better off. Perhaps others who are experienced and read this will drop in and lend a vote to what I'm telling you, so you'll see what I say is quite true, and important. Because what I'm advising you is not my personal preference, but a very common experience of virtually all who have advanced professionally in this field. |
#22
|
|||
|
|||
Help with calculations in a query
Dear Richard:
After you have redesigned the table, the query would need to change somewhat: SELECT * FROM [Meter Usage] MU0, [Meter Usage] MU1 WHERE MU0.Date = #11/26/05# AND MU1.Date = #11/25/05# AND MU1.MeterID = MU0.MeterID It may be better to construct this as an INNER JOIN as well. I'm also wondering what you have stored in this table other than a date and meter reading. Perhaps there are other table changes you should consider when doing this. Tom Ellison "RWhittet" wrote in message ... Dear Tom, I tried the query and it did work, however I am going to set up my table like you have said so that I have done it correctly. I am going to work on this for a while and I will get back to you later if that is okay. Will the same query work for the new table if I have set the table up like you suggested? Richard "Tom Ellison" wrote: Dear Richard: When it comes to being able to make an analysis like the one you mention, it really is much better to have 20 rows for 20 meters than just on row for 20 meters. But the real test is this. What would you have to do to add a new meter? If the answer is that you'd have to add a new column to a table and change all the forms and reports, then you have a severe normalization problem. For your more immediate needs, did you try the query I proposed? Tom Ellison "RWhittet" wrote in message ... Dear Tom, I'm not real sure if we are on the same page right now. I know that I have some normalization issues in this database, particuliarly with the naming, but I thought I had the tables set up correctly. We've been using the database for about three years and it's done more than anyone expected it to. I want to make sure I am understanding what you said correctly, so for 11/26/2005, I would have some 20 rows of data? The way I have understood it is that I should have one record for all of the day's meter readings, not 20. "Tom Ellison" wrote: Dear Richard: Looking at your data, I had already suspected you have a large amount of normalization problem. To be effective, your database should have one row for each combination of meter and date. There are excellent ways of doing this. Typically, you would have a table of all the meters involved. Each meter would be uniquely named. You could temprorarily fix this by creating a UNION query that creates this appearance. For the time being, then, all you comparisons are between a single record for all of today's readings and another record for all of yesterday's readings. The foundational query for this would be: SELECT * FROM [Meter Usage] MU0, [Meter Usage] MU1 WHERE MU0.Date = #11/26/05# AND MU1.Date = #11/25/05# This puts all the columns of 2 of your records together. You can then simply add a difference calculation between them. This could become complicated by the 255 column limitation for any table or query. Probably you wouldn't have much problem for now, but if you have more and more meters then it will become more and more of a problem. As it is, if you needed to add a meter you'd need to add a new column to the table for its data and then change all the reports and forms working with this. That's really the hard way of doing it. Tom Ellison "RWhittet" wrote in message ... Dear Tom, The relationship between the rows is determined by the date field. I apologize if the way I pasted the information is confusing. If you would prefer, I could figure out an easier way to get this information to you. Each row is the reading for one meter on one day. If you look to the far left column of the message, you will see the field names, such as:LT 24, LT 18, LT 16 and Filter 14-23. Each one of those represents a meter and the number that follows them is the meter reading for that day. Richard "Tom Ellison" wrote: Dear Richard: Looking at the two records you provided, the first question is this: On which column(s) is the relationship between the rows determined? How can I tell that these two rows are for the same meter? Is each row the reading for one meter on one day? Tom Ellison "RWhittet" wrote in message ... Dear Tom, I see that I forgot to give you some data. So I am reposting it, sorry. Table - Meter Usage ID # - Primary Key - 1719 Date - 8/17/05 Day - 230 Employee Name - Richard Whittet LT 24 - 0.49 LT 18 - 1.154 LT 16 - 2.592 CW 20 - 1.477 LT Mariana - 0.244 LTCW 42 - 11.396 LT 8 - 0.195 North Carter - * 499246 Comments 12 Inch RW - * 18635057 Turnout #1 - 0.98 North Recycle - * 488734 Filter 14 - * 137059 Filter 15 - * 122433 Filter 16 - * 3282807 Filter 17 - * 2755124 Filter 18 - * 2740611 Filter 19 - * 1275411 Filter 20 - * 1028674 Filter 21 - * 1136286 Filter 22 - * 1222160 Filter 23 - * 1072176 14 Inch BW - * 7540 South Recycle - * 1318149 South Recycle Pond Switch - yes South Recycle Pond - 1 North Recycle Pond Switch - no North Recycle Pond - 1 Demand High - 8.450 Demand Low AM - 5.550 CB-2 Minimum Level - 3.50 CB-3 Minimum Level - 4.21 3-4 Flowrate - 2.10 5-8 Flowrate - 4.56 Table - Meter Usage ID # - Primary Key - 1720 Date - 8/18/05 Day - 231 Employee Name - Richard Whittet LT 24 - 0.53 LT 18 - 1.235 LT 16 - 2.844 CW 20 - 1.427 LT Mariana - 0.315 LTCW 42 - 11.926 LT 8 - 0.213 North Carter - * 500049 Comments 12 Inch RW - * 18639524 Turnout #1 - 1.915 North Recycle - * 492855 Filter 14 - * 138449 Filter 15 - * 123823 Filter 16 - * 3284193 Filter 17 - * 2756494 Filter 18 - * 2741978 Filter 19 - * 1276836 Filter 20 - * 1030078 Filter 21 - * 1137684 Filter 22 - * 1223561 Filter 23 - * 1073563 14 Inch BW - * 7829 South Recycle - * 1319259 South Recycle Pond Switch - no South Recycle Pond - 3 North Recycle Pond Switch - no North Recycle Pond - 1 Demand High - 8.689 Demand Low AM - 4.689 CB-2 Minimum Level - 3.75 CB-3 Minimum Level - 4.15 3-4 Flowrate - 2.05 5-8 Flowrate - 4.50 Thanks again for your assistance. Richard "Tom Ellison" wrote: Dear Richard: Well, Richard, I'll tell you what I do know. I do know that I've tried various approaches to solving many database problems. From some successes and failures, I've come to the conclusion that the rules about how databases should be constructed are extremely valuable. And these rules say not to store any derived values in tables. The biggest reason for that is as follows: If you have a derived value, and any of the stored values that are components of that value are changed, then the derived value must immediately change as well. I have actually written the code that will make a derived value follow any database changes. It is about 5-10 times as much work as doing it correctly, and it tends to be unreliable. It is too easy to forget one of the things that might change and make the derived value incorrect. For example, when you do this, how will you handle the case that a user deletes the row containing the previous day's reading? How will you handle it if the user then re-enters that row, but with a different value? I'm telling you, it's a complex mess handling all these eventualities, if you write the system incorrectly. However, if you simply derive all the derived values at the moment you need them, then you won't have this problem. Everything in my 23 year's experience writing database software screams that this is a really bad approach. The kind of query you need is something I write several times a week, and have done so hundreds of times. In my own project, this would not take me 15 minutes. I say that not to brag (there are many who post answers here who have similar experience and capability) but to encourage you to learn the best skills and to apply them properly. Very soon you'll see that this becomes easy to handle, and that this is definitely the best way to handle the situation. As I see it, you're at a turning point in how you will develop as a database programmer. If you listen to good advise, and put it into practice, you'll be vastly better off. Perhaps others who are experienced and read this will drop in and lend a vote to what I'm telling you, so you'll see what I say is quite true, and important. Because what I'm advising you is not my personal preference, but a very common experience of virtually all who have advanced professionally in this field. |
#23
|
|||
|
|||
Help with calculations in a query
Dear Tom,
All that we are storing is a date and a meter reading. There are several calculations used just based on the readings when added as a whole or as a group, but after talking with you I think I can just create these calculations in my query and go from there. I have about 20 other tables in this database that aren't normalized so I will be getting to those soon. The issues I see arising from following all of the normalization rules is severe in our case because of the numerous parameters we look at and record daily. I hate to sound disappointed but I have already put so much time into this database and it's frustrating when you find out you've been wrong all along. I do appreciate your help in getting me on the right track. Richard "Tom Ellison" wrote: Dear Richard: After you have redesigned the table, the query would need to change somewhat: SELECT * FROM [Meter Usage] MU0, [Meter Usage] MU1 WHERE MU0.Date = #11/26/05# AND MU1.Date = #11/25/05# AND MU1.MeterID = MU0.MeterID It may be better to construct this as an INNER JOIN as well. I'm also wondering what you have stored in this table other than a date and meter reading. Perhaps there are other table changes you should consider when doing this. Tom Ellison "RWhittet" wrote in message ... Dear Tom, I tried the query and it did work, however I am going to set up my table like you have said so that I have done it correctly. I am going to work on this for a while and I will get back to you later if that is okay. Will the same query work for the new table if I have set the table up like you suggested? Richard "Tom Ellison" wrote: Dear Richard: When it comes to being able to make an analysis like the one you mention, it really is much better to have 20 rows for 20 meters than just on row for 20 meters. But the real test is this. What would you have to do to add a new meter? If the answer is that you'd have to add a new column to a table and change all the forms and reports, then you have a severe normalization problem. For your more immediate needs, did you try the query I proposed? Tom Ellison "RWhittet" wrote in message ... Dear Tom, I'm not real sure if we are on the same page right now. I know that I have some normalization issues in this database, particuliarly with the naming, but I thought I had the tables set up correctly. We've been using the database for about three years and it's done more than anyone expected it to. I want to make sure I am understanding what you said correctly, so for 11/26/2005, I would have some 20 rows of data? The way I have understood it is that I should have one record for all of the day's meter readings, not 20. "Tom Ellison" wrote: Dear Richard: Looking at your data, I had already suspected you have a large amount of normalization problem. To be effective, your database should have one row for each combination of meter and date. There are excellent ways of doing this. Typically, you would have a table of all the meters involved. Each meter would be uniquely named. You could temprorarily fix this by creating a UNION query that creates this appearance. For the time being, then, all you comparisons are between a single record for all of today's readings and another record for all of yesterday's readings. The foundational query for this would be: SELECT * FROM [Meter Usage] MU0, [Meter Usage] MU1 WHERE MU0.Date = #11/26/05# AND MU1.Date = #11/25/05# This puts all the columns of 2 of your records together. You can then simply add a difference calculation between them. This could become complicated by the 255 column limitation for any table or query. Probably you wouldn't have much problem for now, but if you have more and more meters then it will become more and more of a problem. As it is, if you needed to add a meter you'd need to add a new column to the table for its data and then change all the reports and forms working with this. That's really the hard way of doing it. Tom Ellison "RWhittet" wrote in message ... Dear Tom, The relationship between the rows is determined by the date field. I apologize if the way I pasted the information is confusing. If you would prefer, I could figure out an easier way to get this information to you. Each row is the reading for one meter on one day. If you look to the far left column of the message, you will see the field names, such as:LT 24, LT 18, LT 16 and Filter 14-23. Each one of those represents a meter and the number that follows them is the meter reading for that day. Richard "Tom Ellison" wrote: Dear Richard: Looking at the two records you provided, the first question is this: On which column(s) is the relationship between the rows determined? How can I tell that these two rows are for the same meter? Is each row the reading for one meter on one day? Tom Ellison "RWhittet" wrote in message ... Dear Tom, I see that I forgot to give you some data. So I am reposting it, sorry. Table - Meter Usage ID # - Primary Key - 1719 Date - 8/17/05 Day - 230 Employee Name - Richard Whittet LT 24 - 0.49 LT 18 - 1.154 LT 16 - 2.592 CW 20 - 1.477 LT Mariana - 0.244 LTCW 42 - 11.396 LT 8 - 0.195 North Carter - * 499246 Comments 12 Inch RW - * 18635057 Turnout #1 - 0.98 North Recycle - * 488734 Filter 14 - * 137059 Filter 15 - * 122433 Filter 16 - * 3282807 Filter 17 - * 2755124 Filter 18 - * 2740611 Filter 19 - * 1275411 Filter 20 - * 1028674 Filter 21 - * 1136286 Filter 22 - * 1222160 Filter 23 - * 1072176 14 Inch BW - * 7540 South Recycle - * 1318149 South Recycle Pond Switch - yes South Recycle Pond - 1 North Recycle Pond Switch - no North Recycle Pond - 1 Demand High - 8.450 Demand Low AM - 5.550 CB-2 Minimum Level - 3.50 CB-3 Minimum Level - 4.21 3-4 Flowrate - 2.10 5-8 Flowrate - 4.56 Table - Meter Usage ID # - Primary Key - 1720 Date - 8/18/05 Day - 231 Employee Name - Richard Whittet LT 24 - 0.53 LT 18 - 1.235 LT 16 - 2.844 CW 20 - 1.427 LT Mariana - 0.315 LTCW 42 - 11.926 LT 8 - 0.213 North Carter - * 500049 Comments 12 Inch RW - * 18639524 Turnout #1 - 1.915 North Recycle - * 492855 Filter 14 - * 138449 Filter 15 - * 123823 Filter 16 - * 3284193 Filter 17 - * 2756494 Filter 18 - * 2741978 Filter 19 - * 1276836 Filter 20 - * 1030078 Filter 21 - * 1137684 Filter 22 - * 1223561 Filter 23 - * 1073563 14 Inch BW - * 7829 South Recycle - * 1319259 South Recycle Pond Switch - no South Recycle Pond - 3 North Recycle Pond Switch - no North Recycle Pond - 1 Demand High - 8.689 Demand Low AM - 4.689 CB-2 Minimum Level - 3.75 CB-3 Minimum Level - 4.15 3-4 Flowrate - 2.05 5-8 Flowrate - 4.50 Thanks again for your assistance. Richard "Tom Ellison" wrote: Dear Richard: Well, Richard, I'll tell you what I do know. I do know that I've tried various approaches to solving many database problems. From some successes and failures, I've come to the conclusion that the rules about how databases should be constructed are extremely valuable. And these rules say not to store any derived values in tables. The biggest reason for that is as follows: If you have a derived value, and any of the stored values that are components of that value are changed, then the derived value must immediately change as well. I have actually written the code that will make a derived value follow any database changes. It is about 5-10 times as much work as doing it correctly, and it tends to be unreliable. It is too easy to forget one of the things that might change and make the derived value incorrect. For example, when you do this, how will you handle the case that a |
#24
|
|||
|
|||
Help with calculations in a query
Dear Tom,
I wanted to thank you again for assisting me with this issue. The query you built has allowed me to calculate everything I needed to with our meters. I am grateful that all of you who help us rookies out are willing to do so. Sincerely, Richard Whittet "Tom Ellison" wrote: Dear John: Thanks for the kind words. I've known John as a fellow MVP (which we are not at the moment) and met him in Seattle in 2004 at the MVP Summit. He, like all the MVPs I've met, is very good at what he does and generous with his good advise. To hear from him like this is gratifying. Hope to see you at one of the MVP confabs again, John! Betsy sends her greeting, too! Tom Ellison "John Spencer" wrote in message ... Well, you are getting much more comprehensive advice from Tom Ellison. If you can, use his advice. The DLookup function expects strings for its arguments, so Expr1: DLookUp("[South Recycle]","[Meter Usage]", "[Day]=#"& DateDiff("d",-1,[Day]) &"#") The third argument is basically a WHERE clause without "WHERE" at the beginning of the clause. It should end up being a string that looks like "[Day]=#11/12/2005#" Where 11/12/2005 is one less than the value of Day in the current record. Like I said work with Tom Ellison - he will help you develop a robust solution that will take into account the missing data. RWhittet wrote: Is there a way to manipulate this expression to where it will work in the query? I have been trying different things but it always returns the same days reading. I think the problem lies in the criteria of the argument. Also, I'm not good with the SQL language so if you could retype it using my field and table names that would be great. Expr1: DLookUp([South Recycle],"Meter Usage",[Day]-1) "John Spencer" wrote: If you have one reading for every day then this is fairly simple. If you have multiple readings or days get skipped then this gets to be a bit more complex. Simplest case - one reading EVERY day. (Substitute your field and table names) SELECT A.MeterID, A.ReadingDate, (A.Reading - B.Reading)/1000 FROM YourTable as A INNER JOIN YourTable As B ON A.ReadingDate = B.ReadingDate -1 AND R.MeterID = B.MeterID This will return NO record (row) for any case where there is no immediate prior date. So for instance the oldest record in the table will not be in your list. By the way, if you can do it with DLookup on a form, you should be able to transfer that same logic into the query. You could post your DLookup code and someone may be able to suggest how you can use that to get the desired results. RWhittet wrote: I have run into a problem in my query. I am trying to run calculations on water meters. I have been able to calculate the information I need on my forms, by using DLookup, but it's a bit more tricky to do in the query. These meters are read everyday, and the calculations needed are also daily as they convert the value into million gallons per day. The calculation is as follows; (Day 2 meter reading - Day 1 meter reading)/1000 All of the readings are stored in the same table, so the biggest problem is referencing the value from the previous record. I have also been able to create the calculations in my reports, but I'm unable to use a monthly average or sum on a calculated field in a report. Any solutions or suggestions would be greatly appreciated! Richard Whittet |
#25
|
|||
|
|||
Help with calculations in a query
Dear Richard:
I'm glad to hear you're underway on this. If you have future difficulty, you may certainly come back here with further questions. Tom Ellison "RWhittet" wrote in message ... Dear Tom, I wanted to thank you again for assisting me with this issue. The query you built has allowed me to calculate everything I needed to with our meters. I am grateful that all of you who help us rookies out are willing to do so. Sincerely, Richard Whittet "Tom Ellison" wrote: Dear John: Thanks for the kind words. I've known John as a fellow MVP (which we are not at the moment) and met him in Seattle in 2004 at the MVP Summit. He, like all the MVPs I've met, is very good at what he does and generous with his good advise. To hear from him like this is gratifying. Hope to see you at one of the MVP confabs again, John! Betsy sends her greeting, too! Tom Ellison "John Spencer" wrote in message ... Well, you are getting much more comprehensive advice from Tom Ellison. If you can, use his advice. The DLookup function expects strings for its arguments, so Expr1: DLookUp("[South Recycle]","[Meter Usage]", "[Day]=#"& DateDiff("d",-1,[Day]) &"#") The third argument is basically a WHERE clause without "WHERE" at the beginning of the clause. It should end up being a string that looks like "[Day]=#11/12/2005#" Where 11/12/2005 is one less than the value of Day in the current record. Like I said work with Tom Ellison - he will help you develop a robust solution that will take into account the missing data. RWhittet wrote: Is there a way to manipulate this expression to where it will work in the query? I have been trying different things but it always returns the same days reading. I think the problem lies in the criteria of the argument. Also, I'm not good with the SQL language so if you could retype it using my field and table names that would be great. Expr1: DLookUp([South Recycle],"Meter Usage",[Day]-1) "John Spencer" wrote: If you have one reading for every day then this is fairly simple. If you have multiple readings or days get skipped then this gets to be a bit more complex. Simplest case - one reading EVERY day. (Substitute your field and table names) SELECT A.MeterID, A.ReadingDate, (A.Reading - B.Reading)/1000 FROM YourTable as A INNER JOIN YourTable As B ON A.ReadingDate = B.ReadingDate -1 AND R.MeterID = B.MeterID This will return NO record (row) for any case where there is no immediate prior date. So for instance the oldest record in the table will not be in your list. By the way, if you can do it with DLookup on a form, you should be able to transfer that same logic into the query. You could post your DLookup code and someone may be able to suggest how you can use that to get the desired results. RWhittet wrote: I have run into a problem in my query. I am trying to run calculations on water meters. I have been able to calculate the information I need on my forms, by using DLookup, but it's a bit more tricky to do in the query. These meters are read everyday, and the calculations needed are also daily as they convert the value into million gallons per day. The calculation is as follows; (Day 2 meter reading - Day 1 meter reading)/1000 All of the readings are stored in the same table, so the biggest problem is referencing the value from the previous record. I have also been able to create the calculations in my reports, but I'm unable to use a monthly average or sum on a calculated field in a report. Any solutions or suggestions would be greatly appreciated! Richard Whittet |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
adding 2 fields including null entries | Jesse | Running & Setting Up Queries | 26 | January 18th, 2005 05:31 PM |
AVG Function in a Query | JohnL | Running & Setting Up Queries | 5 | December 18th, 2004 05:52 AM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
Too Few Parameters error Mail Merge Access Parameter Query | Tony_VBACoder | Mailmerge | 3 | September 14th, 2004 12:15 PM |
Hidden files in Ms-Query cause ODBC connect errors or Query is wac | needyourhelp | General Discussion | 4 | July 12th, 2004 09:38 PM |