Copying boolean states?

edit

Thank you for your very prompt response. I won't be have time to try it out until tomorrow, but several careful re-reads of your answer are making me feel confident that it will be exactly what I'm looking for. When I have confirmed this by testing tomorrow, I'll be sure to mark the question as answered - THANK YOU

@Uncle Robin

From what I see you did not create a correct Case statement for one selection. See -> The SQL CASE Statement

In your image lower right you have (portion of SQL):

Case When "KEN" = 'YES' Then 1 Else 0   "Date", Case When "ACTIVE" = 'YES' Then 1 Else 0  END

In the first Case statement there is no END. Probably should be:

Case When "KEN" = 'YES' Then 1 Else 0 End,  "Date", Case When "ACTIVE" = 'YES' Then 1 Else 0  END

This is the cause of Unexpected token... you see.

Edit:

Based upon other answer and your comment there, it may be best to verify that your bool columns do in face only contain yes/no (seen you have presented different spelling of these) by doing a Select statement with a where clause. This may save you many headaches later.

THANK YOU! The problem was that missing END. Inserted that, all worked perfectly. SO grateful for your help, and feeling encouraged that my first attempt at playing with SQL came off so nearly right, despite being done at 4AM after a longish night shift.

A new wrinkle, sorry! When I added the missing END - the SQL command window said "command executed successfully", and the output window said "200 records updated". But when I compare the 2 tables, the boolean checkboxes are all empty. Following your suggestion above, I ran a SELECT WHERE on each of the two columns in Table3 that where meant to be boolean. The second of them has some "No" entries, as shown in the screenshot HERE . Obviously, I am still doing something wrong, do you have any suggestion what my mistake might be this time?

@Uncle Robin

What is needed is the SQL statement you used to create Table4.

Whoops! I did not create Table4 in SQL, I used "Create Table in Design View" from the GUI. I did notice that when I saved Table4 after doing so, the length of each of the Boolean fields was "1", but after running the import, and checking the fields in Edit mode, the length had changed to "0". I will try writing the table instead and see what I can learn.

I tried again, this time creating the table in SQL

DROP TABLE Table4 IF EXISTS;

CREATE TABLE Table4 ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY ,

FOO VARCHAR(255),

BAR VARCHAR(255),

FOOBAR1 VARCHAR(255),

LOREM VARCHAR(255),

IPSUM VARCHAR(255),

TOW VARCHAR(255),

KEN BOOLEAN NOT NULL ,

DATE DATE ,

ACTIVE BOOLEAN NOT NULL

);

And the table was created successfully. When I ran the copy/import, the same result as before "command executed successfully" "200 records updated", 200 empty boolean checkboxes

@Uncle Robin

Sorry you went through all that. Please see my previous comment.