# 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.

edit retag close merge delete

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 :)

( 2015-12-15 22:29:49 +0200 )edit

Sort by » oldest newest most voted

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

more

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) C:\fakepath\wbl1.odb

Tks, Hans

more

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.

more

@peterwt

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

Regards.

( 2015-12-24 07:33:17 +0200 )edit