Dlookup

Benyaw616
Benyaw616 used Ask the Experts™
on
i have a query which is linked to another Query,

my 2nd query groups together records (typically 6 records) based on a text ref called: Mcode,

so i can do a count and group by to do this, i can even have my selling amount as min so it shows me the cheapest price but i then need to look up against the MCode and Price to find the unique id number linked to that price.

any suggestions?
EE-Query.jpg
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
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
                      );

Open in new window

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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.

Author

Commented:
Hi Dale,

i'm not going to be displaying the data, i just need to export it to a csv file
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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

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

Commented:
With an appropriate index, it's acceptable.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
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.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
@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.

Author

Commented:
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;
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Personally, I would have put all of the aggregation in a subquery (or a separate saved query), and then joined it to the other table.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial