I am trying to create a basis for a list of products in an MS Access Query. The basis must later be used in a form where the products are visualized with only one of the many images associated with each product.
The image name is calculated with an expression field based on the product ID and color ID, which is also the composite primary key for the table.
The table name: Images
Field Name Datatype
ProductID Number
ColorID Number
ImageName Expression [ProductID] & "-" & [ColorID] & ".jpg"
A small output from the table with more than 62K records:
| ProductID | ColorID | ImageName |
|---|---|---|
| 100000 | 6 | 100000-6.jpg |
| 100000 | 11 | 100000-11.jpg |
| 100000 | 1546 | 100000-1546.jpg |
| 100001 | 3 | 100001-3.jpg |
| 100001 | 11 | 100001-11.jpg |
| 100001 | 18 | 100001-18.jpg |
| 100001 | 23 | 100001-23.jpg |
| 100001 | 45 | 100001-45.jpg |
| 100001 | 230 | 100001-230.jpg |
| 100002 | 3 | 100002-3.jpg |
| 100002 | 6 | 100002-6.jpg |
| 100004 | 11 | 100004-11.jpg |
| 100004 | 26 | 100004-26.jpg |
| 100004 | 285 | 100004-285.jpg |
| 100006 | 3 | 100006-3.jpg |
| 100006 | 6 | 100006-6.jpg |
| 100006 | 11 | 100006-11.jpg |
| 100006 | 106 | 100006-106.jpg |
| 100007 | 3 | 100007-3.jpg |
| 100007 | 6 | 100007-6.jpg |
I have tried using Distinct, and Group By with a MIN Min function with no success
SELECT ProductID, ColorID, ImageName
FROM Images
GROUP BY ProductID, ColorID, ImageName
HAVING (((ColorID)=(SELECT MIN(ColorID) FROM Images)));
What I expect from the query result:
| ProductID | ColorID | ImageName |
|---|---|---|
| 100000 | 6 | 100000-6.jpg |
| 100001 | 3 | 100001-3.jpg |
| 100002 | 3 | 100002-3.jpg |
| 100004 | 11 | 100004-11.jpg |
| 100006 | 3 | 100006-3.jpg |
| 100007 | 3 | 100007-3.jpg |