Count Unique

  • Thread starter Thread starter Matt
  • Start date Start date
M

Matt

Ok...I'm in way over my head here. I probably have just
enough knowledge in Access with too little in VBA to be
dangerous. Wondering if anyone can take a few seaconds to
help me dig myself in deeper.

What I have is a query in a subform that I have the Link
Child Field as a date and the Link Masterfield as a date
on the parent form. So that when you chose a date on the
form (the Masterfield) it will pull up every event on that
date. The problem is there are sub-events under each
event that are acutally the line items in the query.

So my query looks like this

Event Time Event Sub-event
12/12/04 05:01 Truck0005 Order 2134
12/12/04 05:01 Truck0005 Order 2133
12/12/04 05:01 Truck0005 Order 2132
12/12/04 16:32 Truck0002 Order 2158
12/12/04 16:32 Truck0002 Order 2157

My challenge: I need to count the unique Events. I'm
wondering if there is a way to do this with an already
built in function. I was thinking it would be grand if
there was somthing like CountUnique([Event]). Or do I
have to do this with VBA code? The other way I could do
this, of course is to run another query of the same type
that was a "Unique" query and link it the same way and
then use the count() funciton. And copapse it to be so
tiny you couldn't see it. But I think that would be way to
sloppy. (Although I have very little experiance with VBA
so that might be the way I'd go until I figure it out)

Well I'd be very appreciative if anybody can offer any
suggestions or direction.
 
Event Time Event Sub-event
12/12/04 05:01 Truck0005 Order 2134
12/12/04 05:01 Truck0005 Order 2133
12/12/04 05:01 Truck0005 Order 2132
12/12/04 16:32 Truck0002 Order 2158
12/12/04 16:32 Truck0002 Order 2157

My challenge: I need to count the unique Events. I'm
wondering if there is a way to do this with an already
built in function.
Click to expand...

Not really. Even in Queries, Access doesn't support the COUNT DISTINCT
predicate recognized by many other dbms's.

I'd suggest creating a Unique Values Query referencing the Master Link
Field as a criterion, and use DCount() to count the number of
subevents.
 
Back
Top