[Solved] 2 tables in n to m relation

[robert leleu]

Two tables (liens, identite), each with an "ID" column, in a relation n to m through a 3d table (lieniden) with columns (liens_ID, identite_ID)

The form "liens" displays the table "liens" in its mainform grid
Also in the mainform is a textfield "fldID" displaying the ID value of the selected row.
and a subform grid displaying the "identite"s for a the selected row in "liens"

My aim is to retrieve the number of "identite" occurrences for the selected row in "liens"

A SQL instruction is able to get the answer when it uses the numeral value of ID in table "liens"

SELECT COUNT(*) AS "nbadhlien" FROM "lieniden" WHERE "liens_ID"=10
when used as SQL command in the data tab (Form properties) of the subform including a "nbadhlien" textfield displays the correct answer (89)

however the 3 here below trials are in error
SELECT COUNT(*) AS "nbadhlien" FROM "lieniden" WHERE "liens_ID"="fldID" Column not found: fldID
SELECT COUNT(*) AS "nbadhlien" FROM "lieniden" WHERE "liens_ID"="ID" Column not found: ID
SELECT COUNT(*) AS "nbadhlien" FROM "lieniden" WHERE "liens_ID"="liens.ID" Column not found: liens.ID

a first thank if you've read until here, and numerous others if you know the answer....

[MrProgrammer]

You want to use the FROM table JOIN table ON … syntax of the SELECT statement. Matching values from two tables is done with the ON clause, not the WHERE clause.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Villeroy]

@MrProgrammer
With today's database engines
SELECT * FROM A, B WHERE A.X=B.Y
is perfectly equivalent to
SELECT * FROM A JOIN B ON A.X=B.Y
In former times there was a perfomance loss when the engine fetched the full blown cartesian product AxB before filtering out the matching records by the WHERE clause.
But this is not what Robert wants to do anyway.

------------------------------

With 2 related tables it must be a 1 to n relation where each element n belongs to exactly one item in the parent table. With a n-m relation each element n may belong to multiple items of the other table and vice versa which requires a third mapping table.
Anyhow, your main form should be filled with an editable record set from one table and the subform should be filled with another record set from another table. In the subform properties you can define pairs of matching fields without using any SQL statements. Define the master fields of the parent form and the corresponding slave fields of the subform and the subform will show all the records with matching values in the corresponding fields of the parent form.
If a simple mapping A.X=B.Y and A.Y=B.Z is not enough, there are more sophisticated ways to get matching subform records from parameter queries.

[Example] Relations reflected by list boxes in forms is a demo with a 1-n relation between persons and animals (each animal belongs to one person or none) and a m-n relation between persons and things (the same thing may belong to many persons and vice versa). It contains some of the most common form layouts that can be derived from these relations so you are able to edit related data from all tables in one form.

[robert leleu]

Thanks for the clarification.
Indeed the form I am working on displays
- a MainForm grid showing all records of table "liens"
- a linked SubForm grid showing the records of query "liensiden" related to the selected row in the MainForm. This grid has a navigation bar, which says "record n of N" but "N" is just the number of displayed records.
I want to display the total number, the one which is displayed when one clicks the "last record" button, and to display it just by selecting a row in the MainForm grid.

I tried with macro and with a «SQL» field, and could report if useful.

Thanks for your help.

[Villeroy]

Create a second subform and bind it to

SELECT COUNT(*) AS "Count" FROM "lieniden" WHERE "liens_ID"= :fldID

:fldID is a named parameter. When you run the query as is, you'll be prompted for a value to substitute the parameter fldID. When you use it as a source of a subform, the parent form can substitute the parameter.

Master field: same as in the first subform
Slave field: fldID

Add a text field to the subform, bind it to the "Count" field.

[Villeroy]

Alternatively:
SELECT "liens_ID", COUNT(*) AS "Count" FROM "lieniden" GROUP BY "liens_ID"

Master field: same as in the first subform
Slave field: "liens_ID"

[robert leleu]

So fast, so convenient.
I was close.....but I had to learn about the column before a variable......

thanks