[Solved] Compare tables & automatically set Boolean state?

[Doranwen]

The database I'm working on is basically what's here: viewtopic.php?f=39&t=103544
I'd upload the most current version but it's split (not embedded anymore) and is too large now (over 3 MB). The tables and table relationships are essentially the same, but I've added a few new checkboxes and mucked around with the entries in pretty much all tables. (I've found using the ID columns for display order is best for the listboxes since they're in the logical order I expect to find them, and I've figured out some very quick and effective ways using SQL to "insert" records and shift all the data down if I suddenly remember an element I forgot or missed somehow, and it works beautifully.) I'm now getting quite comfortable using SQL to add & modify columns and insert the occasional new record that can't be done on the form (I could do it in the table directly but sometimes it's easier to just use the command if I've got a similar one I can tweak only a tiny bit to make it work).

One of the things I did was to import a large selection of words along with their frequencies for editing and classifying. However, in order to identify which ones belong to, say, the Dolch list, I've been having to keep a copy of that list open and check it whenever I think a word might be on it (or else search for each word on that list one by one and check the box when I find them). What I would like to do is, with that list or with any other list of words I want to match up, have a way of automatically identifying all words that belong to that list in one fell swoop.

Assuming I import the set of words in whatever list I'm wanting to identify (like the Dolch list, for example) into a new (unrelated) table, is there a way to take that table and automatically compare it with the Words table, looking for where the "Word" field matches, and automatically set the correct Boolean field in Words to True when it does? This link seemed to have a vaguely similar problem but I could not take the solutions provided and apply it to my problem, unfortunately: https://stackoverflow.com/questions/224 ... a-id-match

I have a second question to ask, but it's Forms-specific and will only be necessary if this is possible, lol.

[chrisb]

assuming tables are called "Word" & "Dolch"
&
field containing word in both tables is called "Word"
&
after backing up your tables
then
execute from menu:Tools>SQL

Code: Select all

update "Word" set "Dolch" =
(select true from "Dolch" where "Word" = "Word"."Word")
where 1 in
(select 1 from "Dolch" where "Word" = "Word"."Word")

Edit: the code above will set the boolean field "Dolch" = true where "Word" is matched & ignore those records where "Word" is not matched. the code below may be better suited to your needs. it will set the boolean field "Dolch" = true where "Word" is matched or set the boolean field "Dolch" = false where "Word" is not matched.

Code: Select all

update "Word" set "Dolch" =
coalesce(
(select true from "Dolch" where "Word" = "Word"."Word")
, false)

[Doranwen]

Thank you! That does indeed work. The first code is perfect for my needs because the Dolch Boolean field I already set to default to False.