urgent help with criteria in access query

  • Thread starter Thread starter opeyemi1
  • Start date Start date
O

opeyemi1

Hello everyone:

I have the following query set up and it works well so far:

SELECT Count([B&B No Negatives and Processing Time].[Processing Time]
AS [CountOfProcessing Time], Min([B&B No Negatives and Processin
Time].[Processing Time]) AS [MinOfProcessing Time], Max([B&B N
Negatives and Processing Time].[Processing Time]) AS [MaxOfProcessin
Time], Avg([B&B No Negatives and Processing Time].[Processing Time]) A
[AvgOfProcessing Time]
FROM [B&B No Negatives and Processing Time]

I have 2 other fields ([Pay] and [Total Check Amount], that I woul
like to add to the criteria for this query, such that when the query i
finding the Count, Min, Max and Avg of the Processing time it shoul
ignore instances when a row has Pay AND the Total Check Amount is les
than 250000 only. Therefore, it should find the count, min, max an
avg based on the remainder.

Please please help, this is urgent.


thank
 
Add WHERE criteria to your query. Assuming Pay is a text field and Total Check
Amount is a number field the SQL would look something like the following. Not
that for ease of entry I have aliased your table as BB. If Pay is a Boolean
(Yes/No) field then change the BB.Pay is Not Null to BB.Pay = False

SELECT
Count([BB].[Processing Time]) AS [CountOfProcessing Time],
Min([BB].[Processing Time]) AS [MinOfProcessing Time],
Max([BB].[Processing Time]) AS [MaxOfProcessing Time],
Avg([BB].[Processing Time]) AS [AvgOfProcessing Time]
FROM [B&B No Negatives and Processing Time] AS BB
WHERE BB.Pay Is Not Null
AND BB.[Total Check Amount] < 250000
 
Back
Top