This SQL problem is the MAX() amount of frustration I can handle right now. (2 Viewers)

[KitaYama]

It might be worth repeating the tests with a larger dataset as that may affect the overall efficiency.
My very simple test database is attached

Million thanks for all your efforts.
I'm at home and don't have Access here. I'll check it on Monday.
I really appreciate your assist.

 

[DaveMD]

With this information that you are using SQL Server and you are looking to scale this (you note the table is expected to grow to half a million records), you have several good ways of tackling this:

1. As @DHookom notes, I also would highly suggest having this query be processed on SQL Server either as a pass-through query or using VBA to construct the query dynamically and using ADO to execute it on SQL Server. SQL Server will much better optimize performance particularly as your data size grows.

2. Use indexes on SQL Server to improve performance. If you are going to be frequently querying for particular PricesProductFK values, an index on that field could go a long way and you could also incorporate other fields into the index (as additional key fields) to optimize.

3. As @isladogs suggests, absolutely test with larger datasets. Query performance can vary significantly with larger datasets and what performs best on a small dataset can perform very differently as the dataset gets larger.

4. With SQL Server, you have many more options for how to write the query. For example, you could take a common SQL Server approach that uses the ROW_NUMBER window function to get the result you are looking for. See below for your query that uses that approach (this would need to run as a pass-through or direct query on SQL Server as this is not supported by the Access DB Engine).

WITH CTE AS (
    SELECT p.*,
           ROW_NUMBER() OVER (
               PARTITION BY p.PricesProductFK, p.PricesOrderedFromFK,
                            p.PricesQuantity, p.PricesRegisteredBy
               ORDER BY p.PricesRegisteredOn DESC, p.PricesUnitPrice DESC
           ) AS rownum
    FROM tblProductPrices AS p
)
SELECT
    PricesProductFK,
    PricesOrderedFromFK,
    PricesQuantity,
    PricesRegisteredOn AS RegisteredOn,
    PricesRegisteredBy,
    PricesUnitPrice
FROM CTE
WHERE rownum = 1 AND PricesProductFK = 34086;

 

[NauticalGent]

@zozew , I think you may have been hacked