If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
append query question
I'm keeping track of monthly meter readings with a sample of 6 accounts. I
have created an append query that copies the previous month's accounts to the table and puts last month's current reading into this month's previous reading column and leaves the current reading with zeros or blank. I'm using the Month field and a parameter query to enter last month’s date (ex: 4/1/08) as the criteria in the query. This all works great. However, when the new records are created, they are also showing 4/1/08 in the Month field. I would like the new records to show this month’s date (5/1/08) so I don’t have to re-enter them. Can I do that in the same append query, or do I have to create an update query and run both? I would like it all to be in one step, if possible. -- Patti |
#2
|
|||
|
|||
append query question
Use DateAdd function -- DateAdd("m", 1, [YourFieldName])
-- KARL DEWEY Build a little - Test a little "PattiP" wrote: I'm keeping track of monthly meter readings with a sample of 6 accounts. I have created an append query that copies the previous month's accounts to the table and puts last month's current reading into this month's previous reading column and leaves the current reading with zeros or blank. I'm using the Month field and a parameter query to enter last month’s date (ex: 4/1/08) as the criteria in the query. This all works great. However, when the new records are created, they are also showing 4/1/08 in the Month field. I would like the new records to show this month’s date (5/1/08) so I don’t have to re-enter them. Can I do that in the same append query, or do I have to create an update query and run both? I would like it all to be in one step, if possible. -- Patti |
#3
|
|||
|
|||
append query question
On Jul 16, 1:59*pm, KARL DEWEY
wrote: Use DateAdd function -- *DateAdd("m", 1, [YourFieldName]) -- KARL DEWEY Build a little - Test a little "PattiP" wrote: I'm keeping track of monthly meter readings with a sample of 6 accounts.. *I have created an append query that copies the previous month's accounts to the table and puts last month's current reading into this month's previous reading column and leaves the current reading with zeros or blank. *I'm using the Month field and a parameter query to enter last month’s date (ex: 4/1/08) as the criteria in the query. *This all works great. *However, when the new records are created, they are also showing 4/1/08 in the Month field. *I would like the new records to show this month’s date (5/1/08) so I don’t have to re-enter them. *Can I do that in the same append query, or do I have to create an update query and run both? *I would like it all to be in one step, if possible. -- Patti- Hide quoted text - - Show quoted text - Would it make sense to try something like this? The "B" query finds the most recent-month and the current balance for that month. Add one month to the most-recent month and use the the CurrentBalance of last month for the PreviousBalance in this month. Finally, set the CurrentBalance for this month to be zero. INSERT into MeterReadings( ReadingMonth, PreviousBalance, CurrentBalance ) SELECT DateAdd( "m" , 1, B.ReadingMonth ) as ReadingMonth, CurrentBalance, 0 from ( SELECT ReadingMonth, CurrentBalance from MeterReadings as A ReadingMonth = ( SELECT Max( ReadingMonth ) from MeterReadings ) ) as B |
#4
|
|||
|
|||
append query question
Thanks, Karl.
that sort of worked, except it appended all my records, not just the ones from last month. Following are 2 different queries that I tried. the 2nd one I tried using a parameter query to select only last month's records. #1- this one copied ALL records: INSERT INTO tblTEST ( Location, MonthYear, PreviousReading ) SELECT tblTEST.Location, DateAdd("m",1,[MonthYear]) AS Expr1, tblTEST.CurrentReading FROM tblTEST; #2 - this one copied 0 records: INSERT INTO tblTEST ( Location, MonthYear, PreviousReading ) SELECT tblTEST.Location, DateAdd("m",1,[MonthYear]) AS Expr1, tblTEST.CurrentReading FROM tblTEST WHERE (((DateAdd("m",1,[MonthYear]))=[enter last month])); -- Patti "KARL DEWEY" wrote: Use DateAdd function -- DateAdd("m", 1, [YourFieldName]) -- KARL DEWEY Build a little - Test a little "PattiP" wrote: I'm keeping track of monthly meter readings with a sample of 6 accounts. I have created an append query that copies the previous month's accounts to the table and puts last month's current reading into this month's previous reading column and leaves the current reading with zeros or blank. I'm using the Month field and a parameter query to enter last month’s date (ex: 4/1/08) as the criteria in the query. This all works great. However, when the new records are created, they are also showing 4/1/08 in the Month field. I would like the new records to show this month’s date (5/1/08) so I don’t have to re-enter them. Can I do that in the same append query, or do I have to create an update query and run both? I would like it all to be in one step, if possible. -- Patti |
#5
|
|||
|
|||
append query question
Louis,
I tried typing your SQL, replacing your field names with mine, but not sure I'm understanding it. I keep getting syntax errors??? -- Patti " wrote: On Jul 16, 1:59 pm, KARL DEWEY wrote: Use DateAdd function -- DateAdd("m", 1, [YourFieldName]) -- KARL DEWEY Build a little - Test a little "PattiP" wrote: I'm keeping track of monthly meter readings with a sample of 6 accounts.. I have created an append query that copies the previous month's accounts to the table and puts last month's current reading into this month's previous reading column and leaves the current reading with zeros or blank. I'm using the Month field and a parameter query to enter last month’s date (ex: 4/1/08) as the criteria in the query. This all works great. However, when the new records are created, they are also showing 4/1/08 in the Month field. I would like the new records to show this month’s date (5/1/08) so I don’t have to re-enter them. Can I do that in the same append query, or do I have to create an update query and run both? I would like it all to be in one step, if possible. -- Patti- Hide quoted text - - Show quoted text - Would it make sense to try something like this? The "B" query finds the most recent-month and the current balance for that month. Add one month to the most-recent month and use the the CurrentBalance of last month for the PreviousBalance in this month. Finally, set the CurrentBalance for this month to be zero. INSERT into MeterReadings( ReadingMonth, PreviousBalance, CurrentBalance ) SELECT DateAdd( "m" , 1, B.ReadingMonth ) as ReadingMonth, CurrentBalance, 0 from ( SELECT ReadingMonth, CurrentBalance from MeterReadings as A ReadingMonth = ( SELECT Max( ReadingMonth ) from MeterReadings ) ) as B |
#6
|
|||
|
|||
append query question
INSERT INTO tblTEST ( Location, MonthYear, PreviousReading )
SELECT tblTEST.Location, DateAdd("m",1,[MonthYear]) AS Expr1, tblTEST.CurrentReading FROM tblTEST WHERE Format([MonthYear], "yyyymm") = Format(DateAdd("m", -1,Date()), "yyyymm"); Here it subtracts one month from today, formats to 200806 pulls records equal to the same formated date so only last month's records are pulled. -- KARL DEWEY Build a little - Test a little "PattiP" wrote: Thanks, Karl. that sort of worked, except it appended all my records, not just the ones from last month. Following are 2 different queries that I tried. the 2nd one I tried using a parameter query to select only last month's records. #1- this one copied ALL records: INSERT INTO tblTEST ( Location, MonthYear, PreviousReading ) SELECT tblTEST.Location, DateAdd("m",1,[MonthYear]) AS Expr1, tblTEST.CurrentReading FROM tblTEST; #2 - this one copied 0 records: INSERT INTO tblTEST ( Location, MonthYear, PreviousReading ) SELECT tblTEST.Location, DateAdd("m",1,[MonthYear]) AS Expr1, tblTEST.CurrentReading FROM tblTEST WHERE (((DateAdd("m",1,[MonthYear]))=[enter last month])); -- Patti "KARL DEWEY" wrote: Use DateAdd function -- DateAdd("m", 1, [YourFieldName]) -- KARL DEWEY Build a little - Test a little "PattiP" wrote: I'm keeping track of monthly meter readings with a sample of 6 accounts. I have created an append query that copies the previous month's accounts to the table and puts last month's current reading into this month's previous reading column and leaves the current reading with zeros or blank. I'm using the Month field and a parameter query to enter last month’s date (ex: 4/1/08) as the criteria in the query. This all works great. However, when the new records are created, they are also showing 4/1/08 in the Month field. I would like the new records to show this month’s date (5/1/08) so I don’t have to re-enter them. Can I do that in the same append query, or do I have to create an update query and run both? I would like it all to be in one step, if possible. -- Patti |
#7
|
|||
|
|||
append query question
Thank, Karl... that worked perfectly!!
However, I'll be a few months behind in updating so instead of using Date(), I inserted the parameter: [Enter next month] and it worked ! THANK YOU !!! -- Patti "KARL DEWEY" wrote: INSERT INTO tblTEST ( Location, MonthYear, PreviousReading ) SELECT tblTEST.Location, DateAdd("m",1,[MonthYear]) AS Expr1, tblTEST.CurrentReading FROM tblTEST WHERE Format([MonthYear], "yyyymm") = Format(DateAdd("m", -1,Date()), "yyyymm"); Here it subtracts one month from today, formats to 200806 pulls records equal to the same formated date so only last month's records are pulled. -- KARL DEWEY Build a little - Test a little "PattiP" wrote: Thanks, Karl. that sort of worked, except it appended all my records, not just the ones from last month. Following are 2 different queries that I tried. the 2nd one I tried using a parameter query to select only last month's records. #1- this one copied ALL records: INSERT INTO tblTEST ( Location, MonthYear, PreviousReading ) SELECT tblTEST.Location, DateAdd("m",1,[MonthYear]) AS Expr1, tblTEST.CurrentReading FROM tblTEST; #2 - this one copied 0 records: INSERT INTO tblTEST ( Location, MonthYear, PreviousReading ) SELECT tblTEST.Location, DateAdd("m",1,[MonthYear]) AS Expr1, tblTEST.CurrentReading FROM tblTEST WHERE (((DateAdd("m",1,[MonthYear]))=[enter last month])); -- Patti "KARL DEWEY" wrote: Use DateAdd function -- DateAdd("m", 1, [YourFieldName]) -- KARL DEWEY Build a little - Test a little "PattiP" wrote: I'm keeping track of monthly meter readings with a sample of 6 accounts. I have created an append query that copies the previous month's accounts to the table and puts last month's current reading into this month's previous reading column and leaves the current reading with zeros or blank. I'm using the Month field and a parameter query to enter last month’s date (ex: 4/1/08) as the criteria in the query. This all works great. However, when the new records are created, they are also showing 4/1/08 in the Month field. I would like the new records to show this month’s date (5/1/08) so I don’t have to re-enter them. Can I do that in the same append query, or do I have to create an update query and run both? I would like it all to be in one step, if possible. -- Patti |
Thread Tools | |
Display Modes | |
|
|