Count Values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello:

I have a report based on a query which calculates date difference and shows
it as count of days as follows:

txtDateDiff: DateDiff("d",[ReceivedDate],[DateCompleted])

I would like to print a summary somewhere at the end of the detail report
which whould show me how many records I have with:

1 days eg. 10
2 days eg. 4
3 days eg. 5
4 days eg 4
count records with more then 4 days eg. 50
Click to expand...

Your help would be appreciated.

Thanks.
 
"somewhere at the end of the detail report" suggest that you create a totals
query that groups by your datediff function with an expression like:
DayBuckets:
IIf(DateDiff("d",[ReceivedDate],[DateCompleted])>4,100,DateDiff("d",[ReceivedDate],[DateCompleted]))
Count the number of records.

Use this query as the record source for a subreport which you can plop into
the Report Footer section of the main report.
You can modify the DayBuckets to change the 100 to "greater than 4".
--
Duane Hookom
MS Access MVP

danka said:
Hello:

I have a report based on a query which calculates date difference and
shows
it as count of days as follows:

txtDateDiff: DateDiff("d",[ReceivedDate],[DateCompleted])

I would like to print a summary somewhere at the end of the detail report
which whould show me how many records I have with:

1 days eg. 10
2 days eg. 4
3 days eg. 5
4 days eg 4
count records with more then 4 days eg. 50
Click to expand...

Your help would be appreciated.

Thanks.
Click to expand...
 
danka said:
I have a report based on a query which calculates date difference and shows
it as count of days as follows:

txtDateDiff: DateDiff("d",[ReceivedDate],[DateCompleted])

I would like to print a summary somewhere at the end of the detail report
which whould show me how many records I have with:

1 days eg. 10
2 days eg. 4
3 days eg. 5
4 days eg 4
count records with more then 4 days eg. 50
Click to expand...
Click to expand...


You should create a simple report base on a query like:

SELECT Partition(DateDiff("d", [ReceivedDate],
[DateCompleted]), 1,4,1) As Days,
Count(*) As CountOfDays
FROM the table
GROUP BY Partition(DateDiff("d", [ReceivedDate],
[DateCompleted]), 1,4,1)
 
Great reply Marsh.
--
Duane Hookom
MS Access MVP

Marshall Barton said:
danka said:
I have a report based on a query which calculates date difference and
shows
it as count of days as follows:

txtDateDiff: DateDiff("d",[ReceivedDate],[DateCompleted])

I would like to print a summary somewhere at the end of the detail report
which whould show me how many records I have with:

1 days eg. 10
2 days eg. 4
3 days eg. 5
4 days eg 4
count records with more then 4 days eg. 50
Click to expand...
Click to expand...


You should create a simple report base on a query like:

SELECT Partition(DateDiff("d", [ReceivedDate],
[DateCompleted]), 1,4,1) As Days,
Count(*) As CountOfDays
FROM the table
GROUP BY Partition(DateDiff("d", [ReceivedDate],
[DateCompleted]), 1,4,1)
Click to expand...
 
Duane said:
Great reply Marsh.
Click to expand...


Glad you like it, Duane, but I just noticed that I neglected
to say that the new report would then be used as a subreport
in the report footer section.
 
Hello:

The partition function in a query works very well. Thank you very much.

However, when I create a subreport in the footer based on the propsed by you
query I get the result based on all the records in the table. My main report
is based on the query with the selection criteria which selects records for
the chosen by the user date range, eg.

Between [forms]![frmReportsOperating]![cboStartDate] And
[forms]![frmReportsOperating]![cboEndDate]

Subreport needs to be based on the same set of records. Since subreport is
not linked to main report I am not sure how to limit records for the selected
dates in q query. Can you help?

Thank.
 
Hello:

One more question. My query result is as follows:
Expr1 CountOfDays IncStatus
4 3
:0 27 3
1:1 36 3
2:2 15 3
3:3 10 3
4:4 13 3
5:5 5 3
6:6 5 3
7: 41 3

The numbers are accureate. I applied some filter to get the dates. How can
I make it more readable. For example instead 7: I would like to put a label
7 or more or greater then 7?

Thanks.
--
danka


danka said:
Hello:

The partition function in a query works very well. Thank you very much.

However, when I create a subreport in the footer based on the propsed by you
query I get the result based on all the records in the table. My main report
is based on the query with the selection criteria which selects records for
the chosen by the user date range, eg.

Between [forms]![frmReportsOperating]![cboStartDate] And
[forms]![frmReportsOperating]![cboEndDate]

Subreport needs to be based on the same set of records. Since subreport is
not linked to main report I am not sure how to limit records for the selected
dates in q query. Can you help?

Thank.
--
danka


Marshall Barton said:
Glad you like it, Duane, but I just noticed that I neglected
to say that the new report would then be used as a subreport
in the report footer section.
Click to expand...
Click to expand...
 
danka said:
The partition function in a query works very well. Thank you very much.

However, when I create a subreport in the footer based on the propsed by you
query I get the result based on all the records in the table. My main report
is based on the query with the selection criteria which selects records for
the chosen by the user date range, eg.

Between [forms]![frmReportsOperating]![cboStartDate] And
[forms]![frmReportsOperating]![cboEndDate]

Subreport needs to be based on the same set of records. Since subreport is
not linked to main report I am not sure how to limit records for the selected
dates in q query.
Click to expand...


Try changing the subreport's query to use the main report's
query instead of the table.
 
danka said:
Hello:

One more question. My query result is as follows:
Expr1 CountOfDays IncStatus
4 3
:0 27 3
1:1 36 3
2:2 15 3
3:3 10 3
4:4 13 3
5:5 5 3
6:6 5 3
7: 41 3

The numbers are accureate. I applied some filter to get the dates. How can
I make it more readable. For example instead 7: I would like to put a label
7 or more or greater then 7?
Click to expand...


You can use an expression along these lines in the Days text
box:

=IIf(Left(Days, 1) = ":", "<=" & Mid(Days, 2),
IIf(Right(Days, 1) = ":", ">=", "") & Val(Days))

Make certain that the text box is named something other than
the field name (e.g. txtDays).
 
Thanks Marshall:

Finally, I am getting the right numbers. I followed your suggestion and
based the query for the subreport on the main report query.

I need still to figure out the IIF statement that you have provided. I am
not sure how this is supposed to work.

Thank you very much.
--
danka


Marshall Barton said:
danka said:
The partition function in a query works very well. Thank you very much.

However, when I create a subreport in the footer based on the propsed by you
query I get the result based on all the records in the table. My main report
is based on the query with the selection criteria which selects records for
the chosen by the user date range, eg.

Between [forms]![frmReportsOperating]![cboStartDate] And
[forms]![frmReportsOperating]![cboEndDate]

Subreport needs to be based on the same set of records. Since subreport is
not linked to main report I am not sure how to limit records for the selected
dates in q query.
Click to expand...


Try changing the subreport's query to use the main report's
query instead of the table.
Click to expand...
 
danka said:
Finally, I am getting the right numbers. I followed your suggestion and
based the query for the subreport on the main report query.

I need still to figure out the IIF statement that you have provided. I am
not sure how this is supposed to work.
Click to expand...


Not sure what you are seeing, but that was supposed to be
all on one line.

It is a text box expression for you to use in the text box
that displayed the :0, 1:1, etc. If you have not assigned a
more meaningful name to the calculated field in the query
and it's still Expr1, the change the name of the field from
Days to Expr1.

The idea there is to look for the : and figure out a more
user friendly text to display. The example I posted should
display:
<=0
1
2
...
=7
Click to expand...

You can change the <= or >= to whatever you like.
 
Hello Marshall:

Can you still help me to sort his out?

:0
1:1
2:2
3:3
4:

This statement gives me the following result:

=IIf(Left([Expr1],1)=":","<=" &
Mid([Expr1],2),IIf(Right([Expr1],1)=":",">=","") & Val([Expr1]))

0
1
2
3
4

I would like to say somewhere "greater then 4" in the last range.

This expression gives me this:

=IIf(Left([expr1],1)>3,"greater then ",IIf(Right([Expr1],1)=":",">=","")) &
Val([Expr1])

# Error
1
2
3
greater then 4

This one is almost right, except that it cannot evaluate a space in :0.

I probably don't understand fully what is supposed to happen in your
expression.
 
Bleep! I missed the space in the boundary cases.
Try this:

=IIf(Left([Expr1],2)=" :","<=" & Mid([Expr1],3),
IIf(Right([Expr1],2)=": ",">=","") & Val([Expr1]))
 
Hello:

Thank you very much. Everything works as expected I can finish my report.
Thanks for detailed explanation - it helped me to understand what a display
was supposed to look like.

I really appreciate your assistance.
 
Back
Top