Count Records

[nittany77]

I need to count the number of records returned after a query and do some math to get a percentage. I tried a couple times using totals and count, but it is apparent I really don't know how to go about it. Attached is a sample database. What I would like to do is run the Query Form, select 7/1/14 for a start date, 8/20/14 for an end date, and Grinder 1 as the Workcenter. This returns 8 records, the column that I am interested in is the Yes/No column. One of the records is blank so I want to exclude that one, that leaves 7 records, 4 of these are Yes and 3 are No. I need to get the percentage of Yes records compared to the total, so I need to divide the 4 by 7 for a percentage of 57%.

I assume this is possible, right? Can someone point me in the right direction?

[June7]

Need additional criteria in query of: Not [Yes/No] Is Null

Consider:

SELECT Count(Rework.[Order Number]) AS [CountOfOrder Number], Count(IIf([Yes/No]="Yes",1,Null)) AS IsYes, Count(IIf([Yes/No]="Yes",1,Null))/Count([Order Number]) AS Pct
FROM Rework
WHERE (((Rework.Date) Between [Forms]![Query Form]![Text52] And [Forms]![Query Form]![Text54]) AND (Not (Rework.[Yes/No]) Is Null))
GROUP BY Rework.Workcenter
HAVING (((Rework.Workcenter)=[Forms]![Query Form]![Combo56]));

If you want to show detail records, this is probably best done in a report. Report RecordSource:
SELECT * FROM Rework
WHERE (((Rework.Date) Between [Forms]![Query Form]![Text52] And [Forms]![Query Form]![Text54]) AND (Not (Rework.[Yes/No]) Is Null))

Do aggregate calcs for counts in report footer. An expression in textbox to count just the Yes records:
=Count(IIf([Yes/No]="Yes", 1, Null)
or
=Sum(IIf([Yes/No]="Yes", 1, 0)


BTW, advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Better would be YesNo or Yes_No or something more descriptive like IncludeInReport.

[nittany77]

Thank you very much, that works well. I am interested in using the report to get detailed records, however I don't know how to use the code like you showed above in a report. Sorry for all of the questions but I am new to Access and learning on my own (with help).

[June7]

Set the report RecordSource property to the query. Put expressions in textbox ControlSource.

Building reports is basic Access functionality. Access Help has more guidelines or reference an introductory tutorial book.

[nittany77]

I have tried to get this to work in a report but just can't get it. I keep getting syntax error and expression operator errors even when using the code you gave me. I hate to ask for someone to do it for me but I just can't seem to get there.

[June7]

The aggregate expression must be in a Footer section. And sorry, I forgot a paren at the end. Add another paren at the end and see what happens. Always make sure brackets and parens are in pairs - remember Algebra.

If you want to provide db for analysis, follow instructions at bottom of my post.

[nittany77]

Still can't get it to work using the report. I am getting the data I want using the query form but when I create the report based on the query I still get the popup boxes asking me to enter the data instead of having selection boxes, just like I do if I click directly on the query.

[June7]

If you want to show detail records, using the wrong query for the report. Look at post 2 again. As noted, use the second query, with additional parameter for WorkCenter. Put Sum or Count expressions in textboxes in report footer section.

CountYes textbox expression: =Count(IIf([Yes/No]="Yes", 1, Null)
CountAll textbox expression: =Count(*)
Percent textbox expression: =CountYes / CountAll * 100

The form needs to be open and code needs to open report instead of query. However, the report will be behind the form because form is set to Modal.

Regardless, the Requery command is not needed.

[nittany77]

I got it working great, thank you for your patience.