The Data of tblStdBagConvRates is as below. The values 0.001 to 0.015 represent the ValueAdd for the bag sewing operation and this value depends on width of the bag (BagWidthMin and BagWidthMax) and the type of sewing BottomSewingStd or BottomSewingEZOpen
In the tblItemMasterPricingSpecs, the typical data related to this is as below. This table is existing and used for many other queries and changing the structure is not feasible
SewingTypebottom – 1 to 54 is BottomSewingStd and 55 to 81 is BottomSewingEZOpen
Question: How do I join the tblItemMasterPricingSpecs with tblStdBagConvRates in the query to get the ValueAdd?
I could do a Dlookup, as below, but this generally slows down queries and the actual query will multiple such lookups for different bag operations
ValueAdd: IIF([Sewingtypebottom] between 37 to 54, Dlookup(“BottomSewingStd”, “tblStdBagconvrates”, “’&[WidthBagorFabric]&’ between BagWidthMin and BagwidthMax”),
IIF(([Sewingtypebottom] between 55 to 81, Dlookup(“BottomSewingEZopen”, “tblStdBagconvrates”, “’&[WidthBagorFabric]&’ between BagWidthMin and BagwidthMax”),0))
BagConvID | BagWidthMin | BagWidthMax | BottomSewingStd | BottomSewingEZOpen |
3 | 10 | 24 | 0.001 | 0.015 |
4 | 24.01 | 32 | 0.0015 | 0.015 |
5 | 32.01 | 40 | 0.03 | 0.015 |
In the tblItemMasterPricingSpecs, the typical data related to this is as below. This table is existing and used for many other queries and changing the structure is not feasible
Group | FGCode | WidthBagOrFabric | SewingTypeBottom |
Bag | 10001 | 14 | 38 |
SewingTypebottom – 1 to 54 is BottomSewingStd and 55 to 81 is BottomSewingEZOpen
Question: How do I join the tblItemMasterPricingSpecs with tblStdBagConvRates in the query to get the ValueAdd?
I could do a Dlookup, as below, but this generally slows down queries and the actual query will multiple such lookups for different bag operations
ValueAdd: IIF([Sewingtypebottom] between 37 to 54, Dlookup(“BottomSewingStd”, “tblStdBagconvrates”, “’&[WidthBagorFabric]&’ between BagWidthMin and BagwidthMax”),
IIF(([Sewingtypebottom] between 55 to 81, Dlookup(“BottomSewingEZopen”, “tblStdBagconvrates”, “’&[WidthBagorFabric]&’ between BagWidthMin and BagwidthMax”),0))