complex dlookup?

[mbar]

Hi-

I have products listed below:

item    shipdim1    shipdim2    shipdim3
ITEM10  28  28  11
ITEM11  13  11  2
ITEM12  32  21  18
ITEM13  30  96  57
ITEM14  30  72  57
ITEM15  30  60  57
ITEM16  28  28  11
ITEM17  44  15  12
ITEM18  25  8   7
ITEM19  37  8   7
ITEM20  25  8   7
ITEM21  47  32  23
ITEM22  41  37  36
ITEM23  48  37  23
ITEM24  30  28  5
ITEM25  30  88  88
ITEM26  49  21  21
ITEM27  49  21  21
ITEM28  32  21  18
ITEM29  58  15  12
ITEM30  28  19  14
ITEM31  28  19  14
ITEM32  48  21  21
ITEM33  32  21  18
ITEM34  28  19  14
ITEM35  42  32  25
ITEM36  35  33  38
ITEM37  25  8   7
ITEM38  37  8   7
ITEM39  48  8   7
ITEM40  72  8   7

I also have multiple boxes that these products need to fit into:

BOXID   dim1    dim2    dim3
BOX1    16  13  3
BOX2    16  15  12
BOX3    25  8   7
BOX4    25  25  18
BOX5    28  13  12
BOX6    28  19  14
BOX7    28  28  11
BOX8    30  15  12
BOX9    30  28  5
BOX10   32  21  18
BOX11   37  8   7
BOX12   42  32  25
BOX13   44  15  12
BOX14   47  32  23
BOX15   48  36  18
BOX16   56  25  18
BOX17   58  15  12

I need create a function that uses the smallest box possible, where shipdim1 doesn not exceed dim1, shipdim2 does not exceed dim2, ect.. Ultimately, I need to find the [BOXID] value that is the best match.

For example, the StBestBox variable for PROD10 would be BOXID= BOX7

I'm assuming this can be achieved using Dlookup and Dmin?

Please help!!
​

 

[spikepl]

Sudoku is more fun than this.

Edit your post. Choose Advanced, select your data, press # (that wraps your data in code tags) and then format it into something readable

 

[spikepl]

You need to define "smallest", otherwise multiple solutions can presumably turn up.

 

[mbar]

LOL- post has been fixed. much easier to understand.

spikepl- Maybe something like DMin("dim1", TblBoxes, "dim1 >= " Me!shipdim1) however I need the code to incorporate all 3 box dims.....

 

[spikepl]

What I meant is that you first and foremost need to define exactly what determines "smallest fit", and worry about the implementation afterwards.

A given product could be stuffed into the same box in various ways (or not?) - some of them may fit and some not.

When the product fits into more than one box, how do you wish to decide which one? Maybe one box is too long by some amount, whereas another box is too high by the same amount, so which one?

Would "smallest" be smallest volume? And if the product fits into two different boxes with same volume? (I haven't checked whether there indeed are such boxes right now, but things can change with time)

Algorithms are like small stupid children - you have to tell them exactly what to do for each occasion, or else they are at a loss.

 

[mbar]

"smallest fit" for this purpose is the dimensions. I've arranged the product dims and the box dims so that the greatest dimesion is dim1 and the smallest dimension is dim3. Therefore, I need to somehow execute the following:

find the boxid's where dim1 > shipdim1.
then, from this list, find the boxids where dim2 > shipdim2
then from this list, find the boxids where dim3 > shipdim3

if more than 1 match exists, then I'll use a standard UPS price to an average zip code to determine what is the least expensive. This pricing accounts for any surcharges relative to a box dimension that is too long, ect..

if null, then msbox "no box can be used for this product"

 

[spikepl]

Try this. Make a query in the query designer with all three criteria and sorted by dim1, dim2, dim3 ascending , so that it gives you a list of all boxes an item fits into, with the smallest dim1 first, then smallest dim2 etc. If there are any records then you have found some suitable boxes. This is not perfect but may work.

The query could also have a calculated field, showing the cost based on dimensions. And perhaps should be sorted by that field (ascending) so you directly have the cheapest at the top of your list.

 

[mbar]

I've already done this- the user selects the appropriate boxid from a combo box.

I have 7000 items and the turnover is great. Box sizes also change periodically. I need some sort of code or sets of queries where the boxid is set for each item

 

[Mile-O]

Shipdim1, Shipdim2, Shipdim3

This sort of thing usually indicates there's a structural issue with your table design. See: Normalisation.

 

[spikepl]

So what prevents you from using that query (using TOP 1 in the select) to grab the top BoxId and assigning it to the Item in your table of items? Meaning, making an update query that uses this query as input for updating all items' boxid until next time you run the query?

Mile-O: normalisation is good, but also has to make sense. In this instance, it doesn't

 

[mbar]

My lack of knowledge!
That's what I attempted with no luck. I tried something like this:
boxesthatwork: DLookUp("cardbid","cardbrd","[dim1] > [shipdim1]")
query can't find [shipdim1]. The 2 table cannot be linked and I don't know how to reference the Product table where [shipdim1] resides. I'm not even sure if you can or if this is the best method to accomplish my problem...

 

[spikepl]

Using Dlookup is performance-wise a very costly way of achieving the same thing as I suggested, and with 7000 items can take a while.

Do it in steps.

First create a SELECT query that finds the appropriate BoxID for a specific Item as per #7. If you don't know how to do that then do a tutorial on queries.

Then make a select query that finds the boxid for each item- this can get a little more complex.

Once you have that, you can work on an Update query.

You can get some directions from me if you make an effort, or someone else here may even do this for you, if you wait

 

[mbar]

Maybe something like this? I don't know the correct syntax however...

Dim LFindBox As Long
Dim LtMinbox As Long
Dim StBox As sting
 
LFindBox = DLookup("pricetoship", "cardbrd", "[dim1] > [shipdim1" And "[dim2] > [shipdim2]" And "[dim3] > [shipdim3]")
 
LMinbox = DMin(StFindBox)
 
StBox = DLookup("boxid", "cardbrd", "[pricetoship] = " & LMinbox)
 
Me.Boxid.Value = StBox
End Sub

Then, I need to loop through all records. anyone know the proper code?

 

[spikepl]

No looping in code.
QUERIES in the query designer. That will be faster, and does not require code. See #12

 

[mbar]

"as per #7" "See #12" . what are you reffering to?