Reset Rank column in MS Access query every year

[Alhakeem1977]

I have got a query extracts the business days from a table for the whole year as the below:

Rank DDate

246 26-Dec-19
247 29-Dec-19
248 30-Dec-19
249 02-Jan-20
250 05-Jan-20
251 06-Jan-20

My query syntax as the below:

SELECT DCount("[DDate]","Sub","[DDate]<=#" & [DDate] & "#") AS Rank, Sub.DDate
FROM Sub;

,,,,,,,,,,,,,,,,,,,,,,,,

I want to reset the [Rank] column by year from the above query like the below:

Rank DDate

246 26-Dec-19
247 29-Dec-19
248 30-Dec-19
1 02-Jan-20
2 05-Jan-20
3 06-Jan-20

,,,,,,,,,,,,

How can I achieve to solve this issue?


Your earliest response would be highly appreciated.

Thanks in advance.

 

[Ranman256]

you can do it in a report.
in the query make a constant field: Rank= 1
in the report, add the GROUP band for DDATE
add Rank & Date on the report detail: RANK , DDATE
select the RANK field, show properties
set RUNNING SUM = OVER GROUP.

then the rank should start at 1 each Date group and increment.

 

[Alhakeem1977]

you can do it in a report.
in the query make a constant field: Rank= 1
in the report, add the GROUP band for DDATE
add Rank & Date on the report detail: RANK , DDATE
select the RANK field, show properties
set RUNNING SUM = OVER GROUP.

then the rank should start at 1 each Date group and increment.

Thanks for your earliest response, the Rank must be within a query because I need it for a concatenated field to generate a barcode font.

I will use the steps you provided in a summary report thanks again for that.

Awaiting your earliest response or anybody can help.

 

[isladogs]

Have a look at the method described here using the Serialize function:
http://www.mendipdatasystems.co.uk/rank-order-queries/4594424063

Or try the subquery approach in the link from there to Allen Browne's website

 

[Alhakeem1977]

Sorry, I tried but I couldn't achieve the below query to reset count by year:

SELECT DCount("[DDate]","Sub","[DDate]<=#" & [DDate] & "#") AS Rank, Sub.DDate
FROM Sub;

Rank DDate

246 26-Dec-19
247 29-Dec-19
248 30-Dec-19
1 02-Jan-20
2 05-Jan-20
3 06-Jan-20

 

[isladogs]

All you appear to have done is reposted what you had in post #1.
Did you look at the two links I suggested?

 

[Alhakeem1977]

All you appear to have done is reposted what you had in post #1.
Did you look at the two links I suggested?

Thanks a lot for the earliest response, you're so active.

Yes, I did but I couldn't be able to implement it, because of my limited knowledge.
My problem is that how to amend the query syntax to return reset of counting every year?

SELECT DCount("[DDate]","Sub","[DDate]<=#" & [DDate] & "#") AS Rank, Sub.DDate
FROM Sub;

I change it to the below but it didn' work :banghead:

SELECT Year(DCount("[DDate]","Sub","[DDate]<=#" & [DDate] & "#") AS Rank), Sub.DDate
FROM Sub;

Thanks in advance.

 

[plog]

You need to add in a year criterion in your DCount, not inside it:

SELECT DCount("DDate","MyTable","DDate<=#" & [DDate] & "# AND Year(DDate)=" & Year([DDate])) AS Rank, MyTable.DDate
FROM MyTable;

I also advise running this on a datasource as close to the table as possible. I don't know what your 'sub' query is doing, but if you have a table with the unique dates use that instead.

Further, depending on how you are going to use this down the line, you might do an UPDATE to your table so that you store this information rather than requery it everytime you need it. The DCount is going to run very slowly especially the larger the dataset it is using in the FROM.

 

[Alhakeem1977]

You need to add in a year criterion in your DCount, not inside it:

SELECT DCount("DDate","MyTable","DDate<=#" & [DDate] & "# AND Year(DDate)=" & Year([DDate])) AS Rank, MyTable.DDate
FROM MyTable;

I also advise running this on a datasource as close to the table as possible. I don't know what your 'sub' query is doing, but if you have a table with the unique dates use that instead.

Further, depending on how you are going to use this down the line, you might do an UPDATE to your table so that you store this information rather than requery it everytime you need it. The DCount is going to run very slowly especially the larger the dataset it is using in the FROM.

works perfectly

Actually, I extracted the [Sub] from a table which has the full business days for three departments each has its own business day.

And I will use this query once a year to generate barcode labels.

Thanks a lot for your help and all of you guys