append query question

[PattiP]

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.

 

[KARL DEWEY]

Use DateAdd function -- DateAdd("m", 1, [YourFieldName])

 

[louisjohnphillips]

Use DateAdd function --  DateAdd("m", 1, [YourFieldName])
--
KARL DEWEY
Build a little - Test a little

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, whenthe 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.

- 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

 

[PattiP]

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

Use DateAdd function -- DateAdd("m", 1, [YourFieldName])
--
KARL DEWEY
Build a little - Test a little

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.

 

[PattiP]

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

Use DateAdd function -- DateAdd("m", 1, [YourFieldName])
--
KARL DEWEY
Build a little - Test a little

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.

- 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

 

[KARL DEWEY]

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

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

Use DateAdd function -- DateAdd("m", 1, [YourFieldName])
--
KARL DEWEY
Build a little - Test a little

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.

 

[PattiP]

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

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

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

Use DateAdd function -- DateAdd("m", 1, [YourFieldName])
--
KARL DEWEY
Build a little - Test a little


:

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.