I have a photo database with the photos and the People figuring in the photos. The database contains two tables (photos, People). The photo tabel references the People table by the People index.
for bystanders I want to create a form that shows the first photo in the photos table for every person in the People table.
Distinct en (MIN) don’t seemto work.
Any suggestions?
Thanks
How do you get the “first”? Do you want to decide it by primarykey “ID”?
SELECT
"PeopleID", MIN("PhotoID") AS "PID"
FROM "Phototable"
GROUP BY "PeopleID"
could be a solution if “PhotoID” is the value, which should decide to be first photo.
We have no idea how your database looks like. Do you know the term “many-to-many relation”?
This is the real database. The second table is necessary because i wanted to identify up to 5 persons in each photo.
To simplify the question I considered the case where only one person is to be identified in each photo so the second table would not be wanted.
What I want is
For each record in table Personen there is a collection of records referencing this record (i.e. each person has multiple photos in Foto table)
For each person in the “Personen” table I want to display one and only one photo from the “Fotos” table (probably the first)
A many-to-many relation maps many items of one list to many items of another list with the help of an intermediate table, however you did not implement that idea correctly.
Download the attached database and add 3 arbitrary picture files named IMG_0001.jpg, IMG_0002.jpg and IMG_0003.jpg to the same directory.
Open the relations window.
The primary key in both lists “Persons” and “Fotos” are auto-IDs. The primary key of the mapping table “P_F” is a compound key of the 2 foreign keys.
This way you can map as many fotos to persons as needed without adding new columns to any table (Nr6, Nr7 etc). And you won’t have any duplicates. The person names and the file names of the fotos have a unique index and the combined primary key in the mapping table does not allow duplicate mappings.
The resulting forms are almost identical. One maps persons to fotos, the other one maps fotos to persons. The Persons2Fotos form has an additional sub-subform to show the multi-line comment and the picture because the table control can not show pictures.
persons2pics.odb (22.8 KB)
Thanks for the suggestion, it certainly simplifies the management of my database
My problem remains unsolved however namely to display one record for every person in the Persons table.
You could imagine eg a sales Company wanting to find out the last purchase made by every one of their customers. What is wanted is one record per customer (in this case the last) in a restricted list.
Ik can’t figure out what the SQL statement to achieve this could look like ( i tried max, limit…to only get aggregate errors or erronous results)
Sorry to bodder You again
Query the latest foto by higest ID:
SELECT "Fotos".*
FROM "Fotos" JOIN "P_F" ON "Fotos"."ID" = "P_F"."FID" JOIN "Persons" ON "Persons"."ID" = "P_F"."PID"
WHERE "P_F"."ID"= (SELECT MAX("ID") FROM "P_F") AND "PID"= :pPID
:pPID is a named parameter specifying the person ID in question. Bind pPID as slave field of a subform linked to a person’s ID as master in the parent form.
- I found this workaround for my problem
- Find ONE record in TABLE1(Fotos) for EVERY record in TABLE2 (Persons) using an intermediate table (P_F)
CREATE TABLE “Temp” (“FID” Integer, “PID” Integer)
INSERT INTO “Temp”
(SELECT MAX( “P_F”.“FID” ) AS “Fotonr”, “P_F”.“PID” FROM “P_F” INNER JOIN “Fotos” ON “P_F”.“PID” = “Fotos”.“ID” GROUP BY “P_F”.“PID”)
– Creates a table holding the reference to one photo for every person in the “Persons” table
–Next I query the “Temp”.“Table”
SELECT * FROM “Temp”
Inner Join “Persons” ON “Temp”.“PID” = “Persons”.“ID”
INNER JOIN “Fotos” on “Temp”.“FID” = “Fotos”.“ID”
I’d suggest to sort the output ORDER BY and use LIMIT to get only one record
SELECT * FROM table ORDER BY column LIMIT 1
Ik already tried this, but logically it only displays the first record of te first person, not the first record for every person in the persons table
Thanks anyway