help with query to count different date records in one field

[burnettec]

My database contains a field called "begin date", data type is number, format is yyyymmdd. There are approximately 280,000 records for this field and I want to create a query to count the total number of dates that start with (for example) 2004, 2005, 2006,...2013. The queries I created aren't working for me and I don't know why. This is how I set one of them up: drag "begin date" to query design view column, create a totals row in the query and set it to "count", enter criteria [Like "2004*"], repeat this design within the query for each year I'm interested in counting. When I run the query, no data is returned. Clearly I need more experience with queries! Thanks in advance.

[alansidman]

In a new field, type: Year: Left([begin date],4).

In a new query, using the query you just created with the new field as the record source. Put the Year field in the query grid twice. click on the Sigma in the ribbon. Change the second Group by to Count. Run your query.

[burnettec]

Hi, and thanks for your reply. I'm working on your solution and think I know where you're going with it based on what I did in Excel. Will get back with results asap.

[alansidman]

In a query. It is not a good RDBMS policy to do any calculations in tables.

See attached

[burnettec]

Good advice about not calculating in tables. I have tested your queries and they work great. Thanks very much for your help. Access seems to be a bit more restrictive in its methods than Excel, but very useful for big data sets. Much appreciated.