Append Query

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;
 
G

George Hepworth

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

Douglas J. Steele

Something like

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

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;
 
S

Secret Squirrel

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;
 
N

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


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;
 
J

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
 
H

Hans Up

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?

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:]);
 
N

Nurse Nancy

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?

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:]);
 
J

James A. Fortune

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.

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)
 
K

kate barbara snelleburg mamma di Stefano

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.

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query Date Problem 8
Append problem Duplicate key 0
dlookup in Query 10
Query Question 1
Append Query 3
Creating calculated fields for a query using data from another table 6
Update Query w/ Join 2
Query Lookup 0

Top