Trying to not count duplicates in a report

  • Thread starter Thread starter clueless
  • Start date Start date
C

clueless

I am new to Access. I have a field OrderID, I need it to count the total #
of orders for a daily report and a weekly report. The problem is more than 1
employee will be dealing with each order (1 employee picks the order, another
employee verifies and loads the order on the truck), so in the 2 reports it
shows the same OrderID for both employees and counts it twice; I need it to
only count it once, and to add more confusion it might be picked and loaded
the same day or it could be picked on say Monday and loaded on Tuesday. I
have read all the postings about duplicate counts and my head is spinning. I
really would appreciate any help that someone can offer me. Thanks in
advance!
 
In report design view, click on View | Sorting and Grouping (I'm making the
assumption that you are using Access 2003 or earlier. If you are using Access
2007, the logic will be the same, but you'll have to find the corresponding
steps using the ribbon). Add your OrderID field to the Sorting and Grouping
dialog, with Group Header = Yes, Group Footer = No, Group On = Each Value,
Group Interval = 1 and Keep Together = Whole Group.

Expand the Report Footer section, if it has a height of zero. Add a text box
with the following calculated expression:

=Count([OrderID])

Where OrderID is the name of the field. Make the appropriate substitution if
your field is named something else, such as [Order ID]. Make sure that the
name of this text box in not the same as the name of the any fields involved
in the expression.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
Using
=Count([Any Non-Null Expression])
in a report footer will always count the total number of records in the
report. If the OrderID is repeated, you can add a text box to the OrderID
header:
Name: txtCountOrder
Control Source: =1
Running Sum: Over All
Visible: No
Then add a text box to your report footer:
Control Source: =txtCountOrder

--
Duane Hookom
Microsoft Access MVP


Tom Wickerath said:
In report design view, click on View | Sorting and Grouping (I'm making the
assumption that you are using Access 2003 or earlier. If you are using Access
2007, the logic will be the same, but you'll have to find the corresponding
steps using the ribbon). Add your OrderID field to the Sorting and Grouping
dialog, with Group Header = Yes, Group Footer = No, Group On = Each Value,
Group Interval = 1 and Keep Together = Whole Group.

Expand the Report Footer section, if it has a height of zero. Add a text box
with the following calculated expression:

=Count([OrderID])

Where OrderID is the name of the field. Make the appropriate substitution if
your field is named something else, such as [Order ID]. Make sure that the
name of this text box in not the same as the name of the any fields involved
in the expression.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________


clueless said:
I am new to Access. I have a field OrderID, I need it to count the total #
of orders for a daily report and a weekly report. The problem is more than 1
employee will be dealing with each order (1 employee picks the order, another
employee verifies and loads the order on the truck), so in the 2 reports it
shows the same OrderID for both employees and counts it twice; I need it to
only count it once, and to add more confusion it might be picked and loaded
the same day or it could be picked on say Monday and loaded on Tuesday. I
have read all the postings about duplicate counts and my head is spinning. I
really would appreciate any help that someone can offer me. Thanks in
advance!
Click to expand...
Click to expand...
 
I couldn't begin to count the number of times my answers have been
"clarified" by others ;-)
--
Duane Hookom
Microsoft Access MVP


Tom Wickerath said:
Hi Duane,

Yep, you're right. I screwed up. I had created a quicky test report that
included 65 pages, and it showed 1665 total orders. I made the incorrect
assumption that 1665 represented unique order numbers. It did not. Oops.
Thanks for the correction.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Duane Hookom said:
Using
=Count([Any Non-Null Expression])
in a report footer will always count the total number of records in the
report. If the OrderID is repeated, you can add a text box to the OrderID
header:
Name: txtCountOrder
Control Source: =1
Running Sum: Over All
Visible: No
Then add a text box to your report footer:
Control Source: =txtCountOrder
Click to expand...
Click to expand...
 
Hi. Thanks for the quick response. I put in =Count([Any Non-Null
Expression]) and now an enter Parameter value box pops up when I go to look
at the report. Why is that and what do I need to do? (I am using Access 2007)
--
clueless


Duane Hookom said:
Using
=Count([Any Non-Null Expression])
in a report footer will always count the total number of records in the
report. If the OrderID is repeated, you can add a text box to the OrderID
header:
Name: txtCountOrder
Control Source: =1
Running Sum: Over All
Visible: No
Then add a text box to your report footer:
Control Source: =txtCountOrder

--
Duane Hookom
Microsoft Access MVP


Tom Wickerath said:
In report design view, click on View | Sorting and Grouping (I'm making the
assumption that you are using Access 2003 or earlier. If you are using Access
2007, the logic will be the same, but you'll have to find the corresponding
steps using the ribbon). Add your OrderID field to the Sorting and Grouping
dialog, with Group Header = Yes, Group Footer = No, Group On = Each Value,
Group Interval = 1 and Keep Together = Whole Group.

Expand the Report Footer section, if it has a height of zero. Add a text box
with the following calculated expression:

=Count([OrderID])

Where OrderID is the name of the field. Make the appropriate substitution if
your field is named something else, such as [Order ID]. Make sure that the
name of this text box in not the same as the name of the any fields involved
in the expression.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________


clueless said:
I am new to Access. I have a field OrderID, I need it to count the total #
of orders for a daily report and a weekly report. The problem is more than 1
employee will be dealing with each order (1 employee picks the order, another
employee verifies and loads the order on the truck), so in the 2 reports it
shows the same OrderID for both employees and counts it twice; I need it to
only count it once, and to add more confusion it might be picked and loaded
the same day or it could be picked on say Monday and loaded on Tuesday. I
have read all the postings about duplicate counts and my head is spinning. I
really would appreciate any help that someone can offer me. Thanks in
advance!
Click to expand...
Click to expand...
Click to expand...
 
Duane's solution should work with your daily report, but if you are first
grouping the weekly report by days then the same OrderID could appear in more
than one group header if picked and loaded on separate days as this would be
the second group level, so it would again be counted twice.

A solution would be to count the distinct OrderID values in code in the
report's module. First you'd declare two module level variables, one to hold
a value list of the distinct OrderIDs, one to hold the count. In the detail
section's Print event procedure the OrderID would be added to the value list
and the count incremented each time a new OrderID is encountered. Finally
the value of the count would be assigned to an unbound text box in the report
footer. So the report's module would look something like this:

Option Compare Database
Option Explicit

Dim strIDList As String
Dim intIDCount As Integer

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

If PrintCount = 1 Then
If InStr(strIDList, "~" & Me.OrderID) = 0 Then
strIDList = strIDList & "~" & Me.OrderID
intIDCount = intIDCount + 1
End If
End If

End Sub

Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)

Me.txtOrderCount = intIDCount

End Sub

Note that I've used the tilde character as the delimiter for the value list
rather than the more usual comma, colon or semi-colon. This is just in case
you are using a structured OrderID (or equivalent) which might contain one of
those characters. I'm assuming it won't contain a tilde! If it’s a simple
number such as an autonumber it will still work of course.

Ken Sheridan
Stafford, England
 
My suggestion was to NOT use the =Count([...]). You need to understand that
when some suggests something like "Any Non-Null Expression" this is an
instruction like "Press Any Key". You won't find a key on your keyboard with
"Any" printed on it.

--
Duane Hookom
Microsoft Access MVP


clueless said:
Hi. Thanks for the quick response. I put in =Count([Any Non-Null
Expression]) and now an enter Parameter value box pops up when I go to look
at the report. Why is that and what do I need to do? (I am using Access 2007)
--
clueless


Duane Hookom said:
Using
=Count([Any Non-Null Expression])
in a report footer will always count the total number of records in the
report. If the OrderID is repeated, you can add a text box to the OrderID
header:
Name: txtCountOrder
Control Source: =1
Running Sum: Over All
Visible: No
Then add a text box to your report footer:
Control Source: =txtCountOrder

--
Duane Hookom
Microsoft Access MVP


Tom Wickerath said:
In report design view, click on View | Sorting and Grouping (I'm making the
assumption that you are using Access 2003 or earlier. If you are using Access
2007, the logic will be the same, but you'll have to find the corresponding
steps using the ribbon). Add your OrderID field to the Sorting and Grouping
dialog, with Group Header = Yes, Group Footer = No, Group On = Each Value,
Group Interval = 1 and Keep Together = Whole Group.

Expand the Report Footer section, if it has a height of zero. Add a text box
with the following calculated expression:

=Count([OrderID])

Where OrderID is the name of the field. Make the appropriate substitution if
your field is named something else, such as [Order ID]. Make sure that the
name of this text box in not the same as the name of the any fields involved
in the expression.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________


:

I am new to Access. I have a field OrderID, I need it to count the total #
of orders for a daily report and a weekly report. The problem is more than 1
employee will be dealing with each order (1 employee picks the order, another
employee verifies and loads the order on the truck), so in the 2 reports it
shows the same OrderID for both employees and counts it twice; I need it to
only count it once, and to add more confusion it might be picked and loaded
the same day or it could be picked on say Monday and loaded on Tuesday. I
have read all the postings about duplicate counts and my head is spinning. I
really would appreciate any help that someone can offer me. Thanks in
advance!
Click to expand...
Click to expand...
Click to expand...
 
Duane, Sorry about that. Yes, I finally figured out what you were telling me
and it works beautifully for my daily report, so I thank you. Next, I have
to try to figure out what Ken is telling me. I really like Access, but it is
such a challenge. Thanks again for your help.
--
clueless


Duane Hookom said:
My suggestion was to NOT use the =Count([...]). You need to understand that
when some suggests something like "Any Non-Null Expression" this is an
instruction like "Press Any Key". You won't find a key on your keyboard with
"Any" printed on it.

--
Duane Hookom
Microsoft Access MVP


clueless said:
Hi. Thanks for the quick response. I put in =Count([Any Non-Null
Expression]) and now an enter Parameter value box pops up when I go to look
at the report. Why is that and what do I need to do? (I am using Access 2007)
--
clueless


Duane Hookom said:
Using
=Count([Any Non-Null Expression])
in a report footer will always count the total number of records in the
report. If the OrderID is repeated, you can add a text box to the OrderID
header:
Name: txtCountOrder
Control Source: =1
Running Sum: Over All
Visible: No
Then add a text box to your report footer:
Control Source: =txtCountOrder

--
Duane Hookom
Microsoft Access MVP


:

In report design view, click on View | Sorting and Grouping (I'm making the
assumption that you are using Access 2003 or earlier. If you are using Access
2007, the logic will be the same, but you'll have to find the corresponding
steps using the ribbon). Add your OrderID field to the Sorting and Grouping
dialog, with Group Header = Yes, Group Footer = No, Group On = Each Value,
Group Interval = 1 and Keep Together = Whole Group.

Expand the Report Footer section, if it has a height of zero. Add a text box
with the following calculated expression:

=Count([OrderID])

Where OrderID is the name of the field. Make the appropriate substitution if
your field is named something else, such as [Order ID]. Make sure that the
name of this text box in not the same as the name of the any fields involved
in the expression.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________


:

I am new to Access. I have a field OrderID, I need it to count the total #
of orders for a daily report and a weekly report. The problem is more than 1
employee will be dealing with each order (1 employee picks the order, another
employee verifies and loads the order on the truck), so in the 2 reports it
shows the same OrderID for both employees and counts it twice; I need it to
only count it once, and to add more confusion it might be picked and loaded
the same day or it could be picked on say Monday and loaded on Tuesday. I
have read all the postings about duplicate counts and my head is spinning. I
really would appreciate any help that someone can offer me. Thanks in
advance!
Click to expand...
Click to expand...
Click to expand...
 
Ken,
Thanks for responding. Please bear with me, my 2 one day Access Certificate
classes at the local college did not cover modules. If I insert it in a
module, I get an error "Invalid use of Me keyword". If I insert it in a
class module, I get "Method or data member not found" and .OrderID is
highlighted. No idea what the difference is between the two and definitely
no idea how to correct the error. What do I need to do? Thanks for your
help!
 
Ken's suggestion would place the code in the Report's module. You could
select the detail section of the report and find the On Print event. Click
the builder button [...] on the far right and choose to enter some code.
--
Duane Hookom
Microsoft Access MVP


clueless said:
Ken,
Thanks for responding. Please bear with me, my 2 one day Access Certificate
classes at the local college did not cover modules. If I insert it in a
module, I get an error "Invalid use of Me keyword". If I insert it in a
class module, I get "Method or data member not found" and .OrderID is
highlighted. No idea what the difference is between the two and definitely
no idea how to correct the error. What do I need to do? Thanks for your
help!
Click to expand...
 
Duane,
Thanks for your help in clarifying how to do this. I appreciate it!
--
clueless


Duane Hookom said:
Ken's suggestion would place the code in the Report's module. You could
select the detail section of the report and find the On Print event. Click
the builder button [...] on the far right and choose to enter some code.
--
Duane Hookom
Microsoft Access MVP


clueless said:
Ken,
Thanks for responding. Please bear with me, my 2 one day Access Certificate
classes at the local college did not cover modules. If I insert it in a
module, I get an error "Invalid use of Me keyword". If I insert it in a
class module, I get "Method or data member not found" and .OrderID is
highlighted. No idea what the difference is between the two and definitely
no idea how to correct the error. What do I need to do? Thanks for your
help!
Click to expand...
Click to expand...
 
This post is pretty old but exactly what I am looking for. I followed the
instructions but for some reason it is erroring at Me.txtOrderCount =
intIDCount. I have very little knowledge of code and I am not sure what I am
doing wrong. Could you please explain in more detail? Thanks for your help.
 
Back
Top