COUNT Query "almost" working right, need further separation in results

[Alhassani]

Hello,



I'm not getting exaclty the results that I want from the query below. As it runs now, it successfully tells me that out of 114 options, 89 records are listed as either pass or fyi. However, I want it to tell me how many passes or fails are listed per each distinct DCN listing. So, in this instance it should tell me that DCN 123 has 32 records that are either pass or fyi, and dcn 1234 has 57. Not sure how to make it separate by distinct DCN.

SELECT Count (*)
FROM (SELECT DISTINCT 
Audit_Scores.DCN as DCN,
Audit_Scores.Audit_Field as Field
FROM Audit_Scores
WHERE [Field_Result] = "Pass" OR [Field_Result] = "FYI")  AS T1;

[June7]

Suggest you build a report and use report Grouping & Sorting feature with summary calcs in each group level.

Need to know more about data structure. Why are you using DISTINCT and not a GROUP BY (Totals) aggregate query?

[Alhassani]

Was doing the DISTINCT because I'm an Access newbie and its one of the few clauses I have learned so far. Switched the whole set-up to a groupby clause, have worked through the bugs until the last part. It keeps telling me that there is a "Syntax error (missing operator)" in the Group By clause, and then highlights "BY".

The table consists of three columns that are generated from a series of queries and macros. The "DCN" column act like an identifier with the "Audit_Fields" column (Composite Primary Key). For every DISTINCT DCN, there will be 57 entires. Those 57 entires are then each identified as the testing field in the second column (Audit_Field). Then, each 57 tests on each DCN is given a value of either Pass, FYI, FYIP, or a specific defect code for an error.

I need to identify for each DCN how many fields were marked as either FYI or Pass. That will in turn be fed into a series of calculations (that I will probably be on here getting help with next week) to generate various employee reports. (i.e. 53 fields returned as FYI or Pass out of 55. Will then be averaging that to 99.36% field level score and 0% form level score.)

SELECT (Audit_Scores.DCN),
COUNT ([Audit_Scores.Audit_Field]) AS Total,
WHERE ([Audit_Scores.Field_Result]) = "FYI" OR [Audit_Scores.Field_Result] = "Pass",
GROUP BY [Audit_Scores.DCN];

[June7]

Remove the commas before WHERE and GROUP BY.

Did you use the query design grid?

[Alhassani]

No, I can't stand the design grid. I manually typed them in. Taking the commas out gives the statement "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

[June7]

Remove all the []. They aren't needed because the naming does not include spaces or special characters. Access will then apply them correctly.

Don't put parens around fieldnames unless they are within a function.

Suggest you use the builder and then switch to SQL view. It will help you learn to use the SQL view more effectively.

I just noticed the query is missing FROM clause.

[Alhassani]

Thanks June, and sorry for this delayed response. The updated code is below but I am still getting a syntax error. It says the error is in the FROM clause but highlights field name Audit_Field in the COUNT clause.

SELECT Audit_Scores.DCN
FROM Audit_Scores
COUNT Audit_Fields AS Total
WHERE Audit_Scores.Field_Result = "FYI" OR Audit_Scores.Field_Result = "Pass"
GROUP BY Audit_Scores.DCN;

[June7]

This is why I suggest you start the query with the Design view of query builder then switch to SQL view to see the correct syntax.

Open query builder, pull in the Audit_Scores table, click the Totals button on ribbon, set up fields in the grid, then switch to SQL view and you will see what is wrong with your query.

[Alhassani]

Thanks for the help June. I was thinking that there was something wierd with my version of access as the design view for a select query does not have a totals box. This has thrown me off for awhile because most every screen shot online has this box. Hitting the totals ribbon button fixes this. So, thank you for that.

As far as this query goes, one of our programmers happened to swing by today and take a look at what I was trying to do. He claims that the general syntax was correct, there were just a few modifications to be done. First, setting the statement up as I had it would return how many fields met both the DCN and Pass criteria as one record, and then DCN and FYI as a second record. In order to fix it we had to run a subquery to then combine the two records as one. Then, we switched the "WHERE" clause to a "HAVING" clause (not really sure why). The code below is the correct SQL statement and returns the numbers as intended.

 SELECT [%$##@_Alias].Audit_DCN, Sum([%$##@_Alias].count_x) AS Total_Score
FROM (SELECT COUNT(Field_Result) as count_x,DCN as Audit_DCN
FROM Audit_Scores
GROUP BY DCN, Field_Result
HAVING (Field_Result = "FYI" OR Field_Result = "Pass"))  AS [%$##@_Alias]
GROUP BY [%$##@_Alias].Audit_DCN;