DLookUp function.... (1 Viewer)

MannyST

Member
Local time
Today, 01:49
Joined
May 23, 2020
Messages
42
So i'm trying to come up with the best method to create a "Size" table for rugs.....there are only 7 options that any particular sized rug must be categorized in and the value of one of the 7 options needs to be returned. What would be the best way to create this lookup table with so many different variations on length x width of the actual size of the rugs???

2x3 2x4 2x8 5x7 8x10 10x13 12x15 these are the only options.....I have a size field format is that has all the sizes of the rugs in the following format WxL
2'6" x 10'0", 8'0" x 10'0", 7'6" x 10'0", 2'6" x 12'0", 2'6" x 3'10", etc Also have a Width field that lists the following format 5' 9", 11' 5", 1' 6", etc and there is also a Length field.

What would the best approach to building a table with so many variations, but only 7 options to categorize in?
 

Minty

AWF VIP
Local time
Today, 06:49
Joined
Jul 26, 2013
Messages
7,530
Simply record all the possible variations as the following fields RugWidth, RugHeight, RugLength, SizeCategory.

Access won't complain if it's 5 rows or 500.

You can then easily build forms to select the size you want using combo's, and always return the correct size category.
If you need to add a size it's just another row and or category.
 

MannyST

Member
Local time
Today, 01:49
Joined
May 23, 2020
Messages
42
That was my original path. I won't be selecting sizes as I will be importing files with predetermined data that I have to the format for my use. I was hoping something a little more automatic such as a table with ranges and a dlookup function to search and retrieve??
 

Minty

AWF VIP
Local time
Today, 06:49
Joined
Jul 26, 2013
Messages
7,530
The problem with that is that you need two lookups - one for height & one for width (Having thought about this it must only be length and width? forget height)
If you create a table with all possibilities in it you can simply join to it.

A table with boundary values (upper and lower values) would also work but you need to filter by both values to get your result so your boundary table would be almost as complicated.
 

MannyST

Member
Local time
Today, 01:49
Joined
May 23, 2020
Messages
42
The problem with that is that you need two lookups - one for height & one for width (Having thought about this it must only be length and width? forget height)
If you create a table with all possibilities in it you can simply join to it.

A table with boundary values (upper and lower values) would also work but you need to filter by both values to get your result so your boundary table would be almost as complicated.
That's why I was hoping s
Someone could come up with something.....
 

Minty

AWF VIP
Local time
Today, 06:49
Joined
Jul 26, 2013
Messages
7,530
No simple way around it, You can create the lookup values easily by having two simple tables (height and width) with the individual values.
Drag them both into a new blank query, select the single field from both as outputs, don't make any join!

Run the query - you'll get all the possible outcomes - this is known as a cartesian join.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:49
Joined
Feb 19, 2002
Messages
30,132
You can categorize the odd sizes, you just need rules. For example, which of the 7 categories would 2'6" x 10'0" fall into? How did you decide which category to choose? If you can tell us in words, we can help you with VBA logic.

You're missing a size that would correspond to a long runner.
 

Users who are viewing this thread

Top Bottom