summ year bsed agency and date

[sal21]

In the sheet attached is the strucure of my access table (in yellow).
I want a query to count how many year are for each agency. (result in green)

[HansV]

This is called a crosstab query.
- Create a query in design view based on your table.
- Add the Agency field.
- Add a calculated column

A: Year([YEAR])

- Add the YEAR field.
- Select Query | Crosstab Query.
- In the Totals row, select Count for the third column.
- In the Crosstab row, select Row for the first column, Column for the second column, and Value for the third column.

[sal21]

This is called a crosstab query.
- Create a query in design view based on your table.
- Add the Agency field.
- Add a calculated column

A: Year([YEAR])

- Add the YEAR field.
- Select Query | Crosstab Query.
- In the Totals row, select Count for the third column.
- In the Crosstab row, select Row for the first column, Column for the second column, and Value for the third column.

TABELLA = my table
TIPO_DATO is a filter
SPORT=Agency
DATA_ACC = date in format dd/mm/yyyy

SQL = "SELECT SPORT, DATA_ACC, COUNT(*) FROM " & TABELLA & " WHERE TIPO_DATO='" & TIPO_DAT & "' GROUP BY SPORT, DATA_ACC ORDER BY SPORT, DATA_ACC"

the query return a value and summ, but not is correct...
in effect the query return agency the day date and count...

[HansV]

You haven't created a crosstab query.

Please create the query in design view in Access and follow my instructions. Don't forget to make it a crosstab query!
When it works correctly, select View | SQL and look at the SQL generated by Access. You can use this in your SQL string.

[sal21]

ok... resolved with design view in Access and with a little modify!
tks.