Avatar of Benyaw616
Benyaw616
 asked on

Dlookup

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
DatabasesMicrosoft AccessSQL

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
ste5an

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 Fye

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.
Benyaw616

ASKER
Hi Dale,

i'm not going to be displaying the data, i just need to export it to a csv file
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Benyaw616

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

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

With an appropriate index, it's acceptable.
Jim Dettman (EE MVE)

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Dale Fye

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 Fye

@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.
Benyaw616

ASKER
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;
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
Dale Fye

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question