adding a count to the result of a query depending on the month raised

[monplankton]

Hi, I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?

I'm using access 2003.

Thanks

:banghead:

 

[CJ_London]

providing your query has a unique id it can be done with a query along the following lines (replace all field and tablenames with those you have):

SELECT year(flddate), month(flddate), (SELECT count(*) FROM myTable as tmp WHERE month(flddate)=month(myTable.flddate) and year(flddate)=year(myTable.flddate) and uniqueID<=myTable.uniqueID) AS Counter 
FROM myTable

 

[monplankton]

Thanks CJ but I'm not familar with SQL and usually create queries in design view.
I've changed my query to SQA and it looks like this

SELECT qry_COQ_PIR_InspectionStatusReport.Month, qry_COQ_PIR_InspectionStatusReport.Yr
FROM qry_COQ_PIR_InspectionStatusReport
WITH OWNERACCESS OPTION;

Can you advise on how to proceed?

 

[monplankton]

Hi CJ It's OK.
I've figured it out by reverse engineering it. Many thanks works a treat.

 

[CJ_London]

Glad to help - just click on the Thanks icon if you are happy