Counting the number of rows by month

[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

 

[Chris2]

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

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.

 

[SkyMgr]

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

 

[Duane Hookom]

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-----



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.









.

 

[Skymgr]

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

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.









.

.