How do I append rows to a table in Access from two related tables?

December 27, 2010 1:02 PM

How do I append rows to a table in Access from two related tables?

I have searched every Access forum I could find for this and I am banging my head against the wall.

I have a list of tasks that are related to PERSONS, in that the task is done for the person (not BY the person), and each person requires the same set of tasks.

I populated the first two tables (structure below).

PERSONINFO (PersonID, PersonName)
TASKS (TaskID, Task)
PERSONSTASKS (PersonID, TaskID, Completed)

I am at a loss as to how to populate the third. I've created the appropriate relationships between the tables. I want to populate the PERSONSTASKS table with PersonID,TaskID, Completed, so that I get:

PersonID | TaskID | Completed
__________________________
1 | 1 | (yes/no)
1 | 2 | (yes/no)
2 | 1 | (yes/no)
2 | 2 | (yes/no)

Remember again that the persons are not DOING these tasks; these are being done FOR them, thus I treat them like projects. I do not want the tasks entered into the PERSONSTASKS table as they are completed. I want them all there in the beginning, so I can mark them complete as they are done using a form.

I've tried append queries, update queries, goat sacrifice, nothing works. I get "You are about to append 0 rows" or "You are about to update 0 rows."

posted by desjardins to Computers & Internet (3 answers total)

Response by poster: By the way, here is my latest attempt at the append query (frm_checklist is where they enter a new person, I want to append the list of tasks to personstasks whenever a new record is created in the personinfo table):

INSERT INTO PersonsTasks ( PersonID, TaskID )
SELECT PersonInfo.PersonID, Tasks.TaskID
FROM PersonInfo LEFT JOIN (PersonsTasks LEFT JOIN Tasks ON PersonsTasks.TaskID = Tasks.TaskID) ON PersonInfo.PersonID = PersonsTasks.PersonID
WHERE (((PersonInfo.PersonID)=[Forms]![frm_checklist]![PersonID]));

Here I get "You are about to append 1 row." There are 31 records in the Tasks table and 14 records in Personinfo, fwiw.
posted by desjardins at 1:13 PM on December 27, 2010

Best answer: I wouldn't include the PersonInfo table at all, and would do something like this instead:

INSERT INTO PersonsTasks ( PersonID, TaskID )
SELECT [Forms]![frm_checklist]![PersonID], Tasks.TaskID
FROM Tasks;

It has been a while since I've used Access though, so I'm not 100% sure you can use a form field in a select list directly like that.
posted by FishBike at 1:18 PM on December 27, 2010

Response by poster: Holy crap. I owe you a beer or three.
posted by desjardins at 1:30 PM on December 27, 2010