Select different values from the same field dividing them in groups 2

[murphy123]

Hello,
I am having a problem trying to figure out a way to get a query result that breaks up one field into 4 different Amount groups. I had the same question last week, and I believe I didn't post it in the right forum. I am sorry. It was 703-1388918.

I am pulling the data from tblAjustments.

The fields that I am working with are:
AuditorID, AuditDate, Amount, PosterID, PostingDate

I need to know the count of:

Amount = or over -($50.00)
Amount Between -($15) and -($49.99)
Amount = -($3.99), -($4.99) and -($10.99)
Amount > 0

Then find out the amount of adjustments posted within the amounts above to see the percentage that has been audited for a specific time frame.

I tried to create 4 separate queries to give me the count of how many adjustments were audited, then combine them by
4 separate queries that shows the count of adjustments posted by the amounts above. I had almost 16 queries to accomplish one desired result.

I am a Access 2000 user. I was hoping I could use SQL join queries to get the results I am looking for.

Can I create one SQL statement using subqueries to allocate each amount to get the final result?

Any help is appreciated. I've been working on this for almost five days now.

Thanks

 

[lespaul]

can you also provide some sample data and expected results?

thanks,
Leslie

 

[Remou]

This may help: thread705-1383318

 

[VRoscioli]

I'm not sure I understand exactly what you are trying to do. Do the fields other than Amount matter? If not, try:

SELECT Sum(Iif([Amount]<=-50,1,0)) AS Cat1, 
    Sum(Iif([Amount]>-50 AND [Amount]<-15,1,0)) AS Cat2, 
    Sum (Iif([Amount] IN (-3.99, -4.99, -10.99),1,0)) AS Cat 3, 
    Sum(Iif([Amount]>0,1,0)) AS Cat 4 
FROM tblAdjustments;

If I am misunderstanding what you are asking for, please post back with an example of what you are trying to do.

-V

 

[murphy123]

Hi guys,

Thanks so much for the quick replies. I have a sample of the data ready to be posted. But after I read VRoscioli's reply, it looks like it is exactly what I am looking for.

I am going to test it, and if I have any problems I'll get back to you and post my data sample.

Again,

Thanks, Leslie, lespaul, Remou and VRoscioli.

 

[murphy123]

The code VRoscioli provided was right on the money. Thanks everyone.

For total adjustments audited:
qryAdjAuditedByCat

SELECT Sum(IIf([Amount]<=-50,1,0)) AS AuditCat1,
Sum(IIf([Amount]>-50 And [Amount]<-15,1,0)) AS AuditCat2,
Sum(IIf([Amount] In (-3.99,-4.99,-10.99),1,0)) AS AuditCat3,
Sum(IIf([Amount]>0,1,0)) AS AuditCat4
FROM tblAdjustments

WHERE ((tblAdjustments.AuditorID) Is Not Null)
AND ((tblAdjustments.PostingDate)
Between [ENTER START DATE] And [ENTER END DATE]);

One more question...

Based on the results from the statement above, I need to find out the percentage of of audited adjustments for each category. I created another query that is based on the same code but including all adjustments that were posted, regardless if they were audited or not. I need my user to click a button on a form and get the percentage results.

For total adjustment posted:
qryAdjPostedByCat

SELECT Sum(IIf([Amount]<=-50,1,0)) AS PostCat1,
Sum(IIf([Amount]>-50 And [Amount]<-15,1,0)) AS PostCat2,
Sum(IIf([Amount] In (-3.99,-4.99,-10.99),1,0)) AS PostCat3,
Sum(IIf([Amount]>0,1,0)) AS PostCat4
FROM tblAdjustments

WHERE ((tblAdjustments.PostingDate) Is Not Null)
AND ((tblAdjustments.PostingDate)
Between [ENTER START DATE] And [ENTER END DATE]);

I was thinking about linking these two queries to get a percentage on a report. I tried to link it by date. But I can't do it because I am missing a SELECT somewhere on the code and I can't figure out where.

Or is there a way to get the percentage of all adjustments audited in one single query?

Ex: AuditCat1/PostCat1 = my percentage