Counting records 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 as follows.... (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 only where appointment date=created date
3 count of records only where appointment date<>created date

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

Any advice appreciated


TIA

Bob

 

[Conan Kelly]

Bob,

You might be able to accomplish this by creating a query
based on this table. Add as many fields as you want (you
should be able to add the same field more than once). Set
criteria for each field (using your examples: in the
APPOINTMENT DATE field, set its criteria to "= [created
date]"). Then you want to add the TOTALS line to the
query design view. To do this, right-click any where in
the design grid and click TOTALS from the shortcut menu
(or click the TOTALS button on the tool bar--"Σ"). Now
out of the TOTALS row, you can select COUNT for each field
you want to count. You might experment with this though:
by default, GROUP BY is the setting for each field in the
TOTALS row. This might cause the results of your query to
be grouped by all the different possibilities of each
field that is in your query

I hope this helps,

Conan Kelly

-----Original Message-----

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 as follows.... (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 only where appointment date=created date

 

[Van T. Dinh]

Check Access VB Help on the DCount() function.

HTH
Van T. Dinh
MVP (Access)