append query using checkbox field

[elinorigb]

I'm trying to run an append query that will add multiple books to a single record. The single record comes from Table1, the records for books are located in Table2, and Table3 basically links Table1 and Table2 together. For the purposes of the query, I'm not using Table2 (the books table). Instead, I'm using a query of the books that have been checked, as qryCheckBooks.

The query is supposed to append data into the linking table (Table 3), which would include the latest RecordID, along the BookIDs that have been checked from a form. These are the only two fields in the table.

I've tried many things in my SQL statement, but either no records are added or way too many records are added. It seems like there's some kind of looping problem, but I don't remember a lot about FOR loops. I was hoping I could make this work in SQL. Basically, the amount of rows appended seems to be affected by the current number of rows in Table3.

Here's an example of one of the MANY things I've tried:

INSERT INTO Table3 ( BookID, RecordID )
SELECT [qryCheckBook].[BookID] AS SelectedBooks,
[Table1].[RecordID]
FROM Table1, qryCheckBook INNER JOIN Table3 ON [qryCheckBook].[BookID]=[Table3].[BookID]
WHERE ((([Table1].[RecordID])=DMax("[RecordID]","Table1")));

thanks in advance

 

[JonFer]

Don't include Table3 in your FROM clause - you aren't using any fields from it. qryCheckBook has the BookIDs to append and Table1 has the RecordID so that is all you need.

 

[elinorigb]

That's it! So simple that, of course I would overlook it. Sometimes I can't get used to the way Access complicates things. Thanks again!