Solved Building a complex query (1 Viewer)

AnilBagga

Member
Local time
Today, 07:05
Joined
Apr 9, 2020
Messages
137
I have to build a query to calculate bag weight. I am calling this complex from my perspective

The bag weight has 2 major components

  • Fabric – the weight of this is easy to calculate. It is related to width of the bag and length
  • The yarn used sew the bottom of the bag. This yarn weight depends on different types to stitching on top of the bag – say SewA, SewB and SewC as well as the width of the bag which say is 3 categories – WidthA, WidthB and WidthC. So there are 9 possible values of yarn weight 3 x 3 matrix. These 9 values are stored in a Table with rows as below
  • Fields are Sew, WidthA,WidthB and WidthC and value of Sew are SewA, SewB and SewC
WidthAWidthBWidthC
SewA0.50.81.1
SewB0.60.81.0
SewC0.30.60.9

  • The WidthA, WidthB and WidthC are ranges of the actual Width of the bag (Width of the bag in a numeric field called Width) e.g if Width is between 10 and 13, the range is WidthA, between 13 to 15 is WidthB and between 15 to 18 is WidthC

How do I build a query to select the yarn weight?

There is a combo box say cboSew to select the type of Sewing SewA, SewB and SewC,

The argument needs to first read the width of the bag from a Table called CustInput where the user selects this width and length, select the width range it qualifies in WidthA, WidthB or WidthC, and based on this range and the cboSew value, return the weight of the yarn

The weight of the yarn+fabric weight gives the weight of the bag
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 17:35
Joined
Aug 30, 2003
Messages
34,818
The more normalized design would be a table with 3 fields: Sew, Width, value. You'd have 9 records instead of 3, and a query or lookup with criteria on sew and width would find your desired value.
 

AnilBagga

Member
Local time
Today, 07:05
Joined
Apr 9, 2020
Messages
137
Hi Paul

Thanks. That was just an example. The variables actually are 12 for the width range and 7 for the types of Sewing but that is not a deterrent. Making master data with 84 values is not a problem

We will have the masterdata with fields "WidthRange" and "SewType".
User data entry form will capture the Width as a numeric field and SewType from a combobox

Based on these inputs, we would need to first select the "WidthRange" based on the "Width" entered by the user. How do I select this range? For example if width entered falls between 10 to 13, the value in a text field in the User entry form should change to "Width10to13". The master data will use this value of "Width10to13" in the table as a field value!

User data entry form will capture the Width as a numeric field and SewType from a combobox and the value we need to fetch from the table is the data corresponding to the widthrange and sewing

The Input table and masterdata would something like this.
1. How do get the width range value based on Width in UserInput. A nested IIF statement with 9 variables of widthrange?
2. How do I get the weight from the 84 different combinations possible

MasterDataUserInput
WidthRangeSewingTypeWeightWidthWidthRangeSewingTypeWeight
Width10to13Single Stitch
1​
13.1​
Width13to15Single Stitch
1.2​
Width10to13DoubleStitch
2​
12.9​
Width10to13DoubleStitch
2​
Width13to15Single Stitch
1.2​
Width13to15DoubleStitch
2.4​
 

plog

Banishment Pending
Local time
Yesterday, 19:35
Joined
May 11, 2011
Messages
10,260
I am going to treat this like a query issue and disregard your form element because it just muddies the issue. So, let me restate your issue:

You have a table called UserInput with 2 fields [Width] and [SewingType] that you want to JOIN to MasterData and create a query which displays 4 fields: [Width], [WidthRange], [SewingType], [Weight].

If that's wrong, let me know, if not, here's what you do:

1. Remove [WidthRange] from MasterData and replace it with 2 numeric fields: [WidthMin] & [WidthMax]. So instead of [WidthRange]="Width10to13" you would instead have [WidthMin]=10 & [WidthMax]=13.

2. Once you have that it becomes a pretty simple query:

Code:
SELECT Width, "Width" & WidthMin & "to" & WidthMax AS WidthRange, UserInput.SewingType, Weight
FROM UserInput
INNER JOIN MasterData ON UserInput.SewingType = MasterData.SewingType
  AND Width>=WidthMin AND Width<=WidthMax

Lastly, you need to better define your ranges. As it is now, a record in UserInput could match 2 records in MasterData. Suppose UserInput has [Width]=13 & [SewingType]="SingleStitch". Your data will return 2 rows for that because 13 is the end of one range and the beginning of the other. You must decide which row is the correct one. Then to account for that change the >= and <= in the last line of the query I wrote.
 

AnilBagga

Member
Local time
Today, 07:05
Joined
Apr 9, 2020
Messages
137
I tried to do do this INNER JOIN. I get an INNER JOIN error. See DB enclosed. See Screenshot below

I am not good at VBA so pardon my curiosity but we dont use WidthRange of the Select statement anywhere.

Also can you help me understand what we are trying to achieve in this statement?


1595417032096.png
 

Attachments

  • PP_Test.zip
    35 KB · Views: 19

plog

Banishment Pending
Local time
Yesterday, 19:35
Joined
May 11, 2011
Messages
10,260
I don't know why but it wants the full table declaration in the INNER JOIN. I tested the below SQL on your database and it works:

Code:
SELECT Width, "Width" & WidthMin & "to" & WidthMax AS WidthRange, UserInput.SewingType, Weight
FROM UserInput
INNER JOIN MasterData ON UserInput.SewingType = MasterData.SewingType  AND UserInput.Width>=MasterData.WidthMin AND UserInput.Width<=MasterData.WidthMax

What we are achieving is the combination of your 2 tables into a query that JOINs them using a range instead of an equality. Check out the INNER JOIN clause, it makes the SewingTypes equal, but makes the Width fall between the Min and Max.
 

plog

Banishment Pending
Local time
Yesterday, 19:35
Joined
May 11, 2011
Messages
10,260
Here you go.
 

Attachments

  • weights.accdb
    604 KB · Views: 22

AnilBagga

Member
Local time
Today, 07:05
Joined
Apr 9, 2020
Messages
137
Thanks

To understand how it was done, I tried to open the Query in Design view. I get errors as per screenshots below. Can you please explain why? I can see the query only in SQL view

Also can I store the result of the query- the weight in the table UserInput in the field created for this?

1595431168054.png

1595431199692.png

1595431226593.png

1595431258780.png
 

plog

Banishment Pending
Local time
Yesterday, 19:35
Joined
May 11, 2011
Messages
10,260
Also can I store the result of the query- the weight in the table UserInput in the field created for this?

You can, but you shouldn't. In a properly structured database you don't copy data hither and yon, you store it in the correct place then when you need to combine data together you use queries and reference those in other objects (Forms, Reports, Code).

Code:
 To understand how it was done, I tried to open the Query in Design view. I get errors as per screenshots below. Can you please explain why?

The query designer doesn't support the type of JOIN I used (>=, <=). It can only support equijoins (=), so when you go into design view it yells at you that it doesn't understand the SQL and won't be able to represent it correctly in the Designer.

After you made it through those errors it actually should have opened in the designer--however it changed all the JOINs I used to equijoins. So, you should either not save it when you exit (thus reverting to the >=, <= joins) or you have to manually go into the SQL and change them back to the comparisons I used and then save it.
 

Users who are viewing this thread

Top Bottom