Using Excel functions from within Access

message from opeyemi1 on 1 Jun 2004

I posted this earlier, but I think I posted it in the wrong forum.
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. In the Pay field, some rows have pay, some are
blank and some have N/As. I want the query to consider all these, and
not ignore the blank rows for example.

Please please help, this is urgent.

thanks

=?Utf-8?B?SmVycnkgVy4=?= replied to opeyemi1 on 2 Jun 2004

Before your Min/Max query, you want a limiting query that feeds data to it. To this new query, add your (Pay] and [Total Check Amount] fields. In [Total Check Amount] field, add citeria <250000. Sounds like you want all of [Pay], so do not add any limiting criteria to this field. If you do want to limit the [Pay], add that criteria into the next lower row than that of [TCA]. This returns ALL records with both criteria. If you put both criteria on the same horizontal row, this returns ONLY those records matching BOTH the criteria. Hoping this be-fuddled reply helps you somewhat. Bood luck.

Archived message: Using Excel functions from within Access (MS Access Error Message)