urgent help with access query 2

[SharonMee]

Hello All:

I have the following sql in access. I would like the count of the processing time field with a criteria in the Pay field as follows (count all blank fields, count all fields with N/A, and only count fields with Pay AND the check amount is >250000).

SELECT Count([B&B No Negatives and Processing Time].[Processing Time]) AS [CountOfProcessing Time], [B&B No Negatives and Processing Time].[Processing Time]
FROM [B&B No Negatives and Processing Time]
WHERE ((([B&B No Negatives and Processing Time].[pay]) Is Null Or ([B&B No Negatives and Processing Time].[pay])="N/A")) OR ((([B&B No Negatives and Processing Time].[pay])="pay") AND (([B&B No Negatives and Processing Time].[Total Check Amount])>250000))
GROUP BY [B&B No Negatives and Processing Time].[Processing Time];

The query is working partially, but right now, its giving me the count for each processing time e.g.

count processing time
4 0
5 2
56 3

So, it's group all the processing time and count the number it finds in each group. How do I modify this sql so that it just gives me the total count of the processing time (e.g.) for the pay and check amouunt fields that meet the criteria?

Thanks so much for your help.


SharonMee

 

[PHV]

Something like this ?
SELECT Count(*) AS [CountOfProcessing Time], Sum([B&B No Negatives and Processing Time].[Processing Time]) AS [TotalOfProcessing Time]
FROM [B&B No Negatives and Processing Time]
WHERE (Nz([B&B No Negatives and Processing Time].[pay])<>"pay" OR [B&B No Negatives and Processing Time].[Total Check Amount]>250000);



Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SharonMee]

Ph,
That worked!!! thanks a lot.


SharonMee

 

[SharonMee]

One more question,

With the exact same conditions above, I would like to count only processing time less than 6. How do I modify this query?

Thanks for your patience.

SharonMee

 

[lespaul]

You just need to add that to the where clause:

SELECT Count(*) AS [CountOfProcessing Time], Sum([B&B No Negatives and Processing Time].[Processing Time]) AS [TotalOfProcessing Time] 
FROM [B&B No Negatives and Processing Time]
WHERE (Nz([B&B No Negatives and Processing Time].[pay])<>"pay" OR [B&B No Negatives and Processing Time].[Total Check Amount]>250000) AND ProcessingTime < 6;

Leslie

 

[SharonMee]

Hello:

I have the following sql, but I am getting an "Enter Parameter Value" prompt. Could it be that I don't have my brackets in the right spot, could you please explain.

SELECT Count([B&B No Negatives and Processing Time].[Processing Time]) AS [CountOfProcessing Time]
FROM [B&B No Negatives and Processing Time]
WHERE (Nz([B&B No Negatives and Processing Time].[pay])<>"pay" OR [B&B No Negatives and Processing Time].[Total Check Amount]>250000) AND [B&B No Negatives and Processing Time].[ProcessingTime] < 6;


I want the query to count the number of procesing time that are less than 6 based on the conditions in my first post, "I would like the count of the processing time field with a criteria in the Pay field as follows (count all blank fields, count all fields with N/A, and only count fields with Pay AND the check amount is >250000)."

Thanks much.

 

[lespaul]

What parameter is it prompting you for?

Leslie

 

[SharonMee]

Thanks Leslie for helping me,

I just saw that I didn't have a space between the "processing" and "time".

It's working fine now!


SharonMee

 

[lespaul]

pesky little space! Glad you got it worked out! And thanks for the star!

leslie