Combining two fileds into one in a sbuform

[Ramylern]

I’m having trouble querying and combining two text fields into one in a subform in a way that updates the main form.

I have 2 tables:
"Composers" with the columns: ID, First name, Last name, Year of birth
"Works" with the columns: ID, name, genre, composer where "composer" is an INTEGER referencing Composers.ID.

I made a form for creating records of the Works table. I’d like there to be a combo box for the “composer” field where all the options are formatted as: "Composers"."Last Name", "Composers"."First Name"
e.g.

Beethoven, Ludwig van
Brahms, Johannes

and that picking a composer from the list will update the current record in Works with the correct composer number.

I tried to create a subform on the Composers table, and used the following SQL statement:

SELECT "Last Name" || ', ' || "First Name" AS "Composer", "ID" FROM "Composers"  INNER JOIN "Works" ON "Composers"."ID" = "Works"."Composer"; 

Unfortunately, that doesn’t work and won’t let me update/add composers to the current Works record. How can I do this in Base?

Thank you!

[Wanderer]

@Ramylern

Unfortunately, that doesn’t work and won’t let me update/add composers to the current Works record. How can I do this in Base?

For your problem use the answer by @UnklDonald but let me add a general remark: A SELECT will not update data.
You will need an UPDATE-command, and this can only sent via Tools-SQL from the menu or by macro. Something like

UPDATE Composers SET Composer = ...

Another option would be to use SELECT INTO Composers if you wish to populate another table.

[UnklDonald]

Your query should work as the Data Source of a List Box control.
See this tutorial for an example Combo vs List Box

[UnklDonald]

@Ramylern

Unfortunately, that doesn’t work

Sorry, I missed the scroll bar and only saw the first part of your query

SELECT "Last Name" || ', ' || "First Name" AS "Composer", "ID" FROM "Composers"
works fine as the data source in a List Box control.
See attached demonstration.
Demo66_ComposersLO.odb (13.9 KB)

I also normalized Genre, creating a table for that.

You could just convert the Genre column in Works to an Integer field and use a ListBox for that also but since some works span more than one Genre I added an intersection table that allows the selection of multiple Genre for a single piece.

There is a single Form document named Works where data can be viewed and edited, it contains 3 Forms and one SubForm plus two Push Buttons
A more robust database would have other Form documents for searching and filtering the data.

List Boxes do not allow you to add new entries on the fly (that would defeat the normalization of the field), so I added 2 Forms that allow adding new entries to Genre and Composers without leaving the Form document.

Combo boxes do allow adding new data on the fly, but at the cost of normalization. You can add misspelled names and other bogus entries which can be difficult to track down and correct.
Also, some advice. Don’t use white space in field names, that can come back to bite you. Either something like FirstName or First_Name is better.

[Ramylern]

Thanks for making all these clarifications, and for the demo database. it really helped me understand and organize my thoughts. And I love the data details you used in your example!