Count unique records

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

In my table there are fields for startmonth, startday, starthour,
startminute, startsecond, along with several other fields that do not pertain
to this thread.

In a report I have grouped by startday and starthour, and that much is
working.

At the end of report I need to count the total number of days reported in
this query(for example, in December there are 31 days but due to the Holidays
there were records reported for only 21 of those days. I know there are 21
because I ran the report and then tallied each separate day manually.

I tried Count([startday]) but it returned the total # of records. Any other
ideas?
 
Add a group to the report and group on the month
Add a control to the group
Name it "CountMonths"
Set its source to =1
Set it to count OVER ALL

Now in the report footer
-- add a new control
-- set its source to =CountMonths

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Where do I add a group to the report? I have selected 'sorting and grouping'
from the 'view' menu, but do not see where I can add a control to it or set
source =1.


John Spencer said:
Add a group to the report and group on the month
Add a control to the group
Name it "CountMonths"
Set its source to =1
Set it to count OVER ALL

Now in the report footer
-- add a new control
-- set its source to =CountMonths

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Robert said:
In my table there are fields for startmonth, startday, starthour,
startminute, startsecond, along with several other fields that do not
pertain
to this thread.

In a report I have grouped by startday and starthour, and that much is
working.

At the end of report I need to count the total number of days reported in
this query(for example, in December there are 31 days but due to the
Holidays
there were records reported for only 21 of those days. I know there are 21
because I ran the report and then tallied each separate day manually.

I tried Count([startday]) but it returned the total # of records. Any
other
ideas?
Click to expand...
Click to expand...
 
Also, the query has already selected all records from the table with the same
month, so the "startmonth" field will be identical for every record.


John Spencer said:
Add a group to the report and group on the month
Add a control to the group
Name it "CountMonths"
Set its source to =1
Set it to count OVER ALL

Now in the report footer
-- add a new control
-- set its source to =CountMonths

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Robert said:
In my table there are fields for startmonth, startday, starthour,
startminute, startsecond, along with several other fields that do not
pertain
to this thread.

In a report I have grouped by startday and starthour, and that much is
working.

At the end of report I need to count the total number of days reported in
this query(for example, in December there are 31 days but due to the
Holidays
there were records reported for only 21 of those days. I know there are 21
because I ran the report and then tallied each separate day manually.

I tried Count([startday]) but it returned the total # of records. Any
other
ideas?
Click to expand...
Click to expand...
 
One last tidbit that may or may not help. The query currently returns a total
of 1953 records. Some days have no records and therefore are not in the
query, other days have as many as 40-50 records.

John Spencer said:
Add a group to the report and group on the month
Add a control to the group
Name it "CountMonths"
Set its source to =1
Set it to count OVER ALL

Now in the report footer
-- add a new control
-- set its source to =CountMonths

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Robert said:
In my table there are fields for startmonth, startday, starthour,
startminute, startsecond, along with several other fields that do not
pertain
to this thread.

In a report I have grouped by startday and starthour, and that much is
working.

At the end of report I need to count the total number of days reported in
this query(for example, in December there are 31 days but due to the
Holidays
there were records reported for only 21 of those days. I know there are 21
because I ran the report and then tallied each separate day manually.

I tried Count([startday]) but it returned the total # of records. Any
other
ideas?
Click to expand...
Click to expand...
 
Add the group in the Sorting and Grouping Dialog
Use your date field as the source for the grouping
-- Field Expression: YourDateField
-- Group Header: Yes
-- Group On: Month
Close the dialog

Add a CONTROL (Textbox) to the Group Header
Name it "CountMonths"
-- Control Source: =1
-- Running Sum: OVER ALL

Click on the Header and set its Visible property to false
Add a control (textbox) to the Report Footer
-- Control Source: =[Count Months]

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Where do I add a group to the report? I have selected 'sorting and grouping'
from the 'view' menu, but do not see where I can add a control to it or set
source =1.


John Spencer said:
Add a group to the report and group on the month
Add a control to the group
Name it "CountMonths"
Set its source to =1
Set it to count OVER ALL

Now in the report footer
-- add a new control
-- set its source to =CountMonths

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Robert said:
In my table there are fields for startmonth, startday, starthour,
startminute, startsecond, along with several other fields that do not
pertain
to this thread.

In a report I have grouped by startday and starthour, and that much is
working.

At the end of report I need to count the total number of days reported in
this query(for example, in December there are 31 days but due to the
Holidays
there were records reported for only 21 of those days. I know there are 21
because I ran the report and then tallied each separate day manually.

I tried Count([startday]) but it returned the total # of records. Any
other
ideas?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top