QUERY METHOD

  • Thread starter Thread starter gambler844
  • Start date Start date
G

gambler844

I am using microsoft office 2003, i have windows Vista
Is there any way i can perform the following in my query.
I have three fields. [A], ,[C]
I would like to find the average number for the two smallest fields out of
three fields.
Eg A=4, B=2, C=10 My answer would be 3.
Then i could put the 3 in my report.
I sure would apprecitate any help or advise.
Thanks
ed
 
Use Excel.

Having three fields with "related" values (you are trying to average them,
so they must be related somehow) is just how you'd set up the data ... on a
spreadsheet!

Access offers a function that calculates average, but it works on data in a
single field (think tall & skinny, not wide).

Seriously, if you need to do that kind of math, consider exporting the raw
data to Excel and doing the math there.

Or seriously consider normalizing your data...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
My fields would be seperate like [tote1] [tote2] [tote3]
I was hoping i could write a formula in my query to give me the average.
If i use excel could i then import that number into my access report.
Thanks Jeff for taking the time to help me. I sure appreciate it.
ed
--
thanks


Jeff Boyce said:
Use Excel.

Having three fields with "related" values (you are trying to average them,
so they must be related somehow) is just how you'd set up the data ... on a
spreadsheet!

Access offers a function that calculates average, but it works on data in a
single field (think tall & skinny, not wide).

Seriously, if you need to do that kind of math, consider exporting the raw
data to Excel and doing the math there.

Or seriously consider normalizing your data...

Regards

Jeff Boyce
Microsoft Office/Access MVP

gambler844 said:
I am using microsoft office 2003, i have windows Vista
Is there any way i can perform the following in my query.
I have three fields. [A], ,[C]
I would like to find the average number for the two smallest fields out of
three fields.
Eg A=4, B=2, C=10 My answer would be 3.
Then i could put the 3 in my report.
I sure would apprecitate any help or advise.
Thanks
ed
Click to expand...
Click to expand...
 
I am using microsoft office 2003, i have windows Vista
Is there any way i can perform the following in my query.
I have three fields. [A], ,[C]
I would like to find the average number for the two smallest fields out of
three fields.
Eg A=4, B=2, C=10 My answer would be 3.
Then i could put the 3 in my report.
I sure would apprecitate any help or advise.
Thanks
ed
Click to expand...


What Jeff is saying about normalization is right on the mark. If you have a
tote1, tote2, and tote3 field... someday you'll need a tote4, and then you'll
need to redesign your table, all your queries, all your forms, all your
reports. Even if that doesn't happen you're losing the ability to use Totals
queries. "Fields are expensive, records are cheap" - if you have a one
(something) to many (totes) relationship, you really should have two tables in
a one to many relationship!

If you're stuck with this (incorrect, wide-flat) design, you'll need either a
nasty complicated nest of IIF statements, or a VBA function to take the three
values. Post back if you'ld like help going one way or the other (I'd really
recommend normalizing).
 
Im really dont know how to import data from access to excel. I know a little
about excel but not a lot. So once i got the data into excel i could try and
figure how to find my average. Then once i found it could i then import that
number back into my access report and how do i do that?
I dont know what normalizing means but i have made up a few IFF statements.
My strenghts are in queries and my report. I know it is way to much to ask
you to show me how to use excel to solve my problem and a complicated IIF
statement would take a lot of time. So unless you have a simple sountion IM
going to thank you for all your help. Hopefully someday i will learn more
about exporting into excell and use it. Once I find my average in excell can
i then export it into my report?

Thanks for you time and help
ed
--
thanks


John W. Vinson said:
I am using microsoft office 2003, i have windows Vista
Is there any way i can perform the following in my query.
I have three fields. [A], ,[C]
I would like to find the average number for the two smallest fields out of
three fields.
Eg A=4, B=2, C=10 My answer would be 3.
Then i could put the 3 in my report.
I sure would apprecitate any help or advise.
Thanks
ed
Click to expand...


What Jeff is saying about normalization is right on the mark. If you have a
tote1, tote2, and tote3 field... someday you'll need a tote4, and then you'll
need to redesign your table, all your queries, all your forms, all your
reports. Even if that doesn't happen you're losing the ability to use Totals
queries. "Fields are expensive, records are cheap" - if you have a one
(something) to many (totes) relationship, you really should have two tables in
a one to many relationship!

If you're stuck with this (incorrect, wide-flat) design, you'll need either a
nasty complicated nest of IIF statements, or a VBA function to take the three
values. Post back if you'ld like help going one way or the other (I'd really
recommend normalizing).
Click to expand...
 
Im really dont know how to import data from access to excel. I know a little
about excel but not a lot. So once i got the data into excel i could try and
figure how to find my average. Then once i found it could i then import that
number back into my access report and how do i do that?
I dont know what normalizing means but i have made up a few IFF statements.
My strenghts are in queries and my report. I know it is way to much to ask
you to show me how to use excel to solve my problem and a complicated IIF
statement would take a lot of time. So unless you have a simple sountion IM
going to thank you for all your help. Hopefully someday i will learn more
about exporting into excell and use it. Once I find my average in excell can
i then export it into my report?
Click to expand...

As for normalization I'd suggest you check out some of the tutorials at:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

I'm guessing that using Excel would be more work than it's worth. I'll toss
together a quick VBA function to solve your current problem if you can answer
a couple of questions: 1. What are the datatypes of the Tote fields?
Number/Long Integer, Number/Double, Currency...? 2. What datatype do you want
for the result - Long Integer, a whole number, or a decimal number (i.e. the
average of 2 and 3 is 2.5 - or do you want it to be 2, or perhaps 3)?
 
Thanks for the resourse links. I saved each one to my favorite and plan on
reading each one as time permits.
Yor are right about excel, the work would be more than its worth.
I heard of VBA but dont know anything about it.
1. My data type is numbers
2. None decimal number like would be fine.
I sure appreciate you taking your time to help me.
ed
 
My fields would be seperate like [tote1] [tote2] [tote3]
I was hoping i could write a formula in my query to give me the average.
If i use excel could i then import that number into my access report.
Thanks Jeff for taking the time to help me. I sure appreciate it.
ed
Click to expand...

grumble grump...

I really feel bad about doing this because it's letting you continue to
maintain a BAD DESIGN which *should absolutely be changed*. But I'll hold my
nose and post it anyway...

Overelaborate, but you can pass AvgLowN the number of values that you want to
average followed by any desired number of arguments. For your case, use

AvgLowN(2, [Tote1], [Tote2], [Tote3])

and it will average the two lowest of the three values.

Copy and paste the code between the ============ lines into a new Module;
select Debug... Compile <my project>; save the module as basAverage, and try
it in your query.

==================
Private Sub bubble(N As Integer, arr() As Integer)

'N is the number of integers in the array'
'0 to N-1'

Dim I As Integer, J As Integer, P, Temp As Integer

For I = N - 1 To 0 Step -1
P = 0
For J = 0 To I
If arr(J) > arr(J + 1) Then
Temp = arr(J)
arr(J) = arr(J + 1)
arr(J + 1) = Temp
Else
P = P + 1
End If
If P = I Then GoTo premend
Next J
Next I

'premend = premature ending = all integers are allready sorted'
premend:

End Sub

Public Function AvgLowN(N As Integer, ParamArray arr()) As Integer
Dim iSum As Integer
Dim iPos As Integer
Dim arrN() As Integer
iSum = 0
If UBound(arr) >= N - 1 Then
ReDim arrN(UBound(arr()))
For iPos = 0 To UBound(arr)
arrN(iPos) = arr(iPos)
Next iPos
Call bubble(UBound(arrN), arrN)
For iPos = 0 To N - 1
iSum = iSum + arrN(iPos)
Next iPos
AvgLowN = iSum \ N
Else
MsgBox "Too few values provided!", vbOKOnly
AvgLowN = 0
End If
End Function
==================
 
"enabler"... <g>

Jeff B.
John W. Vinson said:
My fields would be seperate like [tote1] [tote2] [tote3]
I was hoping i could write a formula in my query to give me the average.
If i use excel could i then import that number into my access report.
Thanks Jeff for taking the time to help me. I sure appreciate it.
ed
Click to expand...

grumble grump...

I really feel bad about doing this because it's letting you continue to
maintain a BAD DESIGN which *should absolutely be changed*. But I'll hold
my
nose and post it anyway...

Overelaborate, but you can pass AvgLowN the number of values that you want
to
average followed by any desired number of arguments. For your case, use

AvgLowN(2, [Tote1], [Tote2], [Tote3])

and it will average the two lowest of the three values.

Copy and paste the code between the ============ lines into a new Module;
select Debug... Compile <my project>; save the module as basAverage, and
try
it in your query.

==================
Private Sub bubble(N As Integer, arr() As Integer)

'N is the number of integers in the array'
'0 to N-1'

Dim I As Integer, J As Integer, P, Temp As Integer

For I = N - 1 To 0 Step -1
P = 0
For J = 0 To I
If arr(J) > arr(J + 1) Then
Temp = arr(J)
arr(J) = arr(J + 1)
arr(J + 1) = Temp
Else
P = P + 1
End If
If P = I Then GoTo premend
Next J
Next I

'premend = premature ending = all integers are allready sorted'
premend:

End Sub

Public Function AvgLowN(N As Integer, ParamArray arr()) As Integer
Dim iSum As Integer
Dim iPos As Integer
Dim arrN() As Integer
iSum = 0
If UBound(arr) >= N - 1 Then
ReDim arrN(UBound(arr()))
For iPos = 0 To UBound(arr)
arrN(iPos) = arr(iPos)
Next iPos
Call bubble(UBound(arrN), arrN)
For iPos = 0 To N - 1
iSum = iSum + arrN(iPos)
Next iPos
AvgLowN = iSum \ N
Else
MsgBox "Too few values provided!", vbOKOnly
AvgLowN = 0
End If
End Function
==================
Click to expand...
 
Back
Top