Count question

[Someone]

Hi all,

Can someone please help with a 'counting' question?

I have a table where each record has several dates related to it
(I'll use 3 dates for my example but there are several more)

created date
appointment date
closed date

I want to summarise the data like this....(Selection of data is by a parameter
on date range at run-time)

result to be....

1 count of total records (based on created date) ie grand total records in
report
2 count of records where appointment date=created date
3 count of records where appointment date<>created date

I can do the grand total, and could calculate % etc if I could get totals for 2
& 3 but presently I can't see how to get the other sub totals.

Any advice appreciated


TIA

Bob

 

[Steve Schapel]

Bob,

You mentioned a Report. You forgot to mention how you are doing the
"grand total", but this kind of stuff would be be easier directly in
your report, rather than in queries. For your 3 desired results, put
3 unbound textboxes in the footer of the report, and set their Control
Source properties to...
=Count(*)
=Abs(Sum([appointment date]=[created date]))
=Abs(Sum([appointment date]<>[created date]))

- Steve Schapel, Microsoft Access MVP

 

[Gary Walter]

Hi all,

Can someone please help with a 'counting' question?

I have a table where each record has several dates related to it
(I'll use 3 dates for my example but there are several more)

created date
appointment date
closed date

I want to summarise the data like this....(Selection of data is by a parameter
on date range at run-time)

result to be....

1 count of total records (based on created date) ie grand total records in
report
2 count of records where appointment date=created date
3 count of records where appointment date<>created date

I can do the grand total, and could calculate % etc if I could get totals for 2
& 3 but presently I can't see how to get the other sub totals.

Hi Bob,

You just need to use John Vinson's
"sneaky trick" of "summing identities."

Count1: Count("*")
Count2: -Sum([appointment date] = [created date])
Count3: -Sum([appointment date] <> [created date])

In your report, you could (I believe) just create 3 textboxes
(you can set their Visible property to false)
in the detail section:

txtCount1
Control Source: =1
Running Sum: Over All

txtCount2
Control Source: = -([appointment date] = [created date])
Running Sum: Over All

txtCount3
Control Source: = -([appointment date] <> [created date])
Running Sum: Over All

In your report footer, just reference these textboxes
for your summary.

Please provide your query and table structure
if this does not work for you.

Good luck,

Gary Walter