Append Query

[Secret Squirrel]

I have the following append query updating one of my tables. I have another
field in this table that is being updated called "ReviewType". When this
append fires how I can have it automatically put "3 months" in that field for
the records it is appending?

INSERT INTO tblEmployeeReviews ( EmpID, ReviewDate )
SELECT tblEmployees.ID, DateAdd("m",3,[StartDate]) AS NextReview
FROM tblEmployees;

 

[George Hepworth]

INSERT INTO tblEmployeeReviews ( EmpID, ReviewDate, ReviewType)
SELECT tblEmployees.ID, DateAdd("m",3,[StartDate]) AS NextReview, "3 months"
as ReviewType
FROM tblEmployees;

 

[Douglas J. Steele]

Something like

INSERT INTO tblEmployeeReviews ( EmpID, ReviewDate, ReviewType )
SELECT tblEmployees.ID, DateAdd("m",3,[StartDate], "3 months") AS NextReview
FROM tblEmployees;

 

[Tom Wickerath]

Hi Secret Squirrel,

I think something like this will work:

INSERT INTO tblEmployeeReviews ( EmpID, ReviewDate, ReviewType )
SELECT tblEmployees.ID, DateAdd("m",3,[StartDate]) AS NextReview,
"3 Months" As [Review Type]
FROM tblEmployees;


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Steve Schapel]

Squirrel,

You mean like this?...

INSERT INTO tblEmployeeReviews ( EmpID, ReviewDate, ReviewType )
SELECT tblEmployees.ID, DateAdd("m",3,[StartDate]) AS NextReview, "3
months" As RType
FROM tblEmployees;

 

[Secret Squirrel]

Hi Tom,

Hope all is well. Thank you for your help. That works perfectly!

Appreciate it!

Hi Secret Squirrel,

I think something like this will work:

INSERT INTO tblEmployeeReviews ( EmpID, ReviewDate, ReviewType )
SELECT tblEmployees.ID, DateAdd("m",3,[StartDate]) AS NextReview,
"3 Months" As [Review Type]
FROM tblEmployees;


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

I have the following append query updating one of my tables. I have another
field in this table that is being updated called "ReviewType". When this
append fires how I can have it automatically put "3 months" in that field for
the records it is appending?

INSERT INTO tblEmployeeReviews ( EmpID, ReviewDate )
SELECT tblEmployees.ID, DateAdd("m",3,[StartDate]) AS NextReview
FROM tblEmployees;

 

[Nurse Nancy]

I think my issue is similar but i am not sure....

I have an append query and I want to prompt the user for a 'start date' when
the query is run, but i don't want it to be a selection criteria, i want it
to be one of the fields added inserted into the table.

Is this doable?



--
Nancy

Squirrel,

You mean like this?...

INSERT INTO tblEmployeeReviews ( EmpID, ReviewDate, ReviewType )
SELECT tblEmployees.ID, DateAdd("m",3,[StartDate]) AS NextReview, "3
months" As RType
FROM tblEmployees;

--
Steve Schapel, Microsoft Access MVP

I have the following append query updating one of my tables. I have another
field in this table that is being updated called "ReviewType". When this
append fires how I can have it automatically put "3 months" in that field for
the records it is appending?

INSERT INTO tblEmployeeReviews ( EmpID, ReviewDate )
SELECT tblEmployees.ID, DateAdd("m",3,[StartDate]) AS NextReview
FROM tblEmployees;

 

[John Spencer]

Yes.

INSERT INTO SomeTable (Field1, Field2)
SELECT EmployeeName, [Please Enter Start Date]
FROM tblEmployees

That would add records to some table with the employeeName in field 1 and
whatever is input in response the prompt Please Enter Start Date.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Hans Up]

I have an append query and I want to prompt the user for a 'start date' when
the query is run, but i don't want it to be a selection criteria, i want it
to be one of the fields added inserted into the table.

Is this doable?

This append query inserts the value "foo" into a text field:

INSERT INTO tblFoo (MyTextField)
VALUES ("foo");

This version uses a parameter to collect the value to insert:

INSERT INTO tblFoo (MyTextField)
VALUES ([Give me a value:]);

I verified that works with Access 2007.

You're asking about a date value. I didn't try that one specifically,
but it might work like this:

PARAMETERS [Give me a date:] DATETIME;
INSERT INTO tblFoo (MyDateField)
VALUES ([Give me a date:]);

 

[Nurse Nancy]

THAT WORKED!!!!

Thanks soooooooooooooooo much!!!
--
Nancy

I have an append query and I want to prompt the user for a 'start date' when
the query is run, but i don't want it to be a selection criteria, i want it
to be one of the fields added inserted into the table.

Is this doable?

This append query inserts the value "foo" into a text field:

INSERT INTO tblFoo (MyTextField)
VALUES ("foo");

This version uses a parameter to collect the value to insert:

INSERT INTO tblFoo (MyTextField)
VALUES ([Give me a value:]);

I verified that works with Access 2007.

You're asking about a date value. I didn't try that one specifically,
but it might work like this:

PARAMETERS [Give me a date:] DATETIME;
INSERT INTO tblFoo (MyDateField)
VALUES ([Give me a date:]);

 

[James A. Fortune]

Yes.

INSERT INTO SomeTable (Field1, Field2)
SELECT EmployeeName, [Please Enter Start Date]
FROM tblEmployees

That would add records to some table with the employeeName in field 1
and whatever is input in response the prompt Please Enter Start Date.

I made an observation after reading your post and performing a few
tests. The observation turns out to be quite useless for this
particular thread, but it seemed interesting anyway. Instead of doing:

INSERT INTO SomeTable (Field1, Field2)
SELECT EmployeeName, [Please Enter Start Date]
FROM tblEmployees;

or

INSERT INTO SomeTable (Field1, Field2)
VALUES ("June Smith", #9/9/09#);

I tried:

INSERT INTO SomeTable (Field1, Field2)
SELECT "June Smith", #9/9/09#;

I.e., without specifying a table name. It acted exactly like the query
that used VALUES. My guess at this point in time is that the behavior
looks particularly useless for anything at all, but I'll reserve
judgment until all possible potential uses have been exhausted . For
what it's worth, it did save typing two characters.

James A. Fortune
(e-mail address removed)

 

[kate barbara snelleburg mamma di Stefano]

Yes.

INSERT INTO SomeTable (Field1, Field2)
SELECT EmployeeName, [Please Enter Start Date]
FROM tblEmployees

That would add records to some table with the employeeName in field 1 and
whatever is input in response the prompt Please Enter Start Date.

I made an observation after reading your post and performing a few tests.
The observation turns out to be quite useless for this particular thread,
but it seemed interesting anyway. Instead of doing:

INSERT INTO SomeTable (Field1, Field2)
SELECT EmployeeName, [Please Enter Start Date]
FROM tblEmployees;

or

INSERT INTO SomeTable (Field1, Field2)
VALUES ("June Smith", #9/9/09#);

I tried:

INSERT INTO SomeTable (Field1, Field2)
SELECT "June Smith", #9/9/09#;

I.e., without specifying a table name. It acted exactly like the query
that used VALUES. My guess at this point in time is that the behavior
looks particularly useless for anything at all, but I'll reserve judgment
until all possible potential uses have been exhausted . For what it's
worth, it did save typing two characters.

James A. Fortune
(e-mail address removed)