make a double relationship between two tables

In a database I have a table with members and a table with whishes. A whish is placed by one member and executed by another member. In a grid I want to see both the names of the member who places a whish and the member who execute the whish.
In a earlier question I was advised to make a join. This works fine for one relation (the member who places the whish). I cannot connect the name-info of the executing member from the member table in a querie to the grid.

I just solved my own problem by making a new querie as a copy of the members table and making a new relationship from the whishes table to the new querie membertable. Hope I did it right, for now it works :slight_smile:

Hi

It is not always necessary to create a new query. We can insert the same table multiple times in a query. It will be referenced by a table alias (Members_1 in the screenshot) to distinguish it as shown in the screenshot below (see Query1 in Wishes.odb).

Moreover, in a data grid, you can use a drop-down list to join as in the attached example (see Members form).

[EDIT]

It is possible to use the concatenation operator || in the Sql source of the listbox to display more columns (see Wishes form in WishesPys.odb.

Use a DataGrid requires a more complex macro programming…

Regards

Thank you very much for your answer. I realy liked the way you included an example.`
I still see queries and aliases are blanc ( ? ) but it gives me lots to think about.

To enter a whish I would like to use a list (from members table) for both PlacedBy as ExecutedBy. I tried a list (with and without input) but that only gives one field. Is there a way to make a small grid (three fields) to choose the right persons ?

I included my test odb for your information. (translation: members=leden, whishes=wensen) There are more catagories of whishes (garden, administration, transport, diy etc)
wbl1.odb

Tks, Hans

In your EDIT of the Question you asked about concatenation of columns in a list box.


Yes you can do this. If we have a Table Contacts with IDContact as an integer Primary Field and FirstName and Surname as Text fields. Then in the List Box Properties Bound field set to 1 and the List Box List content set to:-

SELECT “FirstName” || ’ - ’ || “Surname”, “IDContact” FROM “Contacts”


The List Box will show FirstName - Surname with the bound field IDContact.

@peterwt

I do not understand why this answer? I did not ask the question, I gave the answer with an example file

Regards.