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
Benyaw616Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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

0
Dale FyeCommented:
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.
0
Benyaw616Author Commented:
Hi Dale,

i'm not going to be displaying the data, i just need to export it to a csv file
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Expr1: DLookUp("RateID","PFS_autoUpdate_Caspio_mainTable","MCode =" & [MCode] & " And SumofGBPTOTAL = " & [CheapestPrice])
0
ste5anSenior DeveloperCommented:
With an appropriate index, it's acceptable.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0
Dale FyeCommented:
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
1
Dale FyeCommented:
@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.
1
Benyaw616Author 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;
0
Dale FyeCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.