Solved Joining tables based on a range - avoid Dlookup in Queries! (1 Viewer)

AnilBagga

Member
Local time
Today, 18:43
Joined
Apr 9, 2020
Messages
223
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


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
Bag10001
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))
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:13
Joined
May 7, 2009
Messages
19,169
Code:
SELECT
    tblItemMasterPricingSpecs.Group,
    tblItemMasterPricingSpecs.FGCode,
    tblItemMasterPricingSpecs.WidthBagOrFabric,
    tblItemMasterPricingSpecs.SewingTypeBottom,
    (SELECT TOP 1 BottomSewingStd FROM tblStdBagConvRates A
        WHERE tblItemMasterPricingSpecs.WidthBagOrFabric
        BETWEEN A.BagWidthMin AND A.BagWidthMax) AS BottomSewingStd,
    (SELECT TOP 1 BottomSewingEZOpen FROM tblStdBagConvRates A
        WHERE tblItemMasterPricingSpecs.WidthBagOrFabric
        BETWEEN A.BagWidthMin AND A.BagWidthMax) AS BottomSewingEZOpen 
FROM tblItemMasterPricingSpecs;
 

AnilBagga

Member
Local time
Today, 18:43
Joined
Apr 9, 2020
Messages
223
Code:
SELECT
    tblItemMasterPricingSpecs.Group,
    tblItemMasterPricingSpecs.FGCode,
    tblItemMasterPricingSpecs.WidthBagOrFabric,
    tblItemMasterPricingSpecs.SewingTypeBottom,
    (SELECT TOP 1 BottomSewingStd FROM tblStdBagConvRates A
        WHERE tblItemMasterPricingSpecs.WidthBagOrFabric
        BETWEEN A.BagWidthMin AND A.BagWidthMax) AS BottomSewingStd,
    (SELECT TOP 1 BottomSewingEZOpen FROM tblStdBagConvRates A
        WHERE tblItemMasterPricingSpecs.WidthBagOrFabric
        BETWEEN A.BagWidthMin AND A.BagWidthMax) AS BottomSewingEZOpen
FROM tblItemMasterPricingSpecs;
Thank you Arnelgp(y)🙏
 

AnilBagga

Member
Local time
Today, 18:43
Joined
Apr 9, 2020
Messages
223
Code:
SELECT
    tblItemMasterPricingSpecs.Group,
    tblItemMasterPricingSpecs.FGCode,
    tblItemMasterPricingSpecs.WidthBagOrFabric,
    tblItemMasterPricingSpecs.SewingTypeBottom,
    (SELECT TOP 1 BottomSewingStd FROM tblStdBagConvRates A
        WHERE tblItemMasterPricingSpecs.WidthBagOrFabric
        BETWEEN A.BagWidthMin AND A.BagWidthMax) AS BottomSewingStd,
    (SELECT TOP 1 BottomSewingEZOpen FROM tblStdBagConvRates A
        WHERE tblItemMasterPricingSpecs.WidthBagOrFabric
        BETWEEN A.BagWidthMin AND A.BagWidthMax) AS BottomSewingEZOpen
FROM tblItemMasterPricingSpecs;
ArnelGP
I am getting the 2 values correctly. The table has one value for sewing code. Can we not check this sewing code value and deliver the one valueAdd number? We can of course use an IIF condition to pick this, if this is the best way
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:13
Joined
May 21, 2018
Messages
8,463
There is no need for the subqueries which make it inefficient. You can pull any fields from either table. Just add both tables to the query and a where statement.
Code:
SELECT
    A.Group,
    A.FGCode,
    A.WidthBagOrFabric,
    A.SewingTypeBottom,
    B.BagConvID
    .... Any other Fields from B
    WHERE A.WidthBagOrFabric BETWEEN B.BagWidthMin AND B.BagWidthMax
FROM tblItemMasterPricingSpecs AS A, tblStdBagConvRantes As B;
 

AnilBagga

Member
Local time
Today, 18:43
Joined
Apr 9, 2020
Messages
223
There is no need for the subqueries which make it inefficient. You can pull any fields from either table. Just add both tables to the query and a where statement.
Code:
SELECT
    A.Group,
    A.FGCode,
    A.WidthBagOrFabric,
    A.SewingTypeBottom,
    B.BagConvID
    .... Any other Fields from B
    WHERE A.WidthBagOrFabric BETWEEN B.BagWidthMin AND B.BagWidthMax
FROM tblItemMasterPricingSpecs AS A, tblStdBagConvRantes As B;
I am not good in VBA or SQL and therefore unable follow this suggestion. What I need is one value as output. I have tried to convey this in the code below but this is plain English and please excuse me for the absurd syntax
Code:
SELECT
tblItemMasterPricingSpecs.Group,
tblItemMasterPricingSpecs.FGCode,
tblItemMasterPricingSpecs.WidthBagOrFabric,
tblItemMasterPricingSpecs.SewingTypeBottom,
(SELECT TOP 1 ValueAddBottom FROM tblStdBagConvRates A
        WHERE tblItemMasterPricingSpecs.WidthBagOrFabric
        BETWEEN A.BagWidthMin AND A.BagWidthMax) AS BottomSewingStd AND tblItemMasterPricingSpecs.SewingTypeBottom BETWEEN 1 AND 54 ,
        (SELECT TOP 1 ValueAddBottom FROM tblStdBagConvRates A
        WHERE tblItemMasterPricingSpecs.WidthBagOrFabric
        BETWEEN A.BagWidthMin AND A.BagWidthMax) AS BottomSewingEZOpen AND tblItemMasterPricingSpecs.SewingTypeBottom BETWEEN 55 AND 81
FROM tblItemMasterPricingSpecs;
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:13
Joined
May 21, 2018
Messages
8,463
Now I understand better. I did not see the different field for valuetoadd.

This would in my opinion by easier to do in a union query
In the first query
1 to 54 return BottomSewingStd as valuetoadd

qry1to54
Code:
SELECT
    A.Group,
    A.FGCode,
    A.WidthBagOrFabric,
    A.SewingTypeBottom,
    B.BagConvID
    B.BottomSewingStd as ValueToAdd
    WHERE (A.WidthBagOrFabric BETWEEN B.BagWidthMin AND B.BagWidthMax) AND (A.SewingTypeBottom between 1 and 54
FROM tblItemMasterPricingSpecs AS A, tblStdBagConvRantes As B;

For 55 to 81 you want to return bottomsewingezopen as valuetoadd


qry55to81
Code:
SELECT
    A.Group,
    A.FGCode,
    A.WidthBagOrFabric,
    A.SewingTypeBottom,
    B.BagConvID
    B.BottomSewingEZopen as ValueToAdd
    WHERE (A.WidthBagOrFabric BETWEEN B.BagWidthMin AND B.BagWidthMax) AND (A.SewingTypeBottom between 55 and 81)
FROM tblItemMasterPricingSpecs AS A, tblStdBagConvRantes As B;

qryAllValues

Code:
Select * from qry1to54 union select * from qry55to81
 
Last edited:

AnilBagga

Member
Local time
Today, 18:43
Joined
Apr 9, 2020
Messages
223
Now I understand better. I did not see the different field for valuetoadd.

This would in my opinion by easier to do in a union query
In the first query
1 to 54 return BottomSewingStd as valuetoadd

qry1to54
Code:
SELECT
    A.Group,
    A.FGCode,
    A.WidthBagOrFabric,
    A.SewingTypeBottom,
    B.BagConvID
    B.BottomSewingStd as ValueToAdd
    WHERE (A.WidthBagOrFabric BETWEEN B.BagWidthMin AND B.BagWidthMax) AND (A.SewingTypeBottom between 1 and 54
FROM tblItemMasterPricingSpecs AS A, tblStdBagConvRantes As B;

For 55 to 81 you want to return bottomsewingezopen as valuetoadd


qry55to81
Code:
SELECT
    A.Group,
    A.FGCode,
    A.WidthBagOrFabric,
    A.SewingTypeBottom,
    B.BagConvID
    B.BottomSewingEZopen as ValueToAdd
    WHERE (A.WidthBagOrFabric BETWEEN B.BagWidthMin AND B.BagWidthMax) AND (A.SewingTypeBottom between 55 and 81)
FROM tblItemMasterPricingSpecs AS A, tblStdBagConvRantes As B;

qryAllValues

Code:
Select * from qry1to54 union select * from qry55to81
So there will be 3 queries. The bag manufacturing several such processes. These is another process which uses the same condition of bag width but the SewingCodeTop conditions are 1 to 36 and 37 to 54. We should be able to have 2 different fields say Valueaddtop in qry1to54 and qry55to81 and the qryAllValues would still hold good. Correct!?
 

AnilBagga

Member
Local time
Today, 18:43
Joined
Apr 9, 2020
Messages
223
So there will be 3 queries. The bag manufacturing several such processes. These is another process which uses the same condition of bag width but the SewingCodeTop conditions are 1 to 36 and 37 to 54. We should be able to have 2 different fields say Valueaddtop in qry1to54 and qry55to81 and the qryAllValues would still hold good. Correct!?
I am getting a syntax error in qry1to54 and qry55to81
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:13
Joined
Feb 28, 2001
Messages
26,998
The best way we can help with syntax errors in queries is if you show us the query.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:13
Joined
May 21, 2018
Messages
8,463
Code:
I am getting a syntax error in qry1to54 and qry55to81
You do understand I do not have the database in front of me and I am typing in a forum window? You need to check all spelling, field names, table names, etc. You can build those individual queries in the design window.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:13
Joined
May 21, 2018
Messages
8,463
. These is another process which uses the same condition of bag width but the SewingCodeTop conditions are 1 to 36 and 37 to 54. We should be able to have 2 different fields say Valueaddtop in qry1to54 and qry55to81 and the qryAllValues would still hold good. Correct!?
I assume you will build two more queries
qry1to36Top and qry37to54Top and change the values and change the fields where you pull the data from. Then make a union query in the same way unioning the two queries.
 

AnilBagga

Member
Local time
Today, 18:43
Joined
Apr 9, 2020
Messages
223
Code:
I am getting a syntax error in qry1to54 and qry55to81
You do understand I do not have the database in front of me and I am typing in a forum window? You need to check all spelling, field names, table names, etc. You can build those individual queries in the design window.
I have enclosed the original tables. For the time being if you could help me build 2 queries for 2 processes, i would then be able to build the balance queries for other process.

If both the values, ValueAddTop and ValueAddBottom are in one query, it will be better


ValueAddTop
For items in tblItemMasterPricingSpecs.Group = 'Bag', and tblItemMasterPricingSpecs.sewingTypeTop is between 1 to 18, check for Hemming value in tblStdBagConvRates, and save as ValueAddTop, WHERE tblItemMasterPricingSpecs.tblStdBagConvRates is between tblBagMasterWt.WidthMin and tblBagMasterWt.Widthmax

ValueAddBottom
For items in tblItemMasterPricingSpecs.Group = 'Bag', and tblItemMasterPricingSpecs.sewingTypeBottom is between 37 to 54, check for BottomSewingStd in tblStdBagConvRates, and save the value as ValueAddBottom,WHERE tblItemMasterPricingSpecs.tblStdBagConvRates is between tblBagMasterWt.WidthMin and tblBagMasterWt.Widthmax

For items in tblItemMasterPricingSpecs.Group = 'Bag', and tblItemMasterPricingSpecs.sewingTypeBottom is between 55 to 81, check for BottomSewingEZOpen in tblStdBagConvRates, and save as ValueAddBottom,WHERE tblItemMasterPricingSpecs.tblStdBagConvRates is between tblBagMasterWt.WidthMin and tblBagMasterWt.Widthmax
 

Attachments

  • PP_Test_05Apr.zip
    49.2 KB · Views: 227

bastanu

AWF VIP
Local time
Today, 06:13
Joined
Apr 13, 2010
Messages
1,401
Hi Anil,

Is this close to what you want? You can add "processes" to the lookup tables if you need new thresholds.

Cheers,
Vlad
 

Attachments

  • PP_Test_05AprVlad.zip
    44.7 KB · Views: 281

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:13
Joined
May 7, 2009
Messages
19,169
here is a 2 query. check the finalQuery.
 

Attachments

  • PP_Test_05Apr.zip
    48.9 KB · Views: 277
Last edited:

Users who are viewing this thread

Top Bottom