Append Query

  • Thread starter Thread starter Secret Squirrel
  • Start date Start date
S

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;
 
INSERT INTO tblEmployeeReviews ( EmpID, ReviewDate, ReviewType)
SELECT tblEmployees.ID, DateAdd("m",3,[StartDate]) AS NextReview, "3 months"
as ReviewType
FROM tblEmployees;
 
Something like

INSERT INTO tblEmployeeReviews ( EmpID, ReviewDate, ReviewType )
SELECT tblEmployees.ID, DateAdd("m",3,[StartDate], "3 months") AS NextReview
FROM tblEmployees;
 
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;
 
Hi Tom,

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

Appreciate it!

Tom Wickerath said:
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
__________________________________________

Secret Squirrel said:
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;
Click to expand...
Click to expand...
 
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


Steve Schapel said:
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

Secret said:
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;
Click to expand...
Click to expand...
 
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
 
Nurse said:
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?
Click to expand...

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:]);
 
THAT WORKED!!!!

Thanks soooooooooooooooo much!!! :)
--
Nancy


Hans Up said:
Nurse said:
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?
Click to expand...

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:]);
Click to expand...
 
John said:
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.
Click to expand...

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)
 
James A. Fortune said:
John said:
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.
Click to expand...

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)
Click to expand...
 
Back
Top