Append

Question

Let's say that I have a table called [TBL] that has a yes/no fields called [Submitted], what I need to do is insert a checkmark in the [Submitted] field after [TBLQ] query is downloaded. Within in a form, I added a control to run the macro below. The macro downloads the [TBLQ] query results, but that's where it ends. The macro also runs an append query (or will), but I do not know how to tell the appends query to add a checkmark to [TBL] that doesn't exist. Am I making sense?

Answer 1

When I ran the update query, I expected ID 1,4, and 6 to be set to TRUE.

That’s what I’d expect.  You didn’t by any chance write the query as:

UPDATE TBL

SET TBL.Submitted = TRUE

WHERE EXISTS

    (SELECT *

     FROM TBLQ

     WHERE TBLQ.ID = TBLQ.ID);

Another method would be:

UPDATE TBL

SET TBL.Submitted = TRUE

WHERE ID IN

    (SELECT ID

     FROM TBLQ);

Answer 2

You probably need something like this:

UPDATE TBL INNER JOIN TBLQ

ON TBL.AccountNumber = TBLQ.AcountNumber

SET TBL.Submitted = TRUE;

By virtue of the INNER JOIN only those rows in TBL which have a match in TBLQ will be updated.

Answer 3

Gotcha, update not append. One more twist, though: The TBLQ query results will list only a few records, so not all records in the TBL table will be updated to true. So I need to include criteria to the update query. Something like: if TBLQ query [AccountNumber] Is Not Null, then set the submitted field in TBL to true. I tried joining TBLQ to TBL in the update query, but it spins off an error.

Answer 4

You are confusing an interface element, i.e., the Checkmark, with the value in the field, which is a Boolean. Booleans can have one of two possible values: True or False. In some implementations, the True value can be displayed as a filled in Checkmark , as "Yes", or even as some other token. The False value can be displayed as an unfilled in Checkmark, as "No" and so on. Fundamentally, True, in Access is the number -1, whereas False is the number 0. All else is an interface display element.

You don't need to add "a checkmark". As Scott pointed out, you need to set the field's value to true.

Answer 5

You need an UPDATE query.

UPDATE table SET field = True;