I am trying to build a SQL Query that takes the following data:
+-------------+--------+---------+---------+--------+
| Primary Key | ID | Version | Class | Fruit? |
+-------------+--------+---------+---------+--------+
| 1 | Banana | 1 | NORTH | Yes |
| 2 | Onion | 1 | WEST | No |
| 3 | Orange | 1 | NA | Yes |
| 4 | Orange | 2 | PACIFIC | Yes |
| 5 | Banana | 2 | EUR | Yes |
| 6 | Celery | 1 | EUR | No |
| 7 | Celery | 3 | SOUTH | No |
| 8 | Celery | 4 | SOUTH | No |
| 9 | Pepper | 1 | N-PAC | No |
| 10 | Pepper | 2 | N-PAX | No |
+-------------+--------+---------+---------+--------+
And returns ID of latest version and its corresponding data, where the criteria of Fruit
is examined.
An SQL string would be needed to return ID with class for Max version where Fruit = No
Results:
+--------+-------+
| ID | Class |
+--------+-------+
| Onion | NORTH |
| Celery | SOUTH |
| Pepper | N-PAX |
+--------+-------+
I only need to return the ID and it's class to store in an MS Access listbox.
I managed to build a Group By / Max query in the editor and was only able to get IDs to return grouped, but the corresponding data was not associated with the maximum version.
Thanks for your help and expertise.