Query help: 3 queries & new to SQL...how do I do this??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to create a NEW field in query, by looking at 5 fields in a row and
returning the highest value found in any one of those 5 fields.

2nd query: I need to create another NEW field, populate it by multiplying 2
fields in that row together.

3rd query: Create another NEW field by multiplying 5 fields together, and
populate the field.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Actually, you need to redesign your table(s), 'cuz from your
descriptions your tables are not in Normal form. If you don't know what
Normal form is - read some articles on the web (search Google.com for
"Normalized" or "3NF" or "Third Normal Form"); read a book on database
design (see Amazon.com or BarnesAndNoble.com)

Generally, tables don't have columns that hold the result of an
expression on other columns in the same row. The expression should be
evaluated during a query run, or in a View if your db can create one.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQe7QyYechKqOuFEgEQLxDQCfQww7yF5433wLL9FbVRZSzsZl3+kAoN5q
Ftv+VcLEq5BjLaGAv6q5bvL+
=4EAi
-----END PGP SIGNATURE-----
 
I am assuming that you really want a calculated field in your queries. General
rule of thumb - if you can calculate a value, don't store it.

Your First Query implies a design problem. Are you stuck with the layout? If
so, post back and tell us why or do a google search against the groups and see
if you can find a GetMax function that works across rows. I've probably posted
one at some time in the past (search for fGetMax).

2nd Query
Field: MultiplyThis: [Field1] * [Field2]

3rd Query
Field: MultiplyThis: [Field1] * [Field2] * [Field3] * [Field4] * [Field5]
 
I can't change the DB table layout. Our database analyst said it was best to
find out the information by query. It would be nice to be able to save the
result in new field.
I am examining "weight" fields for statistical purposes from the 5 fields
and I need to pass the highest value to a Form & Report; this is my ultimate
goal.

I did a search on GetMax and got nothing in the threads; have nothing on it
in my VB 6 book either. I am using MS Access 2000 version.

any recommendations?



John Spencer (MVP) said:
I am assuming that you really want a calculated field in your queries. General
rule of thumb - if you can calculate a value, don't store it.

Your First Query implies a design problem. Are you stuck with the layout? If
so, post back and tell us why or do a google search against the groups and see
if you can find a GetMax function that works across rows. I've probably posted
one at some time in the past (search for fGetMax).

2nd Query
Field: MultiplyThis: [Field1] * [Field2]

3rd Query
Field: MultiplyThis: [Field1] * [Field2] * [Field3] * [Field4] * [Field5]
I need to create a NEW field in query, by looking at 5 fields in a row and
returning the highest value found in any one of those 5 fields.

2nd query: I need to create another NEW field, populate it by multiplying 2
fields in that row together.

3rd query: Create another NEW field by multiplying 5 fields together, and
populate the field.
Click to expand...
Click to expand...
 
If you're stuck, you're stuck.

Try the following VBA. Copy it and paste it into a module.

'------------- Code Starts --------------
Public Function fGetMaxNumber(ParamArray Values()) As Variant
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Returns the Maximum Number of a group of values passed to it.
'Sample call: myMax = GetMaxNumber("-21","TEST","2", "3",4,5,6,"7",0) returns 7
'Ignores values that cannot be treated as numbers.

Dim i As Integer, vMax As Variant, tfFound As Boolean, dblCompare As Double

vMax = -1E+308 'very large negative number
For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then
dblCompare = CDbl(Values(i))
If dblCompare > vMax Then
vMax = dblCompare
tfFound = True
End If
End If
Next

If tfFound Then
fGetMaxNumber = vMax
Else
fGetMaxNumber = Null
End If

End Function

You can use that in your query to get the highest value. In the grid, something like:

Field: HighNum: fGetMaxNumber([Field1],[Field2],[Field3],[Field4],[Field5])

I'm sure there are others out there that will do the same thing.
I can't change the DB table layout. Our database analyst said it was best to
find out the information by query. It would be nice to be able to save the
result in new field.
I am examining "weight" fields for statistical purposes from the 5 fields
and I need to pass the highest value to a Form & Report; this is my ultimate
goal.

I did a search on GetMax and got nothing in the threads; have nothing on it
in my VB 6 book either. I am using MS Access 2000 version.

any recommendations?

John Spencer (MVP) said:
I am assuming that you really want a calculated field in your queries. General
rule of thumb - if you can calculate a value, don't store it.

Your First Query implies a design problem. Are you stuck with the layout? If
so, post back and tell us why or do a google search against the groups and see
if you can find a GetMax function that works across rows. I've probably posted
one at some time in the past (search for fGetMax).

2nd Query
Field: MultiplyThis: [Field1] * [Field2]

3rd Query
Field: MultiplyThis: [Field1] * [Field2] * [Field3] * [Field4] * [Field5]
I need to create a NEW field in query, by looking at 5 fields in a row and
returning the highest value found in any one of those 5 fields.

2nd query: I need to create another NEW field, populate it by multiplying 2
fields in that row together.

3rd query: Create another NEW field by multiplying 5 fields together, and
populate the field.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top