Solved

DLookUp in Queries

Posted on 2013-06-24
28
369 Views
Last Modified: 2013-07-04
I need a combo box to compare the current forms control values with values that are available from a standards table.

Pricing picture Frames. A manufacturer wants an easy way to consistently price custom size frames. He has prices in a table (Standards) based on height and width of frame which he buys from different suppliers. In the combobox's query how do I find the nearest size specified on the main form to that available from stock, only showing the closest size large enough from each supplier.

So I want to price a frame that is 95mm by 155mm from my Standars table which has:

Supplier Smith, nearest size 100 x 160
Supplier Jones, nearest size 95 x 170

Both Smith and Jones have multiple sizes available in my Standards Table.

What I can do is use a DLookUp for the sizes but I am told that you should not use DLookUp in queries?
0
Comment
Question by:DatabaseDek
  • 16
  • 12
28 Comments
 
LVL 57
ID: 39270911
<<What I can do is use a DLookUp for the sizes but I am told that you should not use DLookUp in queries? >>

  That is correct; none of the Domain functions should be used in a query.

  All the domain functions represent SQL Statements, which you can write directly in the query designer, so there's no reason to use them.

  The domain functions are meant to be used in places where SQL statements are not, such as a control source.

 Because the domain functions encapsulate SQL statements, when you use them in a query, the query parser cannot see them, and your ensuring poor performance as a result.

 The trick in this case is to base the combo's rowsource on a query, which filters based on a variable or a control on the current form.

 Say you had a size control on the form.   The query that forms the rowsource for the combo might have this check:

>=FOrms![<myFormName>]![<mySizeControlName>]

  Then in the form, in the size controls after update event, you'd do:

 Me.<myComboControlName>.Requery


 The combo would then present a list of only the sizes that would work.

Jim.
0
 

Author Comment

by:DatabaseDek
ID: 39271004
Thank you.

I have that. The problem is that I don't want to see all the sizes that are irrelevant i.e everything larger than Form control Height and Form control Width. The list goes up to both Smith and Jones 1000mm x 1000mm So then at the bottom of what could be a very long list is a different supplier i.e. Jones 95mm x 170mm. So what I am trying to get is

:The first record from each supplier that is >=FOrms![<myFormName>]![<Height>] And >=FOrms![<myFormName>]![<Width>]

Not an easy one. I tried First in the query grid but it seams to ignore it
0
 
LVL 57
ID: 39271047
<<
 >=FOrms![<myFormName>]![<Height>] And >=FOrms![<myFormName>]![<Width>]
>>

 Construct a serperate query that returns all the records with the above check.

 Now change it to group by (totals query - the "E" button on the toolbar).   Drag down the vendor into the first column and the size.  Set these to Group by.

  If that doesn't do it, you'll need to give me a little more detail on the actual data and what the end result should look like.

Jim.
0
 

Author Comment

by:DatabaseDek
ID: 39271260
Hi Jim

I can do that, the tricky bit is only getting the first record from each supplier that is the problem. If that is not easily understood I will have to post a sample. But that's about an hours work. It's a very involved DB.

Does DLookUp only effect performance or is it more serious?

Thanks Jim

Derek
0
 
LVL 57
ID: 39271343
<<I can do that, the tricky bit is only getting the first record from each supplier that is the problem.>>

 Change the total line to read "MIN" under the size and leave the GroupBy on the vendor.

 If that doesn't do it:

1. Create an empty DB.
2. Import the size table that your trying to display in the combo.
3. Chop some of the records out if it's a large table.
4. Compact and repair if you chopped records out.
5. post the DB.

  all I need is a sample of the data.

Jim.
0
 

Author Comment

by:DatabaseDek
ID: 39272157
Hi Jim

The attached db1 is the data. I use the picture frames as a description of the problem. In fact it is for office door manufacturers. So if, on my main form, I have a pair of doors specified then in Height they are the same 2190mm high, in width the left door is 890 wide and the right door is 590 wide so in my combo box i should see from the data records A B C and D. which I added Under ProductID in the attached table. So you can see that Maverik make doors very close to the size but In-House only have records C and D which are their closest match.

The Door manufacturer then buys the cheapest size and cuts the doors down to the required size. The reason we need both suppliers is that sometimes a larger door from another supplier is larger but cheaper than smaller doors from another manufacturers.
db1.mdb
0
 

Author Comment

by:DatabaseDek
ID: 39272343
Hi Jim

I forgot, when I used DLookUp I had to get two lots of records one for the left door width and one for the right door width from the table using two slightly different queries and joined them with a UNION ALL query so both door sizes appeared in the same combobox data
0
 
LVL 57
ID: 39272363
<<I forgot, when I used DLookUp I had to get two lots of records one for the left door width and one for the right door width from the table using two slightly different queries and joined them with a UNION ALL query so both door sizes appeared in the same combobox data >>

 Create the UNION, save it.   Then for the totals query, use the union query as an input table.

 You can build queries on top of queries.

Jim.
0
 

Author Comment

by:DatabaseDek
ID: 39272562
The problem is that my queries still use the DLookUp

Because that is what I do to get what I need. So is it wrong to use DLookUp or just not efficient
0
 
LVL 57
ID: 39272735
Based on your table, here's the query:

SELECT CoreMeterials.Supplier, First(CoreMeterials.Make) AS FirstOfMake, First(CoreMeterials.Finish) AS FirstOfFinish, First(CoreMeterials.FireRating) AS FirstOfFireRating, First(CoreMeterials.CoreMeterial) AS FirstOfCoreMeterial, First(CoreMeterials.Height) AS FirstOfHeight, First(CoreMeterials.Width) AS FirstOfWidth, First(CoreMeterials.DCost) AS FirstOfDCost, First(CoreMeterials.Thickness) AS FirstOfThickness, First(CoreMeterials.Margin) AS FirstOfMargin
FROM CoreMeterials
WHERE (((CoreMeterials.Height)>=[Door Height]) AND ((CoreMeterials.Width)>=[Door Width]))
GROUP BY CoreMeterials.Supplier;


1. Cut and paste this into the SQL view of a query.
2. Switch to design view
3. Test the query and make sure it gives you the results you need.  When I tested it, I got the first door that would fit for each vendor.
4. Take out the fields you don't need for the combo.
5. Replace [EDoor Height] and [Door Width] with your form references.
6. Save the query.
7. Open the query and save as, but now set it up for the right door.
8. Open a new blank query.
9. Open the query for the left door, switch to SQL view and cut & paste to the new query.  
10. Add UNION to the end.
11. Now open the right door query and do the same thing you did in step 9 for the left door.
12.  Save and test.

Once it's working, you can delete the first two queries.

<<Because that is what I do to get what I need. So is it wrong to use DLookUp or just not efficient >>

 It's wrong in that it's very inefficient. a DLookup() is nothing more then a select <some field> from <some table> where <condition>, but because it's wrapped up in a function call, the query parser can't do anything with it in terms of making the query fast.

Jim.
0
 
LVL 57
ID: 39272744
hang on a second, let me double check that....

Jim.
0
 
LVL 57
ID: 39274352
Well this turned out to be interesting.   With the data you gave me, besides the height and the width, is there anything else that determines what the first choice should be for a given supplier?

In the data you provided, there are several cases where there is a door with the same height and width, so which should be picked?

Jim.
0
 
LVL 57
ID: 39274356
Never mind, I just started re-reading back through the thread and found this:

"The Door manufacturer then buys the cheapest size and cuts the doors down to the required size. The reason we need both suppliers is that sometimes a larger door from another supplier is larger but cheaper than smaller doors from another manufacturers. "

Jim.
0
 
LVL 57
ID: 39274367
I'm looking that this data and shouldn't your user be able to pick the best quality door for a given size (ie. higher fire rating)?   If so, given you data, there may be more then one door from a mfg.

I'm looking at In-house 2300 x 500; both are the same cost, but one carriers a fire rating of FD30 where the other is "None".  

What would the user see in this case?

 also, you said a larger door might be cheaper; is there ever a case where a larger door from the same Mfg might be cheaper because of material used or other factors, so you would need to present more then one door choice too them?

Jim.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:DatabaseDek
ID: 39275345
That's very insightful Jim

Fire rating comes from the form and is the first and most important spec It is always specified by the customer and FD60 doors are always significantly dearer.
Different quality doors would come under a different Material or Type. But usually they are all the same.

Thanks Jim I'm rushing to finish something else for an appointment all day tomorrow. I will work on you suggestions above on Thursday.

Thanks again

Derek
0
 
LVL 57
ID: 39275374
Derek,

  Touch base with me before you do....what I've posted so far doesn't cut it, but I don't think I can go any further before we nail down exactly what it is you need for the user to see.

Jim.
0
 

Author Comment

by:DatabaseDek
ID: 39281664
Hi Jim

The attached is what I currently need and get by using DLookUp in the CoreMaterials table.
The Door selector field would be blank before selection from the combo.


As I said earlier I do not think we need to worry about Fire Rating  or different quality doors because they would always be distinct by door Type.
Capture.JPG
CaptureB.JPG
0
 
LVL 57
ID: 39281997
In the data you provided, there is no "Door Type" field in the CoreMaterials table.

Jim.
0
 

Author Comment

by:DatabaseDek
ID: 39283677
Hi Jim

Sorry don't worry about door type. I forgot I renamed it to Supplier.

But the main point is that we do not consider different qualities and the effect on price.

Cheers Derek
0
 
LVL 57
ID: 39284786
Derek,

 Sorry this is dragging out, but what I'm struggling with is based on what you said above (that a larger door may be cheaper and be cut down), it seems that you either need to:

a. present the user with more then one choice for each mfg

or

b. filter by more criteria when trying to come up with the list of suppliers so that only one door will fit the requirements.

Now that I've said that, let me ask something: would there ever be for a given supplier a door that would be larger then another door that would be cheaper in price?

 In other words, as the size increases, will the price *always* go up for a given supplier?

Jim.
0
 

Author Comment

by:DatabaseDek
ID: 39284829
Hi Jim
We need to present the user with more then one choice for each mfg.

Prices always increase in line with surface area.

But different manufacturers stock different sizes and this causes a situation where it can be advantageous to buy a larger door from a different manufacturer because it is a lower price.. So what I am trying to find in the query and show in the combo is which is the nearest size to the custom door size that each supplier offers. And that would look like Capture.jpg "What I need to see" above.

Derek
0
 
LVL 57
ID: 39285241
OK, give this a go:

SELECT T1.Supplier, T1.[Height]*T1.[Width] AS Surface, T1.Height, T1.Width, T1.Make, T1.Finish, T1.FireRating, T1.CoreMeterial, T1.DCost, T1.Thickness, T1.Margin, T1.ProductID
FROM CoreMeterials AS T1
WHERE (((T1.[Height]*T1.[Width]) In (Select Min([Height]*[Width]) FROM [CoreMeterials]  Where [Supplier] = T1.[Supplier] and [Height]>=[Enter Door Height] and [Width] >=[Enter Door Width])) AND ((T1.Height)>=[Enter Door Height]) AND ((T1.Width)>=[Enter Door Width]))
ORDER BY T1.Supplier, [Height]*[Width], T1.Height, T1.Width;


This will return the smallest possible door that can be used for each supplier based on the height and width.

If no door is sufficently large enough, you will not see the supplier listed.

If more then one door would work, you will see both doors listed.

Jim.
0
 

Author Comment

by:DatabaseDek
ID: 39288370
Wow! That's something else. Never seen anything like it.

Perhaps I should have explained that there may be up to 5000 varieties of door and supplier. (Bit late for that I guess) With 3000 that I have in my table it takes 20 seconds to run. I have filtered most of the fields so it still works OK. But without filtering am I correct in assuming that the query is checking 3000 Records to get the correct Height by 3000 Records to get Width by 3000 to get Make etc?

I have used the code to get the same result for the Right door and used a union all to join both results: So this is what I have:

SELECT T1.Supplier, T1.[Height]*T1.[Width] AS Surface, T1.Height, T1.Width, T1.Make, T1.Finish, T1.FireRating, T1.CoreMeterial, T1.DCost, T1.Thickness, T1.Margin, T1.ProductID
FROM CoreMeterials AS T1
WHERE (((T1.Height)>=[Forms]![ProjectForm]![Details].[Form]![DH]) AND ((T1.Width)>=([Forms]![ProjectForm]![Details].[Form]![LDW]+[Forms]![ProjectForm]![Details].[Form]![DRV]) And (T1.Width)>0) AND ((T1.Make) Like nz([Forms]![ProjectForm]![Details].[Form]![Finish],"*")) AND ((T1.Finish) Like nz([Forms]![ProjectForm]![Details].[Form]![Veneer],"*")) AND ((T1.FireRating)=Left([Forms]![ProjectForm]![Details].[Form]![FireRating],4)) AND ((T1.Thickness) Like nz([Forms]![ProjectForm]![Details].[Form]![Thickness],"*")) AND (([T1].[Height]*[T1].[Width]) In (Select Min([Height]*[Width]) FROM [CoreMeterials]  Where [Supplier] = T1.[Supplier] and [Height]>=[Forms]![ProjectForm]![Details].[Form]![DH] and [Width] >=([Forms]![ProjectForm]![Details].[Form]![LDW]+[Forms]![ProjectForm]![Details].[Form]![DRV]))))
ORDER BY T1.Supplier, [Height]*[Width], T1.Height, T1.Width;

UNION All
SELECT T1.Supplier, T1.[Height]*T1.[Width] AS Surface, T1.Height, T1.Width, T1.Make, T1.Finish, T1.FireRating, T1.CoreMeterial, T1.DCost, T1.Thickness, T1.Margin, T1.ProductID
FROM CoreMeterials AS T1
WHERE (((T1.Height)>=[Forms]![ProjectForm]![Details].[Form]![DH]) AND ((T1.Width)>=([Forms]![ProjectForm]![Details].[Form]![RDW]+[Forms]![ProjectForm]![Details].[Form]![DRV]) And (T1.Width)>0) AND ((T1.Make) Like nz([Forms]![ProjectForm]![Details].[Form]![Finish],"*")) AND ((T1.Finish) Like nz([Forms]![ProjectForm]![Details].[Form]![Veneer],"*")) AND ((T1.FireRating)=Left([Forms]![ProjectForm]![Details].[Form]![FireRating],4)) AND ((T1.Thickness) Like nz([Forms]![ProjectForm]![Details].[Form]![Thickness],"*")) AND (([T1].[Height]*[T1].[Width]) In (Select Min([Height]*[Width]) FROM [CoreMeterials]  Where [Supplier] = T1.[Supplier] and [Height]>=[Forms]![ProjectForm]![Details].[Form]![DH] and [Width] >=([Forms]![ProjectForm]![Details].[Form]![RDW]+[Forms]![ProjectForm]![Details].[Form]![DRV]))));

Would it be better to pre-filter the records in say "CoreMaterialsFilterdQuery" and then swap CoreMaterial table for the query?

Great work Derek
0
 
LVL 57
ID: 39289968
<<Wow! That's something else. Never seen anything like it.>>

 It's called a sub-select; basically a query within a query.  That's also what your doing with a domain function like DLookup().  For every row, your executing another query.   Sounds slow right?  As you have found out, it is.

 However with the problem you posed (finding the nearest door size, which can be cut down from a larger door size), this is the only way to solve that (in fact I doubt you'd have gotten the correct results with a simple Dlookup(), as the problem requires ranking the doors from a mfg in order to get the correct "one" (or more) that will do the job).

<< I have filtered most of the fields so it still works OK. But without filtering am I correct in assuming that the query is checking 3000 Records to get the correct Height by 3000 Records to get Width by 3000 to get Make etc?>>

 You are correct.

<<Perhaps I should have explained that there may be up to 5000 varieties of door and supplier. (Bit late for that I guess) With 3000 that I have in my table it takes 20 seconds to run.>>

  That would have changed things yes; I would have been more apt to use a temp table, fill it with data, and then run the query off that.

 Probably even better yet would have been to write this as a function to do the ranking.

<<I have used the code to get the same result for the Right door and used a union all to join both results: So this is what I have:>>

  Your not going to get the correct list returned because you've added criteria for fire rating, finish, and veneer, but do not have those in the subselect (IN(.....), so the selection process of what is the right door will be wrong.

  It is possible with proper indexing that you can speed this up enough, but sub-selects are always slow.

  I would first try the temp table approach:

1. Delete all records in it.
2. Append records to it based on *all* the criteria you currently have now.
3. Run the following query on the temp table:

SELECT T1.Supplier, T1.[Height]*T1.[Width] AS Surface, T1.Height, T1.Width, T1.Make, T1.Finish, T1.FireRating, T1.CoreMeterial, T1.DCost, T1.Thickness, T1.Margin, T1.ProductID
FROM tmpCoreMeterials AS T1
WHERE (((T1.[Height]*T1.[Width]) In (Select Min([Height]*[Width]) FROM [tmpCoreMeterials]  Where [Supplier] = T1.[Supplier]))
ORDER BY T1.Supplier, [Height]*[Width], T1.Height, T1.Width


  You no longer need filter by anything because it's already been pre-filtered.

If that doesn't get you the speed you want, then the other approach is to not use a IN() subselect, but open a record set on the base query in code and as you read forward through the records, grab the first record for each supplier.

 You can then either fill the selection list with a callback function, or write the results to a temp table and base the control off that.

 Not sure which will be faster, but before you do anything, check your indexing and make sure you have an index on any field you have a criteria on.  Also that Coremeterials has at least one unique index.

Jim.
0
 

Author Comment

by:DatabaseDek
ID: 39290729
"Your not going to get the correct list returned because you've added criteria for fire rating, finish, and veneer, but do not have those in the subselect (IN(.....), so the selection process of what is the right door will be wrong".

When you say the "Right Door" do you mean correct door or Right as opposed to Left? Because all other criteria will be the same for the "Right Door"

Actually the DLookup works much quicker but you are correct I cannot get it to show exactly what I need. Yours works perfect so my contacts will need patients. I guess this kind of query would work much slower with data on a server?
0
 
LVL 57
ID: 39290821
<<When you say the "Right Door" do you mean correct door or Right as opposed to Left? Because all other criteria will be the same for the "Right Door">>

  Not as in the left/right doors, but that the correct doors are displayed in the list.

  The IN() sub-select is trying to return a door the smallest possible door size that will work, but that door may not meet your criteria.   All it is looking at right now is the size.

    But you are filtering the main list on that criteria, so it's possible that doors that should be shown are not.

    If the Mfg had every possible combination of fire rating, veneer, finish, etc for each door size, then you'd be OK.  Otherwise you are not.

<<Actally the DLookup works much quicker but you are correct I cannot get it to show exactly what I need. Yours works perfect so my contacts will need patients>>

  Right, it can't because of the requirement that a larger door, which can be cut down might work and still be cheaper then another supplier's.  A Dlookup() is only going to return the first value it hits.

<<I guess this kind of query would work much slower with data on a server? >>

  Yes. If you stick with the above, I'd have an abundence of indexes.  Make sure every field you have criteria on has an index unless it's a yes/no field.

Jim.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 39290825
BTW, I think you really need to consider the temp table idea.  Should not take too long to test out.

Just do a make table on your current Core Materials table, then setup the process for deleting, appending new records, and then running the query against the temp table.

Jim.
0
 

Author Closing Comment

by:DatabaseDek
ID: 39300564
I will try that, thanks for your help, again.

Derek
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now