SQL Statement Help

[BellKev]

Good Day,

I want a query to look at another query. What I want to do is count the number of records with a given year attached to them an them display the number of records for those years. The output I'm looking for is below ...

2002 5
2003 10
2004 3

Can anyone help me with this?

 

[tracy1234]

You can get that by using this:

SELECT year, Count(year) AS CountOfyear
FROM your_table
GROUP BY year;

But I suspect this is not what you want since you say you want a query to look at a query.

If you post more details, you'll get a better answer.

-Tracy

 

[dhookom]

The "FROM " clause can be a query:

SELECT year, Count(year) AS CountOfyear
FROM your_query
GROUP BY year;

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Golom]

OR, if you're using Access XP, it can be another select statement as in
[tt]
SELECT [year], Count([year]) AS CountOfyear
FROM (Select [Year] From TheOtherTable Where [Year] > 1999)
GROUP BY [year]
[/tt]

BTW: "Year" shouldn't be used as a field name because it is the name of a function in VBA.

 

[BellKev]

Folks ... excellent I thank you for the multitude of help. I have what I required now ... many thanks.