Reset Rank column in MS Access query every year (1 Viewer)

Alhakeem1977 · Mar 22, 2019

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:

Code:

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 · Mar 22, 2019

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 · Mar 22, 2019

Ranman256 said:
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 · Mar 22, 2019

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 · Mar 22, 2019

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

Code:

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 · Mar 22, 2019

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

Alhakeem1977 · Mar 22, 2019

isladogs said:
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?

Code:

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

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

Code:

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

Thanks in advance.

plog · Mar 22, 2019

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

Code:

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 · Mar 22, 2019

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

Code:

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

Reset Rank column in MS Access query every year (1 Viewer)

Alhakeem1977

Registered User.

Ranman256

Well-known member

Alhakeem1977

Registered User.

isladogs

MVP / VIP

Alhakeem1977

Registered User.

isladogs

MVP / VIP

Alhakeem1977

Registered User.

plog

Banishment Pending

Alhakeem1977

Registered User.

Similar threads

Users who are viewing this thread