Count complaint reason

[KevinSlater]

Hi,

I would like to be able to count the number of complaints in a query by counting the number of times a product code appears for each complaint description but not sure how.

So when the user starts the Query it promts to: "Enter a partcode" so if the user enters "2358NXA" the results then show something similar to the below:

Product code Complaint reason Count
2358NXA Not charging 22
2358NXA not working properly 8
2358NXA Other reason 3

Please see attached screenshot of current query. Is this possible? any help on how to do this would be great.

 

[DCrake]

You need to group by product code by reason and count the reason.

 

[Brianwarnock]

Group by Product code and complaint reason Count(*) as expression

 

[KevinSlater]

Hi i tried doing what you suggested but now when i run the query no results are displayed. see attached screenshot of the query design view.

Is this correct?

 

[Brianwarnock]

No Count(*) is in the field row with Expression in the Totals row

Yourchosenname: Count(*)

Note that you are going to count the number broken down to all the fields you have Group by such as Date

Brian

 

[KevinSlater]

Do you mean like this (please see attached) it come up with a message when i run the query

 

[Brianwarnock]

NO I said

No Count(*) is in the field row with Expression in the Totals row

Yourchosenname: Count(*)

Note that you are going to count the number broken down to all the fields you have Group by such as Date

Brian

You still want to group on the reason field.

You could drag that into the query twice selecting Group By for one occurence and Count for the other.
Brian

 

[KevinSlater]

Ok sorry, think ive got the design of the query ok now as suggested however if i do that and run the query, the results just show the number 1 on every row for the count column. See new attached screenshot

 

[Brianwarnock]

That's because of your grouping as I indicated earlier, CC_Ref is presumably unique, maybe the date is also. Your first post did not include those fields, why should your query include them for the counts you require?

Brian

 

[KevinSlater]

I dont need CC_REF sorry ive now removed this from the query" CC_ENTRY_DATE" is needed because as i want to display the date, and possible at some point allow the user to enter a partcode then a date range.

 

[KevinSlater]

I think its ok now thanks, although it wont allow me to specify entering a date as well as a product code, as it says the query is to complex when running

 

[Brianwarnock]

post your SQL and I'll try to see if I can spot something.

Brian

 

[KevinSlater]

Ok, SQL code below, if possible the user can enter a date range so that for example: from 01/12/09 to 16/01/10 (DD/MM/YY format) can be entered, ive just put "Please enter a date" for now to test. if that works i guess the date range should work. i guess i should of been more clearer what i was looking to do to start of with.

Thanks for your help


SELECT [DATA-COMPLAINTS].CC_TYPE_REF, [DATA-COMPLAINTS].STK_PART_CODE, [EXT-STK_PARTS].STK_DESCRIPTION, Count(*) AS [Count], Count([DATA-COMPLAINTS].CC_ENTRY_DATE) AS CountOfCC_ENTRY_DATE
FROM ([DATA-TYPES] INNER JOIN [DATA-COMPLAINTS] ON [DATA-TYPES].CC_TYPE_REF = [DATA-COMPLAINTS].CC_TYPE_REF) INNER JOIN [EXT-STK_PARTS] ON [DATA-COMPLAINTS].STK_PART_CODE = [EXT-STK_PARTS].STK_PART_CODE
GROUP BY [DATA-COMPLAINTS].CC_TYPE_REF, [DATA-COMPLAINTS].STK_PART_CODE, [EXT-STK_PARTS].STK_DESCRIPTION
HAVING ((([DATA-COMPLAINTS].STK_PART_CODE)=[Please Enter Part Code]) AND ((Count([DATA-COMPLAINTS].CC_ENTRY_DATE))=[Please enter date range]))
ORDER BY Count([DATA-COMPLAINTS].CC_ENTRY_DATE) DESC;

 

[Brianwarnock]

I would try

SELECT [DATA-COMPLAINTS].CC_TYPE_REF, [DATA-COMPLAINTS].STK_PART_CODE, [EXT-STK_PARTS].STK_DESCRIPTION, Count(*) AS [FldCount] 
FROM ([DATA-TYPES] INNER JOIN [DATA-COMPLAINTS] ON [DATA-TYPES].CC_TYPE_REF = [DATA-COMPLAINTS].CC_TYPE_REF) INNER JOIN [EXT-STK_PARTS] ON [DATA-COMPLAINTS].STK_PART_CODE = [EXT-STK_PARTS].STK_PART_CODE
GROUP BY [DATA-COMPLAINTS].CC_TYPE_REF, [DATA-COMPLAINTS].STK_PART_CODE, [EXT-STK_PARTS].STK_DESCRIPTION
HAVING (([DATA-COMPLAINTS].STK_PART_CODE)=[Please Enter Part Code]) AND
 ([DATA-COMPLAINTS].CC_ENTRY_DATE Between [Please enter start Date] And [Enter end Date])
ORDER BY Count(*) DESC;

I can see no reason for having 2 counts and do not know why you are counting Dates rather than reasons.

Do not use Count as a field name as it is a reserved word for a function.

A Date range has to be entered as 2 dates as shown.

 

[LPurvis]

You might include two counts when you want a count of rows and a count of non-null values from the specified field.

 

[Brianwarnock]

Leigh
My "you" was aimed at this specific case, not a general statement. If I thought that he had Null desciptions etc I would not use Count(*), but maybe I take too much for granted in assuming data validation.

Brian

 

[KevinSlater]

Brian - ok I tried the SQL code you suggested but when running the query i get an aggregate function error message (please see attached screenshot) any ideas why this could be?

All records have an entry in the description field.

 

[Brianwarnock]

The sql in the message does not appear to be the same as that of the code that I wrote () brackets are missing.

Brian

 

[KevinSlater]

Ok, its working fine now, thank you all for you help on this
Brianwarnock: i added the missing brackets.

(sorry about liitle late reply, thought id already replied)