Help Counting Unique Dates

[Dani]

I have a report that groups by Employee Name and then breaksdown each day
that employee has worked. However, sometimes the employee will have
duplicate dates listed (from coming in and out of work). Is there a way that
I can count each unique date once, rather than counting each record?
Currently it counts each line. Please help!!
Thank you!

 

[Allen Browne]

There are a couple of ways to do this.

The first is to create a query that deduplicates the records.
In query design view, click the Total button on the toolbar.
Then Group by the WorkDate field.
If the date/time field contains both a date and time, enter this expression
in the Field row:
DateValue([WorkDate])
and group on that.

The other alternative is to do it in the report.
Create a Group Header on the WorkDate field.
In the lower pane of the Sorting And Grouping box for this field, set the
properties:
Group On Day
In this group header, add a text box with these properties:
Control Source: =1
Running Sum: Over Group
Format General Number
Visible No
Name txtDateCountRS
Now in the EmployeeID Group Footer section, add a text box with Control
Source of:
=[txtDateCountRS]

(I am assuming you have a group footer for the employee above the group
footer for the date.)

 

[Dani]

You are awesome!! Thank you so much!

There are a couple of ways to do this.

The first is to create a query that deduplicates the records.
In query design view, click the Total button on the toolbar.
Then Group by the WorkDate field.
If the date/time field contains both a date and time, enter this expression
in the Field row:
DateValue([WorkDate])
and group on that.

The other alternative is to do it in the report.
Create a Group Header on the WorkDate field.
In the lower pane of the Sorting And Grouping box for this field, set the
properties:
Group On Day
In this group header, add a text box with these properties:
Control Source: =1
Running Sum: Over Group
Format General Number
Visible No
Name txtDateCountRS
Now in the EmployeeID Group Footer section, add a text box with Control
Source of:
=[txtDateCountRS]

(I am assuming you have a group footer for the employee above the group
footer for the date.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a report that groups by Employee Name and then breaksdown each day
that employee has worked. However, sometimes the employee will have
duplicate dates listed (from coming in and out of work). Is there a way
that
I can count each unique date once, rather than counting each record?
Currently it counts each line. Please help!!
Thank you!

 

[Dani]

Allen,
One more question: Now I can't seem to get a Report Total for all of the
"days worked" for all of the employees. Any suggestions??

You are awesome!! Thank you so much!

There are a couple of ways to do this.

The first is to create a query that deduplicates the records.
In query design view, click the Total button on the toolbar.
Then Group by the WorkDate field.
If the date/time field contains both a date and time, enter this expression
in the Field row:
DateValue([WorkDate])
and group on that.

The other alternative is to do it in the report.
Create a Group Header on the WorkDate field.
In the lower pane of the Sorting And Grouping box for this field, set the
properties:
Group On Day
In this group header, add a text box with these properties:
Control Source: =1
Running Sum: Over Group
Format General Number
Visible No
Name txtDateCountRS
Now in the EmployeeID Group Footer section, add a text box with Control
Source of:
=[txtDateCountRS]

(I am assuming you have a group footer for the employee above the group
footer for the date.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a report that groups by Employee Name and then breaksdown each day
that employee has worked. However, sometimes the employee will have
duplicate dates listed (from coming in and out of work). Is there a way
that
I can count each unique date once, rather than counting each record?
Currently it counts each line. Please help!!
Thank you!

 

[Allen Browne]

If you used the Running Sum method, you should be able to get an over all
total if you set the Running Sum to:
Over All

If you deduplicated with a query, you should be able to get the total count
with a text box in the Report Footer section, that has a Control Source of:
=Count("*")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
One more question: Now I can't seem to get a Report Total for all of the
"days worked" for all of the employees. Any suggestions??

You are awesome!! Thank you so much!

There are a couple of ways to do this.

The first is to create a query that deduplicates the records.
In query design view, click the Total button on the toolbar.
Then Group by the WorkDate field.
If the date/time field contains both a date and time, enter this
expression
in the Field row:
DateValue([WorkDate])
and group on that.

The other alternative is to do it in the report.
Create a Group Header on the WorkDate field.
In the lower pane of the Sorting And Grouping box for this field, set
the
properties:
Group On Day
In this group header, add a text box with these properties:
Control Source: =1
Running Sum: Over Group
Format General Number
Visible No
Name txtDateCountRS
Now in the EmployeeID Group Footer section, add a text box with Control
Source of:
=[txtDateCountRS]

(I am assuming you have a group footer for the employee above the group
footer for the date.)

I have a report that groups by Employee Name and then breaksdown each
day
that employee has worked. However, sometimes the employee will have
duplicate dates listed (from coming in and out of work). Is there a
way
that
I can count each unique date once, rather than counting each record?
Currently it counts each line. Please help!!

 

[Dani]

Unfortunately, this does not work. I am placing this overall count in the
report footer. I used the Running Sum method, which worked for the Employee
Groups. When I place this in the report footer and change the Running Sum to
Over All, it only gives me the number of unique dates over all...which it
calculates as 19. I need the count of each unique dates per employee
total...which usually runs around 400 days. Any help you could offer would be
wonderful
Thank you so much for your input.

If you used the Running Sum method, you should be able to get an over all
total if you set the Running Sum to:
Over All

If you deduplicated with a query, you should be able to get the total count
with a text box in the Report Footer section, that has a Control Source of:
=Count("*")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
One more question: Now I can't seem to get a Report Total for all of the
"days worked" for all of the employees. Any suggestions??

You are awesome!! Thank you so much!

:

There are a couple of ways to do this.

The first is to create a query that deduplicates the records.
In query design view, click the Total button on the toolbar.
Then Group by the WorkDate field.
If the date/time field contains both a date and time, enter this
expression
in the Field row:
DateValue([WorkDate])
and group on that.

The other alternative is to do it in the report.
Create a Group Header on the WorkDate field.
In the lower pane of the Sorting And Grouping box for this field, set
the
properties:
Group On Day
In this group header, add a text box with these properties:
Control Source: =1
Running Sum: Over Group
Format General Number
Visible No
Name txtDateCountRS
Now in the EmployeeID Group Footer section, add a text box with Control
Source of:
=[txtDateCountRS]

(I am assuming you have a group footer for the employee above the group
footer for the date.)

I have a report that groups by Employee Name and then breaksdown each
day
that employee has worked. However, sometimes the employee will have
duplicate dates listed (from coming in and out of work). Is there a
way
that
I can count each unique date once, rather than counting each record?
Currently it counts each line. Please help!!

.

 

[Allen Browne]

You will probabably need 2 text boxes in the EmployeeID group footer
section:
- one with Running Sum set to Over Group, to give the count for that group;
- one with Running Sum set to Over All, to give the total count for the
report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Unfortunately, this does not work. I am placing this overall count in the
report footer. I used the Running Sum method, which worked for the
Employee
Groups. When I place this in the report footer and change the Running Sum
to
Over All, it only gives me the number of unique dates over all...which it
calculates as 19. I need the count of each unique dates per employee
total...which usually runs around 400 days. Any help you could offer would
be
wonderful
Thank you so much for your input.

If you used the Running Sum method, you should be able to get an over all
total if you set the Running Sum to:
Over All

If you deduplicated with a query, you should be able to get the total
count
with a text box in the Report Footer section, that has a Control Source
of:
=Count("*")

Allen,
One more question: Now I can't seem to get a Report Total for all of
the
"days worked" for all of the employees. Any suggestions??

:

You are awesome!! Thank you so much!

:

There are a couple of ways to do this.

The first is to create a query that deduplicates the records.
In query design view, click the Total button on the toolbar.
Then Group by the WorkDate field.
If the date/time field contains both a date and time, enter this
expression
in the Field row:
DateValue([WorkDate])
and group on that.

The other alternative is to do it in the report.
Create a Group Header on the WorkDate field.
In the lower pane of the Sorting And Grouping box for this field,
set
the
properties:
Group On Day
In this group header, add a text box with these properties:
Control Source: =1
Running Sum: Over Group
Format General Number
Visible No
Name txtDateCountRS
Now in the EmployeeID Group Footer section, add a text box with
Control
Source of:
=[txtDateCountRS]

(I am assuming you have a group footer for the employee above the
group
footer for the date.)

I have a report that groups by Employee Name and then breaksdown
each
day
that employee has worked. However, sometimes the employee will
have
duplicate dates listed (from coming in and out of work). Is there
a
way
that
I can count each unique date once, rather than counting each
record?
Currently it counts each line. Please help!!

 

[Dani]

Thank you very much. That did the trick!

You will probabably need 2 text boxes in the EmployeeID group footer
section:
- one with Running Sum set to Over Group, to give the count for that group;
- one with Running Sum set to Over All, to give the total count for the
report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Unfortunately, this does not work. I am placing this overall count in the
report footer. I used the Running Sum method, which worked for the
Employee
Groups. When I place this in the report footer and change the Running Sum
to
Over All, it only gives me the number of unique dates over all...which it
calculates as 19. I need the count of each unique dates per employee
total...which usually runs around 400 days. Any help you could offer would
be
wonderful
Thank you so much for your input.

If you used the Running Sum method, you should be able to get an over all
total if you set the Running Sum to:
Over All

If you deduplicated with a query, you should be able to get the total
count
with a text box in the Report Footer section, that has a Control Source
of:
=Count("*")

Allen,
One more question: Now I can't seem to get a Report Total for all of
the
"days worked" for all of the employees. Any suggestions??

:

You are awesome!! Thank you so much!

:

There are a couple of ways to do this.

The first is to create a query that deduplicates the records.
In query design view, click the Total button on the toolbar.
Then Group by the WorkDate field.
If the date/time field contains both a date and time, enter this
expression
in the Field row:
DateValue([WorkDate])
and group on that.

The other alternative is to do it in the report.
Create a Group Header on the WorkDate field.
In the lower pane of the Sorting And Grouping box for this field,
set
the
properties:
Group On Day
In this group header, add a text box with these properties:
Control Source: =1
Running Sum: Over Group
Format General Number
Visible No
Name txtDateCountRS
Now in the EmployeeID Group Footer section, add a text box with
Control
Source of:
=[txtDateCountRS]

(I am assuming you have a group footer for the employee above the
group
footer for the date.)

I have a report that groups by Employee Name and then breaksdown
each
day
that employee has worked. However, sometimes the employee will
have
duplicate dates listed (from coming in and out of work). Is there
a
way
that
I can count each unique date once, rather than counting each
record?
Currently it counts each line. Please help!!