Count 'unique' field only once

[maw230]

I have a query that returns Line, Item, Count of Store. The idea is to get a total count of stores that stock a particular Line, Item combo. The problem is that the table used for this query stores a Line, Item combo multiple times if the Item is located in more than one place throughout the store.

However, for all intents and purposes, I need to only know if the Line, Item combo occurs 1 time per store and count that store number one time only.

So, for example, if a Store has Line, Item combo #1 located in 4 different areas it will return a count of 4 where I need a count of one.

This can be solved by making a new query that uses the first query as a record source, but for one reason or another (not really sure...) this needs to be done in one query.

Select DISTINCT is being used, but in this case doesn't really matter.

How do I tell the query to only count the Store one time for each unique Line, Item combo that occurs and not how often it occurs?

 

[plog]

There's no way around this--you are going to need 2 queries. Semantically, you can get this all into 1 query object by using a Sub-query. That SQL would look like this:

SELECT UniqueStores.Line, UniqueStores.Item, Count(UniqueStores.Store) AS TotalStores
FROM (SELECT YourTableNameHere.Line, YourTableNameHere.Item, YourTableNameHere.Store
FROM YourTableNameHere
GROUP BY YourTableNameHere.Line, YourTableNameHere.Item, YourTableNameHere.Store)  AS UniqueStores
GROUP BY UniqueStores.Line, UniqueStores.Item;

Replace all instances of 'YourTableNameHere' with your table's name.

 

[maw230]

There's no way around this--you are going to need 2 queries. Semantically, you can get this all into 1 query object by using a Sub-query. That SQL would look like this:

SELECT UniqueStores.Line, UniqueStores.Item, Count(UniqueStores.Store) AS TotalStores
FROM (SELECT YourTableNameHere.Line, YourTableNameHere.Item, YourTableNameHere.Store
FROM YourTableNameHere
GROUP BY YourTableNameHere.Line, YourTableNameHere.Item, YourTableNameHere.Store)  AS UniqueStores
GROUP BY UniqueStores.Line, UniqueStores.Item;

Replace all instances of 'YourTableNameHere' with your table's name.

Ah, forgot about the subquery! I will give this a shot.