If Statement need help

[Sean]

I set a query up to determine if Length, Width, or Height is the largest
dimension of the 3. The fields in my query are:

Item = Y488626
ItemLength = 38
ItemWidth = 40
ItemHeight = 17

The if statement I used to do this is:
L1: IIf([ItemLength]>[itemHeight] And
[itemwidth],[itemlength],IIf([itemheight]>[itemlength] And
[itemwidth],[itemheight],IIf([itemwidth]>[itemheight] And
[itemlength],[itemwidth])))

The result for L1 is 38 which is not the largest dimension, 40 is. What am
I doing wrong to determine L1? Thanks,

 

[Steve]

Set up your tables like this:
TblItem
ItemID
ItemNumber

TblDimension
DimensionID
Dimension (Length, Width, Height)

TblItemDimension
ItemDimensionID
ItemID
DimensionID
DimensionMeasurement

Create a query that includes TblIyem and TblItemDimension. Include
ItemNumber from TblItem and DimensionMeasurement from TblItemDimension.
Change the query to a Totals query. Click on the Sigma button (looks like a
capital E) in the menu at the top of the screen. Under DimensionMeasurement
Change Group By to Max. Leave Group By unchanged under ItemNumber. This
query will return the max dimension for each item.

Steve
(e-mail address removed)

 

[Beetle]

L1: IIf([ItemLength]>[ItemHeight] And [ItemLength]>[itemWidth],
[ItemLength],IIf([ItemHeight]>[ItemLength] And
[ItemHeight]>[ItemWidth],[ItemHeight],[ItemWidth]))

 

[Steve]

There are three records for Dimension in TblDimension:
"Length"
"Width"
"Height"

DimensionMeasurement in TblItemDimension is a numerical value and can be a
Length, Width or Height. DimensionID says what it is. Therefore, each item
has three records in TblItemDimension. This puts all three dimensions in the
same column and thus makes finding the max dimension easy with a Totals
query.

Steve

I don't see where a Dimension table and a junction table are needed in this
situation. It is not clear whether you mean Dimension in tblDimension to
be
a multi-value field, or if you just used shorthand for three fields (L, W,
and H), but in any case it seems to me it would make sense to have a
dimension table only if the idea is to store every possible dimension
combination. Unless that is the case, L, W, and H are atttibutes of the
Item.


That said, perhaps tblDimension could be something like this, related
one-to-
one with tblItem:

TblItemDimension
DimensionID
ItemID
Dimension
DimensionDetail (L,W, or H)

For each ItemID in tblDimension there would be three records (L,W, and H).
You could then find the maximum value, bringing along its DimensionDetail,
for each ItemID. I don't see what advantage this has over L, W, and H
fields
in tblItem, with an expression to find the maximum of the three, but I
expect
it could be done.

Set up your tables like this:
TblItem
ItemID
ItemNumber

TblDimension
DimensionID
Dimension (Length, Width, Height)

TblItemDimension
ItemDimensionID
ItemID
DimensionID
DimensionMeasurement

Create a query that includes TblIyem and TblItemDimension. Include
ItemNumber from TblItem and DimensionMeasurement from TblItemDimension.
Change the query to a Totals query. Click on the Sigma button (looks like
a
capital E) in the menu at the top of the screen. Under
DimensionMeasurement
Change Group By to Max. Leave Group By unchanged under ItemNumber. This
query will return the max dimension for each item.

Steve
(e-mail address removed)

I set a query up to determine if Length, Width, or Height is the largest
dimension of the 3. The fields in my query are:

[quoted text clipped - 13 lines]

am
I doing wrong to determine L1? Thanks,

 

[Steve]

YES ........

Are you saying tblDimension is like this:

tblDimension
DimensionID (Number)
Dimension (Text)

If so, would the three records look something like this:

DimensionID Dimension
1 Length
2 Width
3 Height

There are three records for Dimension in TblDimension:
"Length"
"Width"
"Height"

DimensionMeasurement in TblItemDimension is a numerical value and can be a
Length, Width or Height. DimensionID says what it is. Therefore, each item
has three records in TblItemDimension. This puts all three dimensions in
the
same column and thus makes finding the max dimension easy with a Totals
query.

Steve

I don't see where a Dimension table and a junction table are needed in
this
situation. It is not clear whether you mean Dimension in tblDimension
to

[quoted text clipped - 55 lines]

am
I doing wrong to determine L1? Thanks,

 

[Steve]

Having L, W and H in three separate fields complicates finding the max value
of the three when a simple Totals query can be used when the three are in
one column. Normally when the three are independent attributes not to be
processed together, it's okay to put them as three separte fields in the
item table. However, when the are processed together (find max), they exist
as a one-to-many relationship with Item and therefore need their own table
under the rules of normalization.

Steve

I would not use a table to store DimensionID and a text value. Rather, I
would indicate in tblDimension whether the value is L, W, or H, as I
described earlier. I guess there's no problem with using a tblDimension
as
you describe, but it strikes me as unnecessary overhead. The same number
of
fields would be stored in either case.

Actually, I don't know that I would use a related table at all. Rather, I
would be inclined to use a single table and an expression as I first
described, and as others described. There didn't seem to me anything
inherently wrong with the design the OP described, as length, width, and
height may be considered attributes of the item, so I didn't see a need to
urge the OP to reconsider the design.

YES ........

Are you saying tblDimension is like this:

[quoted text clipped - 30 lines]

am
I doing wrong to determine L1? Thanks,

 

[James A. Fortune]

For a situation where the Length, Width and Height attributes are only
appropriate to particular entities I'd adopt a different model. Say we have
a table Objects, some of which are Balls (spheres) and some of which are
Boxes (regular hexahedrons). The size of former can be defined by the
attribute 'Diameter', the latter by the attributes Length, Width and Height.
Balls and Boxes are sub-types of objects therefore, sharing all the
attributes of the (super) type, but not those of the other sub-type. So I'd
model this in the usual way with an Objects table and Balls and Boxes tables,
each related one-to-one to Objects.

This seems to me to be conceptually more correct than the sort of approach
proffered by Steve, which I think confuses attribute types and attribute
values.

Ken Sheridan
Stafford, England

At the risk of not pulling a log out of my eye in order to see the
speck in someone else's eye, may I suggest the term "rectangular"
instead of "regular." To me, a regular hexahedron would have the same
value for Length, Width and Height and be analogous to a sphere in
having those attributes defined by a single (no pun intended) scalar.
See:

http://mathworld.wolfram.com/RegularPolygon.html

Although nonrectangular hexahedra have Length, Width and Height
attributes as well, it seems to me that the term 'rectangular
hexahedra' conveys more accurately the "shoebox" kinds of shapes that
seem to be implied by the OP.

I agree that using a single column to find the maximum is clean. Your
subquery example is almost identical to what I likely would have done.

After doing quite of bit of graphics in Access recently, I note that
attributes of the solid like Length, Width and Height are often more
conveniently expressed with a custom Type. E.g.:

Public Type Point
X As Double
Y As Double
Z As Double
End Type

Public Type RectangularHexahedron
L As Double
W As Double
H As Double
Color As Long
Origin As Point
End Type

so that I can define my solids like:

Dim Shoebox(20) As RectangularHexahedron

For I = 1 To 20
With Shoebox(I)
.L = Length(I)
.W = WidthBox(I) 'Width(I) gets confused with the Width #
Statement
.H = Height(I)
.Origin.X = OriginX(I)
.Origin.Y = OriginY(I)
.Origin.Z = OriginZ(I)
End With
Next I

Once I have such a set of solids, how do I store them in tables? If
no analysis needs to be done, then L, W, H, X, Y, Z suffices. I can
also note that I am going to need to find the maximum dimension along
with which one(s) and decide to use three records in a related table
to store the dimensions. Your idea to have the Balls and Boxes tables
"inherit" from an Objects table is interesting.

But wouldn't it be great to set up a blob type and simply save the
solids as RectangularHexahedron's? I don't actually mean the custom
Type shown above. I mean the actual CAD model for each solid. SQL
Server 2008 allows you to do just that with its Remote BLOB Store. If
the tables are not being used to generate the solids, a CAD program
could make any changes directly to the solid stored in SQL Server. It
might be worth giving up a lot of computational efficiency to have
that capability. It would also possibly eliminate the need for
separate consideration of spheres and rectangular hexahedra, depending
on the capabilities of the CAD program. It is interesting to note that
CATIA 5 under Windows allows VBA scripting.

Please excuse the desultoriness of my thoughts here.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

Re 'desultoriness'. 'Desultory' has an interesting etymology. The Latin
'desultor' was an athlete in the circus who vaulted from horse to horse. Its
generally thought to stem from de- and the verb 'salire' which means 'to
leap'. The Latin adjective 'desultorius' does have a meaning 'inconstant'
more akin to our modern use of the English word, but only in literary Latin,
usually in the context of love.

Your knowledge of Latin is enviable. Desultory does have bad
connotations indeed! I did not mean to imply those -- especially to
myself. For a similar reason, I used the phrase "separate
consideration of spheres and rectangular hexahedra" instead of
something that could be easily twisted into something improper .
As far as the definition of the "shoebox," the best I could find was:

http://mathworld.wolfram.com/Cuboid.html

That fits in nicely with the sphere/cube analogy I mentioned,
producing spheroid/cuboid.

I try to make such suggestions with a spirit of humility, knowing that
I might be on the receiving end the next time, especially when dealing
with someone so astute.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

Well, that's a turn up for the book! Cuboid was actually the word I
originally had in mind, but I thought it was just a figment of my imagination.
If only I'd checked the OED, which does have it:

cuboid /0ˈkju:bɔɪd/ adjective & noun. E19.
[ORIGIN Modern Latin cuboides from Greek kuboeidēs, from kubos cube noun¹:
see -oid.]

â–º A adjective. Resembling or approximating to a cube in form, cuboidal. E19.
cuboid bone, a squat bone on the outer side of the foot, next to the fourth
and fifth metatarsals.

â–º B noun.
1 The cuboid bone. M19.

2 A solid with six faces that are rectangular but not all equal; an object
with a cuboid shape. L19.

cuˈboidal adjective resembling or approximating to a cube in form; Anatomy of
or pertaining to the cuboid bone; (of epithelium) composed of cuboidal cells:
E19.

I can't find the original Greek form cited, other than Kubos of course, in
Liddell and Scott, but I only have the concise edition. BTW did you know
that Liddell was the father of Alice Liddell, aka Alice in Wonderland?

Mine cites κυβοειδές. Is thatthe original Greek form cited in
yours? It also says:

1890 R. B. HAYWARD "Elem. Solid Geom." 78 Cuboids . . on the same base
are to one another as their heights. Note. The need of some short
word in the place of the polysyllabic 'rectangular parallelopiped' has
long been felt. I have coined the word 'cuboid'.

I didn't know about Liddell. Small world . Marvelous.

BTW, I'm working on an interesting, new way to create generalized
fonts for display within PDF files. I haven't finished the
mathematics yet, so that's why I didn't use it for the Greek letters.
The idea is to create pen/brush strokes as if you are actually holding
a pen/brush. A generalized Envelope Theory can be used to find where
the "ink" intersects with the paper. Incorporating the physics of
pressing a deformable nib into the paper allows for strokes of varying
thickness. Incorporating the deformations of brushes of various sizes
will be a challenge.

James A. Fortune
(e-mail address removed)