Criteria Row

[Greg]

Good afternoon,
I have a question problem that I can't seem to solve. It primarily regards
the usage of "and" and "or".

I have a table that looks like the following:

www,4,rrr, 1,2,3,4
xxx,4,eee,0,3,6,7
yyy,4,fff,0,0,0,0
zzz, 4,ddd,40,40,40,40
ggg,4,jjj,4,0,7,0
kkk,4,ddd,51,8,0,9

What I would like to do is select all rows with non zero values less than 50
in the 4th, 5th, 6th , and 7th column.

In the above example I would want the www, xxx, zzz, and ggg rows because
all the values are not zero and none of the values are greater than 50.

I do not want yyy because the values are all 0 and I do not want row kkk
because one of the values is greater than 50.

I am trying accomplish this via the criteria line of the query Wizard with
multiple lines and multiple "AND" and "OR" .

Does anyone have a suggestion?

Thank you in advance...

Greg

 

[John Spencer]

If the numbers are all integers try

Between 1 and 50 for the criteria. OR the criteria by putting the
criteria on different criteria lines.

Otherwise (values such as .00005 are possible) you can try

0 And <=50 as the criteria. Enter that on separate criteria lines

Access will probably reformat that when you save the query.

Or you can add each field 4 to 7 to the query grid twice.
Then do something like

Field: 4thCol
Criteria(1): > 0

Field: 4thCol(2nd Instance)
Criteria(1): <50


Field: 5thCol
Criteria(1): <<Blank>>
Criteria(2): > 0

Field: 5thCol(2nd Instance)
Criteria(1): <<Blank>>
Criteria(2): < 50

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Rob Parker]

Hi Greg,

Just a quick clarification.

It seems, from the sample results you quote, that you do not want "all the
values are not zero and ..."; rather, you want "any value is not zero and
....". Is this correct?

Rob

 

[John W. Vinson]

Good afternoon,
I have a question problem that I can't seem to solve. It primarily regards
the usage of "and" and "or".

I have a table that looks like the following:

www,4,rrr, 1,2,3,4
xxx,4,eee,0,3,6,7
yyy,4,fff,0,0,0,0
zzz, 4,ddd,40,40,40,40
ggg,4,jjj,4,0,7,0
kkk,4,ddd,51,8,0,9

What I would like to do is select all rows with non zero values less than 50
in the 4th, 5th, 6th , and 7th column.

In the above example I would want the www, xxx, zzz, and ggg rows because
all the values are not zero and none of the values are greater than 50.

I do not want yyy because the values are all 0 and I do not want row kkk
because one of the values is greater than 50.

I am trying accomplish this via the criteria line of the query Wizard with
multiple lines and multiple "AND" and "OR" .

Let's call these fields Field5, Field6, Field7 and Field8 for want of better
names. I'd do this in the SQL window (View... SQL) rather than the grid, which
will get messy if you go back to it!

Build a query in the grid with just one criterion, say > 0 on Field5. Include
whatever fields you want displayed, the sort order, etc. Then select View...
SQL to go to the *real* query. It will contain a clause

WHERE (([Field5]) > 0)

Edit this to

WHERE ([Field5] > 0 OR [Field6] > 0 OR [Field7] > 0 OR [Field8] > 0)
AND Field5 < 50 AND Field6 < 50 AND Field7 < 50 AND Field8 < 50


John W. Vinson [MVP]

 

[Greg]

Rob,

If all the values are 0, I do not want to select the record. If any of the 4
values are greater than 50, I do not want to select the record. To try to
state differently, I want to select records with the 4 values greater than 0
but less than 51 except in the situation where all values are 0 . I hope
this doesn't confuse things further...

Thank You,
Greg

 

[Jeff Boyce]

Greg

You've already received some good advice about "how to"...

Now, "why"? If you'll describe a bit more about what kind of data (the
fieldnames can sometimes help indicate this) you are using 4 fields (the >0,
<51, not all 0) to store. I'm not asking out of idle curiosity, but to see
if the way your data is structured will let Access' relationally-oriented
features/functions do their best work.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[John Spencer]

Whoops!! <= 50 not < 50

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Greg]

Jeff,
I am looking at several thousand wireless units(blackberry,Treo, Pda) on a
monthly basis for voice usage. When an employee separates from a company the
Manager does not always disconnect his communications devices or Plans. We
attempt to review voice usage each month for low values. If there is no
usage for the last 4 months inquiries are made and disconnect of service
often follows. This would be the all 0 condition. We are also interested in
those employees who are using very low voice usage, months with zero usage
and months with less than 50 min. These would be different employees than
the all 0. This information is extracted from a table of usage information.
The columns would represent Peak usage min,Off peak usage min, International
usage min, Weekend Usage, summed for the last four months.

Thank You,
Greg

 

[Greg]

Jeff,
I run a separate query to extract the records with all zero to generate
reports. I have no problem getting this query to work.

Greg

 

[Jeff Boyce]

Greg

Thanks for the additional information.

While I understand a bit more about the business arena you are working in, I
still don't have a clear picture of how the four "number" columns relate to
that business.

Are you saying that you have Month1, Month2, Month3, and Month4 columns? If
so, my previous comment about relationally-oriented data most definitely
applies. Repeating columns such as these are what you find in a
spreadsheet.

If you have [Person] and [UsageAmt] and [MonthOfUsage], these are three
fields in a table. New month? New Row! (not new column/field).

More info, please...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Greg]

Jeff,

The 4 column headings for the 4 number fields would be Peak usage min,Off
peak usage min, International
usage min, Weekend Usage. I have other tables from which this information is
derived. Each row would be a summary of the last 4 months of usage for that
device (Blackberry,Treo, PDA).

I have one row for usage for each device for each month identified by the
telephone number associated with that device in other tables.

Thank You,
Greg

Greg

Thanks for the additional information.

While I understand a bit more about the business arena you are working in, I
still don't have a clear picture of how the four "number" columns relate to
that business.

Are you saying that you have Month1, Month2, Month3, and Month4 columns? If
so, my previous comment about relationally-oriented data most definitely
applies. Repeating columns such as these are what you find in a
spreadsheet.

If you have [Person] and [UsageAmt] and [MonthOfUsage], these are three
fields in a table. New month? New Row! (not new column/field).

More info, please...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Jeff,
I am looking at several thousand wireless units(blackberry,Treo, Pda) on a
monthly basis for voice usage. When an employee separates from a company the
Manager does not always disconnect his communications devices or Plans. We
attempt to review voice usage each month for low values. If there is no
usage for the last 4 months inquiries are made and disconnect of service
often follows. This would be the all 0 condition. We are also interested in
those employees who are using very low voice usage, months with zero usage
and months with less than 50 min. These would be different employees than
the all 0. This information is extracted from a table of usage information.
The columns would represent Peak usage min,Off peak usage min, International
usage min, Weekend Usage, summed for the last four months.

Thank You,
Greg

 

[Greg]

I would like to thank you all for your input. I modified the SQL behind the
query as suggested by Mr. Vinson and have gotten the results I expected. I
repeat -thank you all for your suggestions!

Greg

Jeff,

The 4 column headings for the 4 number fields would be Peak usage min,Off
peak usage min, International
usage min, Weekend Usage. I have other tables from which this information is
derived. Each row would be a summary of the last 4 months of usage for that
device (Blackberry,Treo, PDA).

I have one row for usage for each device for each month identified by the
telephone number associated with that device in other tables.

Thank You,
Greg

Greg

Thanks for the additional information.

While I understand a bit more about the business arena you are working

in,

I
still don't have a clear picture of how the four "number" columns relate to
that business.

Are you saying that you have Month1, Month2, Month3, and Month4 columns? If
so, my previous comment about relationally-oriented data most definitely
applies. Repeating columns such as these are what you find in a
spreadsheet.

If you have [Person] and [UsageAmt] and [MonthOfUsage], these are three
fields in a table. New month? New Row! (not new column/field).

More info, please...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Jeff,
I am looking at several thousand wireless units(blackberry,Treo, Pda)

on

a company
the Plans.
We interested
in

 

[John W. Vinson]

I modified the SQL behind the
query as suggested by Mr. Vinson and have gotten the results I expected. I
repeat -thank you all for your suggestions!

Glad it worked out! Thanks for the feedback.

John W. Vinson [MVP]