INSERT INTO statement question

[megmoo75]

I am trying do an insert into a single table based on the results of another query. Both of my queries are SQL statements generated in the code of a form (based on the selections of the user), not from actual saved Access queries. The SQL statements are generated on the fly based on user selections.

I want to add new records to a table for each unique contact ID found in another query, and then set some of the other fields (other than contact ID) to the same value.

I can successfully generate a list of unique Contact ID's. Each of these should have a new row added to the ContactHistory table and several fields in each row need to be set to a particular value.

I have tried generating an "INSERT INTO" SQL statement with no success. Here is basically what I tried (with field names changed for simplicity):

INSERT INTO ContactHistory (ContactID, f1, f2, f4)
VALUES (
(SELECT DISTINCT ContactID FROM ContactParticipation WHERE ParticipationID = 13 or ParticipationID = 14),
"this value in f1",
"this value in f2",
"this value in f4"

I want this statement to do an insert for each contact ID returned in the "SELECT DISTINCT" portion of the query.

Does anyone have any suggestions as to how I can accomplish this?

 

[swampBoogie]

INSERT INTO ContactHistory (ContactID, f1, f2, f4)
SELECT DISTINCT ContactID ,
'this value in f1',
'this value in f2',
'this value in f4'
FROM ContactParticipation WHERE ParticipationID = 13 or ParticipationID = 14