urgent help with criteria in access query

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 Processing
Time].[Processing Time]) AS [MinOfProcessing Time], Max([B&B No
Negatives and Processing Time].[Processing Time]) AS [MaxOfProcessing
Time], Avg([B&B No Negatives and Processing Time].[Processing Time]) AS
[AvgOfProcessing Time]
FROM [B&B No Negatives and Processing Time]

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

Please please help, this is urgent.


thanks


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

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

opeyemi1 wrote:

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 Processing
Time].[Processing Time]) AS [MinOfProcessing Time], Max([B&B No
Negatives and Processing Time].[Processing Time]) AS [MaxOfProcessing
Time], Avg([B&B No Negatives and Processing Time].[Processing Time]) AS
[AvgOfProcessing Time]
FROM [B&B No Negatives and Processing Time]

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

Please please help, this is urgent.

thanks

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/