Dlookup

look up against the MCode and Price to find the unique id number linked to that price

This could be avoided by an appropriate query, which determines those prices and join the result against your grouped result. But this depends on your price history table. E.g.

SELECT  DISTINCT O.ProductID ,        
        O.ListPrice
FROM    ProductListPriceHistory O
WHERE   O.ListPrice = ( SELECT  MIN(I.ListPrice)
                        FROM    Production.ProductListPriceHistory I
                        WHERE   I.ProductID = O.ProductID
                      );

Select allOpen in new window

How are you planning on displaying this data?

Depending on the number of records being returned by your query, the correlated subquery technique shown by ste5an can be slow (the database has to execute that subquery for every record).

I recently had a situation similar to this and wrote an article (Creating a Faster DLookup) about my solution.  If you are displaying this info in a form, you could create your own "faster Dlookup" function, and call that function from the ControlSource of a control on your form.

Hi Dale,

i'm not going to be displaying the data, i just need to export it to a csv file

i tried this but it's giving me an error:

Expr1: DLookUp("RateID","PFS_autoUpdate_Caspio_mainTable","MCode =" & [MCode] & " And SumofGBPTOTAL = " & [CheapestPrice])

With an appropriate index, it's acceptable.

Expr1: DLookUp("RateID","PFS_autoUpdate_Caspio_mainTable","MCode =" & chr$(34) & [MCode] & chr$(34) & " And SumofGBPTOTAL = " & [CheapestPrice])

But with that, this is a bad way to do it.   The Domain functions should not be used inside a query.  You need to re-think your query, which can simply be a join to another table/query to find the ID with that price.

After all, that's what a DLookup() is; a Select statement.

Jim.

You can still use the technique I described in my article.  I actually used it in a query but found that you could also use the function as the control source of a control on a form or report.

Based on your image, you may need to take that query, and make it a subquery, then do the DLookup (or faster DLookup) outside the subquery.  I'm not sure what price you are trying to compare against or the table, but the query would look something like:

SELECT T.MCode, T.Pod, T.CountOfMCode, T.CheapestPrice,
DLOOKUP("ID", "sometable", "[MCode] = '" & T.MCode & "' AND [Price] = " & T.CheapestPrice) as ProdID
FROM (
'insert your query here
) as T

@Ste5an,

"With an appropriate index, it's acceptable. "

More so in SQL Server than in Access.  I've found that even with the appropriate indices, when you have a lot of records in the result set, the correlated subquery can really slow the query down, very similar to the DLOOKUP.

Without knowing more about the data structure, another method would be to create a subquery, as in my previous response, and actually join that to the table where the OP is trying to get the data from, something like:

SELECT T.MCode, T.Pod, T.CountOfMCode, T.CheapestPrice, OT.ID as ProdID
FROM (
'insert query here
)
INNER JOIN otherTable as OT ON T.MCode = OT.MCode and T.CheapestPrice = OT.Price

This would be far faster than the correlated subquery, at least for large recordsets.

in the end i joined the 2 querys like this:

SELECT Count(PFS_autoUpdate_Caspio_mainTable.MCode) AS CountOfMCode1, PFS_autoUpdate_Caspio_mainTable.RateID, PFS_AutoUpdate_CaspioMainTable_ExportThis.CheapestPrice
FROM PFS_autoUpdate_Caspio_mainTable INNER JOIN PFS_AutoUpdate_CaspioMainTable_ExportThis ON (PFS_autoUpdate_Caspio_mainTable.MCode = PFS_AutoUpdate_CaspioMainTable_ExportThis.MCode) AND (PFS_autoUpdate_Caspio_mainTable.SumOfGBPTOTAL = PFS_AutoUpdate_CaspioMainTable_ExportThis.CheapestPrice)
GROUP BY PFS_autoUpdate_Caspio_mainTable.RateID, PFS_AutoUpdate_CaspioMainTable_ExportThis.CheapestPrice;