Ranking records based on month

  • Thread starter Thread starter NeoFax
  • Start date Start date
N

NeoFax

I am having a problem ranking my data so that the rank resets when the
month changes. Here is what I would like to have:

Helo MonthOf SeqNo
14728 1 1
14729 1 2
14730 1 3
14731 2 1
14732 2 2
14733 2 3
14734 3 1
....

Here is the SQL statement, but it just ranks the info wrong:

SELECT tblPDPDates.HELO, (SELECT COUNT(*)
FROM tblPDPDates
WHERE [REC_START]=tblPDPDates.[REC_START])+1 AS seq_nbr,
Month([REC_START]) AS MonthOf
FROM tblPDPDates
WHERE (((tblPDPDates.STATION)="4fl") AND ((tblPDPDates.REC_START)
Between #1/1/2009# And #12/31/2009#))
GROUP BY tblPDPDates.HELO, Month([REC_START])
HAVING (((Month([REC_START])) Is Not Null))
ORDER BY tblPDPDates.HELO;

Any help would be appreciated. Thanks!
 
Try this --
SELECT Month(Q.[REC_START]) AS MonthOf, Q.HELO, (SELECT COUNT(*) FROM
tblPDPDates AS Q1
WHERE Month(Q1.[REC_START])=Month(Q.[REC_START])
AND Q1.HELO < Q.HELO)+1 AS seq_nbr
FROM tblPDPDates AS Q
WHERE (((Q.STATION)="4fl") AND ((Q.REC_START) Between #1/1/2009# And
#12/31/2009#))
GROUP BY Month(Q.[REC_START]), Q.HELO
ORDER BY Month(Q.[REC_START]), Q.HELO;
 
Close, But here is the results:

MonthOf HELO seq_nbr
1 14728 1
1 14729 2
1 14730 4
2 14731 1
2 14732 2
2 14733 4
3 14734 1
3 14735 3
3 14736 5
4 14737 1
4 14738 3
4 14739 5
5 14740 1
5 14741 2
5 14742 5
6 14743 1
6 14744 2
6 14745 3
6 14746 6
7 14747 1
7 14748 2
7 14749 3
7 14750 6
8 14751 1
8 14752 2
8 14753 3
8 14754 5
8 14755 8
9 14756 1
9 14757 2
9 14758 3
9 14759 6
10 14760 3
10 14761 4
10 14762 5
11 14763 10
11 14764 11
11 14765 13
 
Close, But here is the results:

MonthOf HELO    seq_nbr
1       14728   1
1       14729   2
1       14730   4
2       14731   1
2       14732   2
2       14733   4
3       14734   1
3       14735   3
3       14736   5
4       14737   1
4       14738   3
4       14739   5
5       14740   1
5       14741   2
5       14742   5
6       14743   1
6       14744   2
6       14745   3
6       14746   6
7       14747   1
7       14748   2
7       14749   3
7       14750   6
8       14751   1
8       14752   2
8       14753   3
8       14754   5
8       14755   8
9       14756   1
9       14757   2
9       14758   3
9       14759   6
10      14760   3
10      14761   4
10      14762   5
11      14763   10
11      14764   11
11      14765   13
Click to expand...

Fixed by creating a query that pre-limits the data. Here is the SQL:

SELECT Month(Q.[REC_START]) AS MonthOf, Q.HELO AS Expr1, (SELECT COUNT
(*) FROM
qryPDPMonths AS Q1
WHERE Month(Q1.[REC_START])=Month(Q.[REC_START])
AND Q1.HELO < Q.HELO)+1 AS seq_nbr
FROM qryPDPMonths AS Q
GROUP BY Month(Q.[REC_START]), Q.HELO
ORDER BY Month(Q.[REC_START]), Q.HELO;
 
Close, But here is the results:
Click to expand...
MonthOf HELO    seq_nbr
1       14728   1
1       14729   2
1       14730   4
2       14731   1
2       14732   2
2       14733   4
3       14734   1
3       14735   3
3       14736   5
4       14737   1
4       14738   3
4       14739   5
5       14740   1
5       14741   2
5       14742   5
6       14743   1
6       14744   2
6       14745   3
6       14746   6
7       14747   1
7       14748   2
7       14749   3
7       14750   6
8       14751   1
8       14752   2
8       14753   3
8       14754   5
8       14755   8
9       14756   1
9       14757   2
9       14758   3
9       14759   6
10      14760   3
10      14761   4
10      14762   5
11      14763   10
11      14764   11
11      14765   13
Click to expand...

Fixed by creating a query that pre-limits the data.  Here is the SQL:

SELECT Month(Q.[REC_START]) AS MonthOf, Q.HELO AS Expr1, (SELECT COUNT
(*)   FROM
qryPDPMonths AS Q1
          WHERE   Month(Q1.[REC_START])=Month(Q.[REC_START])
                        AND Q1.HELO < Q.HELO)+1 AS seq_nbr
FROM qryPDPMonths AS Q
GROUP BY Month(Q.[REC_START]), Q.HELO
ORDER BY Month(Q.[REC_START]), Q.HELO;- Hide quoted text -

- Show quoted text -
Click to expand...

This works running as a query, but adding to a report I get an error
stating that a multi-level group by is not allowed in a sub-query.
 
If you have a primary key then try this --
Try this --
SELECT Month(Q.[REC_START]) AS MonthOf, Q.HELO, (SELECT COUNT(*) FROM
tblPDPDates AS Q1
WHERE Month(Q1.[REC_START])=Month(Q.[REC_START])
AND Q1.HELO & Q1.[PrimaryKey] < Q.HELO &
Q.[PrimaryKey])+1 AS seq_nbr
FROM tblPDPDates AS Q
WHERE (((Q.STATION)="4fl") AND ((Q.REC_START) Between #1/1/2009# And
#12/31/2009#))
GROUP BY Month(Q.[REC_START]), Q.HELO
ORDER BY Month(Q.[REC_START]), Q.HELO;
 
Back
Top