If Statement help !

  • Thread starter Thread starter MikeB
  • Start date Start date
M

MikeB

I need to write an If statement and need some help as this one has me lost.

I have four fields (yearly sales totals), and I want to only select those
records that have had sales in at least three years of the four years.
Sounds simple I just can't get it.

Examples
------------------------------
SalesField1=5
SalesField2=0
SalesField3=0
SalesField4=1
This record would not show as only sales in two of the four years.

SalesField1=5
SalesField2=2
SalesField3=0
SalesField4=6
This record would show as it does have sales in at least three of the four
years.

Any help anyone could give would greatly appreciated.

Thanks,
MikeB
 
Create a Calculated Field in your Query grid:

NonZeroSales: Abs((SalesField1>0.0) + (SalesField2>0.0) +
(SalesField3>0.0) + (SalesField4>0.0))

and in the criteria row of this Calculated Field, use:
= 3
Click to expand...
 
Add a calculated column to your query that checks to see if the Salesfield has a
value greater than 0 and then add that up and check if the total is greater than
2.

For example:

Field: NumYears:Abs(SalesField1>0 + Salesfield2>0 + SalesField3>0 +
SalesField4>0)
Criteria: >2

The calculation will probably wrap in the newsreader. Just get it all on one
line.
 
Not only is it not simple, but it is virtually impossible as you have your
data structured. Having your yearly sales totals in separate fields works
great in a spreadsheet application, like Excel, but it is a nightmare for a
relational database like Access.

You should have a field for the Yearly Total AND a field for the Year. Then
you can work with the data in a query to get the result you want.
 
This is a temporary fix, though.

I agrre with Lynn's assessment that the Table should be re-
structured.

HTH
Van T. Dinh
MVP (Access)
 
Back
Top