I am trying to convert an ACCESS database to BASE; I have used ACCESS for many years, but this is my first try with Base. One key thing I need to do is a simple join on two tables - one of them in fact a view - on a text field - the person’s name in fact. I am doing this in SQL with this query:
SELECT “results”.“date”, “concatenate names”.“id”, “results”.“score”, “results”.“section” FROM “concatenate names”, “results” WHERE “concatenate names”.“name” = “results”.“player1”
The entries in the ‘player1’ field in the ‘results’ table comprise 21 different values, all of which are present amongst the 300 entries in the ‘name’ field in ‘concatenate names’ yet this query produces zero rows; the spellings, spacings, etc are identical. Why? The database operates around the (numeric) ‘id’ field (as in ‘concatenate names’) but unfortunately this value is not available in the ‘results’ table, which comes from an external source.
Did you use the default settings for creating a new database to test your example? That would mean HSQLDB is the database engine and it has some limitations. Not sure if your problem is caused by one of them (yet).
Could you be so kind as to change your main question to “How do I join tables on text fields in Base?” (it’s mostly about ‘join on text fields’ instead of ‘joining text fields’). Thanks.
Have you tried using JOINS? Does your player 1 column function as the results table’s primary key? (i.e. confirm only 21 rows, each with a unique player 1 value). May we assume the names may be repeated in the concatenate names table?