Query with both AND OR criteria

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

Guest

TIA

I need to write a query that will allow me to count the number of records that meet a set of criteria for a given time frame (usaully a month).

Field1 is Yes or No
Field2 is Yes or No
Field3 is Yes or No
Field4 is CalculatedField1 <= Field5

So if any of the fields 1,2,3 are Yes AND Field4 is less than or equal to a threshhold value (found in Field5) then I need to be able to count it. Yes can be in one, two, or all three of the Yes/No Fields; so long as a Yes is found (and Field4 evaluates as true) I need to count it.

I hope I've been clear.

Michael
 
Something like...

SELECT Count(TestTable.Field1) AS RecordCount
FROM TestTable
WHERE (TestTable.Field1=Yes or TestTable.Field2=Yes or TestTable.Field3=Yes)
and (TestTable.Field4<=TestTable.Field5)

Not sure how the "given time frame" bit fits in... I'm not clear what you
mean. Perhaps you could post more detail.

HTH
Sam

msprick said:
TIA

I need to write a query that will allow me to count the number of records
Click to expand...
that meet a set of criteria for a given time frame (usaully a month).
Field1 is Yes or No
Field2 is Yes or No
Field3 is Yes or No
Field4 is CalculatedField1 <= Field5

So if any of the fields 1,2,3 are Yes AND Field4 is less than or equal to
Click to expand...
a threshhold value (found in Field5) then I need to be able to count it.
Yes can be in one, two, or all three of the Yes/No Fields; so long as a Yes
is found (and Field4 evaluates as true) I need to count it.
 
Hi,


Edit the SQL expression and type the criteria:

WHERE ( field1 OR field2 OR fiedl3 ) AND ( field4 <=
field5 )


Note that it is not clear if Field4 is a field, or a computed field ( you
say "Field4 evaluates as true" ), neither what is CalculatedField1 ( you
say Field4 is less than or equal to a threshhold value ), so the exact
syntax I supplied is probably incorrect.



Hoping it may help,
Vanderghast, Access MVP


msprick said:
TIA

I need to write a query that will allow me to count the number of records
Click to expand...
that meet a set of criteria for a given time frame (usaully a month).
Field1 is Yes or No
Field2 is Yes or No
Field3 is Yes or No
Field4 is CalculatedField1 <= Field5

So if any of the fields 1,2,3 are Yes AND Field4 is less than or equal to
Click to expand...
a threshhold value (found in Field5) then I need to be able to count it.
Yes can be in one, two, or all three of the Yes/No Fields; so long as a Yes
is found (and Field4 evaluates as true) I need to count it.
 
Presumably you also have a date field (say fldDate) indicating when each
client contact occured. I think you need to group the results based on this
field...

Something like....

SELECT Count(TestTable.Field1) AS RecordCount, Year(fldDate) AS Yr,
Month(fldDate) AS Mth
FROM TestTable
WHERE (TestTable.Field1=Yes or TestTable.Field2=Yes or TestTable.Field3=Yes)
and (TestTable.Field4<=TestTable.Field5)
GROUP BY Year([fldDate]), Month([flddate])
ORDER BY Year([fldDate]), Month([flddate])

HTH
Sam

msprick said:
Sam,

Well the number needs to be calculated on a monthly basis so it can be
Click to expand...
reported back to management et al.
Basically the client comes in and is evaluated. If condition1 is yes or
Click to expand...
condition2 is yes or condition3 is yes and they are below a certian income
ratio (based on family size - hence the calculation) then another agency can
be billed for the cost to service that client.
So the question in English is 'How many clients did we see this month for
Click to expand...
whom we are eligible to recover costs?'
 
Back
Top