counting unique values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm a VBA rookie. I'm attempting to count the number of events that could be
listed in multiple rows due to multiple products associated with the event.
I am adding a group header on the report for each district, so I want a total
for each district. I went to google and found code that I thought was
similar to my situation. However, as I'm attempting, I'm realizing that it
doesn't know what to count. This code counts records in a grouping, but I
can't do that because events may have more than 1 record due to multiple
products. Here are the lines of code that I copied


intEventCount = 0
intEventCount = intEventCount + 1
Me!Text16.Value = intEventCount

I want to count unique values of a field called Event_Row_ID. My question
is, how can I tell VBA that is what I want to count, and I only want to count
unique values. Text16 is the text box that will show the sum.

Thank you in advance for your help.
 
Susie said:
I'm a VBA rookie. I'm attempting to count the number of events that could be
listed in multiple rows due to multiple products associated with the event.
I am adding a group header on the report for each district, so I want a total
for each district. I went to google and found code that I thought was
similar to my situation. However, as I'm attempting, I'm realizing that it
doesn't know what to count. This code counts records in a grouping, but I
can't do that because events may have more than 1 record due to multiple
products. Here are the lines of code that I copied


intEventCount = 0
intEventCount = intEventCount + 1
Me!Text16.Value = intEventCount

I want to count unique values of a field called Event_Row_ID. My question
is, how can I tell VBA that is what I want to count, and I only want to count
unique values. Text16 is the text box that will show the sum.

Thank you in advance for your help.
Click to expand...

Hello

As an aside you can write a query which counts the unique values in a
table/query. For example, something like:
SELECT myTable.Event_Row_ID, Count(myTable.Event_Row_ID) AS CountOfEvent
FROM myTable
GROUP BY myTable.Event_Row_ID;

would produce
event1 3
event2 2

from the following table:
<myTable>
EVENT
event1
event2
event1
event1
event2

However if you are doing this in a report then you may be able to use the
grouping functionality supplied by Access. Search for report grouping in
Access and see if that gives you what you need. It does depend on how you
are formatting/outputting (ie grouping) your results as to whether it will
work in your specific situation.

So assuming that you cannot get Access to do it automatically for you
(definitely investigate report grouping as your first step), what you can do
is use a lookup (eg DLOOKUP) with something like the above query as the doma
in/recordsource and for the where part of the lookup set it to the field
which has the restriction/grouping you need.

I confess I am not 100% sure of what you are wanting to do, so if the above
wont work or doesnt make sense perhaps post back with an example and the NG
can take it from there.

Regards
A
 
Back
Top