Counting the number of rows by month

  • Thread starter Thread starter SkyMgr
  • Start date Start date
S

SkyMgr

I have a report that lists the number of days past due for
each record that satisfies my query criteria. I would like
to total at the bottom of the report an aging count for
each 30 day period.

Days Overdue <30 30 - 59 60 - 89 >=90
Number Due 17 3 2 6

I have tried several different queries, but have not been
successful.

Thanks,
SkyMgr
 
SkyMgr said:
I have a report that lists the number of days past due for
each record that satisfies my query criteria. I would like
to total at the bottom of the report an aging count for
each 30 day period.

Days Overdue <30 30 - 59 60 - 89 >=90
Number Due 17 3 2 6

I have tried several different queries, but have not been
successful.

Thanks,
SkyMgr
Click to expand...

Making many assumptions:

CREATE TABLE RecordsOverdue
(RecordsOverdueID INTEGER
,OverdueDate DATE
,CONSTRAINT pk_RecordsOverdue PRIMARY KEY (RecordsOverdueID)
)

Sample Data
1, 11/01/2003
2, 12/01/2003
3, 12/05/2003
4, 01/01/2004
5, 01/05/2004
6, 02/01/2004
7, 02/29/2004
9, 03/09/2004


TRANSFORM COUNT(M1.OverdueDate)
SELECT "Number Due"
FROM RecordsOverdue AS M1
GROUP BY "Number Due"
PIVOT SWITCH((M1.OverdueDate) > (Date() - 29), "1: <30",
((M1.OverdueDate <= (Date() - 30)) AND (M1.OverdueDate >=
(Date() - 59))), "2: 30 - 59",
((M1.OverdueDate <= (Date() - 60)) AND (M1.OverdueDate >=
(Date() - 89))), "3: 60 - 89",
(M1.OverdueDate) <= (Date() - 90), "4: >=90")

I numbered the output columns to control the order in which they appear.


Sincerely,

Chris O.
 
Chris,
Thanks for the reply. However, I tried doing what you
suggested in a new DB and I wasn't sure of where to start
with your instructions (Transform Count). Is this in a
query or on a report?
Additional information:
In my original DB I used a query to obtain the # of days
overdue "DaysDue: Date()-T_Proposal![Due Date]" and got
ranges of -33 to 233 days. On the report, I created four
boxes at the footer and added a build expression "=Count
([period]<30)" and so on for each box. The result was 21,
which was incorrect. I tried adding an * before [Period]
and the count was 28, which is all of the rows from the
query. Please help.
Thanks,
SkyMgr
 
Do the math to get the Period (number of days) in your query. Then you can
hard code in expressions like:
=Sum(Abs([Period]<=30))
=Sum(Abs([Period]>30 AND [Period]<=60))
etc
--
Duane Hookom
MS Access MVP


SkyMgr said:
Chris,
Thanks for the reply. However, I tried doing what you
suggested in a new DB and I wasn't sure of where to start
with your instructions (Transform Count). Is this in a
query or on a report?
Additional information:
In my original DB I used a query to obtain the # of days
overdue "DaysDue: Date()-T_Proposal![Due Date]" and got
ranges of -33 to 233 days. On the report, I created four
boxes at the footer and added a build expression "=Count
([period]<30)" and so on for each box. The result was 21,
which was incorrect. I tried adding an * before [Period]
and the count was 28, which is all of the rows from the
query. Please help.
Thanks,
SkyMgr
-----Original Message-----



Making many assumptions:

CREATE TABLE RecordsOverdue
(RecordsOverdueID INTEGER
,OverdueDate DATE
,CONSTRAINT pk_RecordsOverdue PRIMARY KEY (RecordsOverdueID)
)

Sample Data
1, 11/01/2003
2, 12/01/2003
3, 12/05/2003
4, 01/01/2004
5, 01/05/2004
6, 02/01/2004
7, 02/29/2004
9, 03/09/2004


TRANSFORM COUNT(M1.OverdueDate)
SELECT "Number Due"
FROM RecordsOverdue AS M1
GROUP BY "Number Due"
PIVOT SWITCH((M1.OverdueDate) > (Date() - 29), "1: <30",
((M1.OverdueDate <= (Date() - 30)) AND (M1.OverdueDate >=
(Date() - 59))), "2: 30 - 59",
((M1.OverdueDate <= (Date() - 60)) AND (M1.OverdueDate >=
(Date() - 89))), "3: 60 - 89",
(M1.OverdueDate) <= (Date() - 90), "4:
=90")

I numbered the output columns to control the order in which they appear.


Sincerely,

Chris O.









.
Click to expand...
Click to expand...
 
Duane,
Thanks a bunch! That worked.

Skymgr
:)
-----Original Message-----
Do the math to get the Period (number of days) in your query. Then you can
hard code in expressions like:
=Sum(Abs([Period]<=30))
=Sum(Abs([Period]>30 AND [Period]<=60))
etc
--
Duane Hookom
MS Access MVP


Chris,
Thanks for the reply. However, I tried doing what you
suggested in a new DB and I wasn't sure of where to start
with your instructions (Transform Count). Is this in a
query or on a report?
Additional information:
In my original DB I used a query to obtain the # of days
overdue "DaysDue: Date()-T_Proposal![Due Date]" and got
ranges of -33 to 233 days. On the report, I created four
boxes at the footer and added a build expression "=Count
([period]<30)" and so on for each box. The result was 21,
which was incorrect. I tried adding an * before [Period]
and the count was 28, which is all of the rows from the
query. Please help.
Thanks,
SkyMgr
-----Original Message-----

I have a report that lists the number of days past
Click to expand...
due
for
each record that satisfies my query criteria. I would like
to total at the bottom of the report an aging count for
each 30 day period.

Days Overdue <30 30 - 59 60 - 89 >=90
Number Due 17 3 2 6

I have tried several different queries, but have not been
successful.

Thanks,
SkyMgr

Making many assumptions:

CREATE TABLE RecordsOverdue
(RecordsOverdueID INTEGER
,OverdueDate DATE
,CONSTRAINT pk_RecordsOverdue PRIMARY KEY (RecordsOverdueID)
)

Sample Data
1, 11/01/2003
2, 12/01/2003
3, 12/05/2003
4, 01/01/2004
5, 01/05/2004
6, 02/01/2004
7, 02/29/2004
9, 03/09/2004


TRANSFORM COUNT(M1.OverdueDate)
SELECT "Number Due"
FROM RecordsOverdue AS M1
GROUP BY "Number Due"
PIVOT SWITCH((M1.OverdueDate) > (Date() - 29), "1: <30",
((M1.OverdueDate <= (Date() - 30)) AND (M1.OverdueDate >=
(Date() - 59))), "2: 30 - 59",
((M1.OverdueDate <= (Date() - 60)) AND (M1.OverdueDate >=
(Date() - 89))), "3: 60 - 89",
(M1.OverdueDate) <= (Date() - 90), "4:
=90")

I numbered the output columns to control the order in which they appear.


Sincerely,

Chris O.









.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top