Count in Query

[Mabena]

First time user, inexperienced access user
I have a table with some of the following fields: date, machine id (as Op Responsible), crank number.

I want to be able to count reject per machine per day. The count will be for crank numbers which is text format.

i tried a query that gave me error that I did not incluse date as part of aggregate function in the transform statement. I hav no clue how to use aggragate or transform statements. Here is that query:
SELECT [T DEFECT DATA].Date, [T DEFECT DATA].[Op Responsible], Count([Crank Number]) AS Total
FROM [T DEFECT DATA]
ORDER BY [T DEFECT DATA].Date;

 

[DJN]

Try this

SELECT [T DEFECT DATA].Date, [T DEFECT DATA].[Op Responsible], Count([T DEFECT DATA].CrankNumber) AS CountOfCrankNumber
FROM [T DEFECT DATA]
GROUP BY [T DEFECT DATA].Date, [T DEFECT DATA].[Op Responsible]
ORDER BY [T DEFECT DATA].Date;

You need to click on the Sum button on the toolbar. This will add 'Group By' to each field. Change CrankNumber to Count in the Total Field, or copy and paste this into the SQL view.

David

 

[RV]

Two advices:

1)Don't use Date as a column name for it's a reserved word in Access.
2) Don't use spaces in column names.

RV

 

[Mabena]

Thank RV, I closed spaces, changed date to Dated (still date/time), Op Responsible to OpResponsible and Crank Number to CrankNumber.

David, The first time I did this query I did it your way, the problem is that it counts by showing value 1, even if the date (now dated) and Op responsible (now OpResponsible) fields have to repeat until all CrankNumber where Dated and OpResponsible have values. Ideally I want to see one Dated shown for each OpResponsible shown with total corresponding CrankNumber count.

I also tried making a second query based on the first one where I used SumCountOfCrankNumber think it would sum all the counts for that particular day and OpResponsible respectively. It still shows 1's as in the first query.

 

[mrxdotcom]

this may help...the best way around this problem is to write a query to count what ever you wish to count i.e.

SELECT Count([T DEFECT DATA].CrankNumber) AS CountOfCrankNumber
FROM [T DEFECT DATA]


and then create another query to combine this query with the new query.. ie... subquery....email me if you need any more help