Counting unique values

  • Thread starter Thread starter Rossz
  • Start date Start date
R

Rossz

I'm working on a report containing magazine delivery information. The
associated query obtains the location id, publication number, magazine
count, etc. Each location can receive one or more different magazines. I
am trying to get the number of delivery stops. Using Count([Location ID])
returns the wrong value as it is counting each magazine as an individual
stop even though it has the same location id. GROUP BY doesn't work since
the combination of Location ID and Publication Number makes the row unique.

I tried creating a seperate query that only contains the group by location
ID and doing a count of that, but I can't get the textbox to reference a
different query than the primary one as its control source, nor can I get
it to use a simple query there.


Location ID, Pub#
1, 1
1, 2
2, 1
3, 2

For this, there are 3 stops, with location 1 getting two magazines, but
Count([Location ID]) returns 4.
 
Rossz said:
I'm working on a report containing magazine delivery information. The
associated query obtains the location id, publication number, magazine
count, etc. Each location can receive one or more different magazines. I
am trying to get the number of delivery stops. Using Count([Location ID])
returns the wrong value as it is counting each magazine as an individual
stop even though it has the same location id. GROUP BY doesn't work since
the combination of Location ID and Publication Number makes the row unique.

I tried creating a seperate query that only contains the group by location
ID and doing a count of that, but I can't get the textbox to reference a
different query than the primary one as its control source, nor can I get
it to use a simple query there.


Location ID, Pub#
1, 1
1, 2
2, 1
3, 2

For this, there are 3 stops, with location 1 getting two magazines, but
Count([Location ID]) returns 4.
Click to expand...

You get 4 because ther are 4 records returned by that query.

If all you want is a single number, then the query should be
a group by [Location ID] with no other fields in the query.

SELECT [Location ID]
FROM originalquery
GROUP BY [Location ID]

Then a text box in the report can use the expression:
=DCount("[Location ID]", "newquery")

Your situation is probably more complex than that, so if
that doesn't do what you want post back here and straighten
me out on the details of your situation.
 
If all you want is a single number, then the query should be
a group by [Location ID] with no other fields in the query.

SELECT [Location ID]
FROM originalquery
GROUP BY [Location ID]

Then a text box in the report can use the expression:
=DCount("[Location ID]", "newquery")

Your situation is probably more complex than that, so if
that doesn't do what you want post back here and straighten
me out on the details of your situation.
Click to expand...

This works perfectly, though I dislike having to toss in another seperate
query. I would have prefered to grab the data direct (too many small
queries clutter things up, in my opinion).
 
If all you want is a single number, then the query should be
a group by [Location ID] with no other fields in the query.

SELECT [Location ID]
FROM originalquery
GROUP BY [Location ID]

Then a text box in the report can use the expression:
=DCount("[Location ID]", "newquery")

Your situation is probably more complex than that, so if
that doesn't do what you want post back here and straighten
me out on the details of your situation.
Click to expand...

Sort of related (maybe). They (the client) have requested the name of the
location be bold when more than one publication is delivered to a location.
I tried using your example to check if the value is > 1 inside of
"Conditional Formatting", but I couldn't get the syntax correct. I tried
these expressions (wild ass guesses on my part):

DCount("[Stops]","Count Stops","[Count Stops].[Location
ID]=Locations.[Location ID]")>1

and

DCount("[Stops]","Count Stops","[Location ID]=Locations.[Location ID]")>1

Neither worked, of course. Is this even possible?
 
Rossz said:
If all you want is a single number, then the query should be
a group by [Location ID] with no other fields in the query.

SELECT [Location ID]
FROM originalquery
GROUP BY [Location ID]

Then a text box in the report can use the expression:
=DCount("[Location ID]", "newquery")

Your situation is probably more complex than that, so if
that doesn't do what you want post back here and straighten
me out on the details of your situation.
Click to expand...

Sort of related (maybe). They (the client) have requested the name of the
location be bold when more than one publication is delivered to a location.
I tried using your example to check if the value is > 1 inside of
"Conditional Formatting", but I couldn't get the syntax correct.
Click to expand...

I'm not sure I understand, but didn't you say in your
original post that a Count(*) text box gave you the number
of pubs delivered to a location? If so, then can't you base
the CF expression on that text box?
 
I'm not sure I understand, but didn't you say in your
original post that a Count(*) text box gave you the number
of pubs delivered to a location? If so, then can't you base
the CF expression on that text box?
Click to expand...

I had assumed something like that would do the job. I plugged in

[Expression is] "Count(Locations.[Location ID])>1"

But the report shows "#Name?" for the affected field.
 
Rossz said:
I'm not sure I understand, but didn't you say in your
original post that a Count(*) text box gave you the number
of pubs delivered to a location? If so, then can't you base
the CF expression on that text box?
Click to expand...

I had assumed something like that would do the job. I plugged in

[Expression is] "Count(Locations.[Location ID])>1"

But the report shows "#Name?" for the affected field.
Click to expand...


Add a text box named txtLocCount to the location group
header and use the expression =Count(*)

Then use txtLocCount > 1 in the CF expression.
 
Add a text box named txtLocCount to the location group
header and use the expression =Count(*)

Then use txtLocCount > 1 in the CF expression.
Click to expand...


Ok, I put the textbox in the "Location ID" group header, made that section
hidden since I didn't want it to display. Then I tried to reference it
from the detail, but it kept changing txtLocCount>1 to "txtLocCount">1
(note the quotes).
 
Marshall Barton wrote
Rossz said:
Ok, I put the textbox in the "Location ID" group header, made that section
hidden since I didn't want it to display. Then I tried to reference it
from the detail, but it kept changing txtLocCount>1 to "txtLocCount">1
(note the quotes).
Click to expand...


Are you sure you set the text box's Name to txtLocCount?

Are you sure you're using the CF Expression (not Value)
option?

I can't get Access add those quotes no matter what I do.

If all else fails, you can always use the tried and true
approach of doing it in code in the detail section's Format
event:

If Me.txtLocCount > 1 Then
Me.sometextbox.FontBold = True
Else
Me.sometextbox.FontBold = False
End If
 
Are you sure you set the text box's Name to txtLocCount?
Click to expand...

My first reaction was I spelled it incorrectly, so I double then triple
checked it.
Are you sure you're using the CF Expression (not Value)
option?
Yep.

If all else fails, you can always use the tried and true
approach of doing it in code in the detail section's Format
event:
Click to expand...

That worked. I had hoped to avoid using code hacks, but I'll live with it.

Thanks for your help.
 
If all you want is a single number, then the query should be
a group by [Location ID] with no other fields in the query.

SELECT [Location ID]
FROM originalquery
GROUP BY [Location ID]

Then a text box in the report can use the expression:
=DCount("[Location ID]", "newquery")

Your situation is probably more complex than that, so if
that doesn't do what you want post back here and straighten
me out on the details of your situation.
Click to expand...

This works perfectly, though I dislike having to toss in another
seperate query. I would have prefered to grab the data direct (too
many small queries clutter things up, in my opinion).
Click to expand...

I spoke too soon. It doesn't work perfectly because I am not limiting the
counting to a specific [Route #], so the count ends up being the total
stops for ALL routes instead of the specific route.

Is there a way to get dcount to use a "current" value in the condition?

DCount( "[Location ID]", "Count Stops", "[Location ID]=[something]")

I can only find examples that use hard-coded values.
 
Rossz said:
Marshall Barton said:
If all you want is a single number, then the query should be
a group by [Location ID] with no other fields in the query.

SELECT [Location ID]
FROM originalquery
GROUP BY [Location ID]

Then a text box in the report can use the expression:
=DCount("[Location ID]", "newquery")

Your situation is probably more complex than that, so if
that doesn't do what you want post back here and straighten
me out on the details of your situation.
Click to expand...

This works perfectly, though I dislike having to toss in another
seperate query. I would have prefered to grab the data direct (too
many small queries clutter things up, in my opinion).
Click to expand...
Click to expand...
Rossz said:
I spoke too soon. It doesn't work perfectly because I am not limiting the
counting to a specific [Route #], so the count ends up being the total
stops for ALL routes instead of the specific route.

Is there a way to get dcount to use a "current" value in the condition?

DCount( "[Location ID]", "Count Stops", "[Location ID]=[something]")

I can only find examples that use hard-coded values.
Click to expand...


DCount("[Location ID]", "Count Stops",
"[Location ID]=" & numericvariable)

or

DCount("[Location ID]", "Count Stops",
"[Location ID]=""" & stringvariable & """")
 
Back
Top