Grouping by Weight Groups

  • Thread starter Thread starter tmkscout575
  • Start date Start date
T

tmkscout575

I have a simple query of shipments & revenue that I need to query by
about 14 weight groups (ex. 0 - 150; 151 - 300; 301 - 500; etc...).
Can anyone offer advise how to accomplish?

Thank You.
 
Dear Toni:

I would create a table for these weight groups, each with a text name
column and the maximum weight for that weight group. I have seen many
designs where the minimum is also stored, but that can be slightly
dangerous.

When querying, I always find the weight group with the lowest maximum
weight among all those >= the actual weight.

SELECT MIN(MaxWeight) FROM WeightGroup WHERE MaxWeight >= ActualWeight

Both the MaxWeight and the text "Label" should be unique. You may
need to add other columns depending on your shipping cost algorithms.

To be very specific, the categories are:

Up to 150
Over 150 and up to 300
etc.

That is, if you have decimal parts, like 150.01, then the weight would
not be in 0-150 nor in 151-300. That's one of the drawbacks to
putting in a minimum weight - you might then have reasonable weights
that don't fall in any category. By having only the maximum weight of
each category, you can eliminate that, and other errors.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I have a simple query of shipments & revenue that I need to query by
about 14 weight groups (ex. 0 - 150; 151 - 300; 301 - 500; etc...).
Can anyone offer advise how to accomplish?

Thank You.
Click to expand...

A "Non Equi Join" query is handy here. Create a table WeightGroups
with three fields - Low, High, Groupname.

You will need to go into the SQL window to create the join; if you're
not familiar with SQL, it's easiest if you first create a Query
joining your Shipments table to WeightGroups, joining the weight field
to Low. Then go into SQL view and edit

ON [Shipments].[Weight] = [WeightGroups].[Low]

to

ON [Shipments].[Weight] >= [WeightGroups].[Low]
AND [Shipments].[Weight] <= [WeightGroups].[High]
 
John's approach is philosophically different from mine. I object to
having both high and low columns in this table, although that makes
the query more difficult. John's approach makes the query simpler,
but places a burden of accuracy and completeness on the person
creating the values in the table. I'll admit that most simple
designers choose his method (not meant as an insult to John - he's
probably fully aware of the issues involved). I've had too much
trouble with having someone set up the WeightGroup table poorly so it
gives multiple results, or no results.

For example:

min max
0 150
151 300
300 500

Now, to which group does 150.7 belong? And 300 belongs to two groups.

If you give them only the ability to set a max for each bracket, these
problems go away. I've seen user make these mistakes - gaps between
groups and overlapping groups. What can I say? I don't like to leave
such holes in my designs.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I have a simple query of shipments & revenue that I need to query by
about 14 weight groups (ex. 0 - 150; 151 - 300; 301 - 500; etc...).
Can anyone offer advise how to accomplish?

Thank You.
Click to expand...

A "Non Equi Join" query is handy here. Create a table WeightGroups
with three fields - Low, High, Groupname.

You will need to go into the SQL window to create the join; if you're
not familiar with SQL, it's easiest if you first create a Query
joining your Shipments table to WeightGroups, joining the weight field
to Low. Then go into SQL view and edit

ON [Shipments].[Weight] = [WeightGroups].[Low]

to

ON [Shipments].[Weight] >= [WeightGroups].[Low]
AND [Shipments].[Weight] <= [WeightGroups].[High]
Click to expand...
 
John's approach is philosophically different from mine. I object to
having both high and low columns in this table, although that makes
the query more difficult. John's approach makes the query simpler,
but places a burden of accuracy and completeness on the person
creating the values in the table. I'll admit that most simple
designers choose his method (not meant as an insult to John - he's
probably fully aware of the issues involved).
Click to expand...

Excellent points (as usual), Tom! I should have mentioned that this
approach is not truly normalized as the WeightGroups records are
dependent upon each other, not just upon each record's PK.

It's one of several instances where there are several ways to do
something - each with its own advantages and disadvantages.
 
Back
Top