CODEWITHSUNDEEP
sqlms-access
sigil
sigil

Reputation: 9546

Having trouble using count() in Access SQL query

Using Access 2007.

I have a table Inventory with the following fields:

ContainerID FolderID

My goal is to find out which FolderIDs correspond to more than one ContainerID, and what the ContainerIDs are for these records. I thought the best way to handle this would be doing a query that returns ContainerID, FolderID, and a count of each FolderID, so that I could sort that list to put the records with count>1 on top. I tried this with:

select ContainerID,FolderID,count(FolderID) from (select distinct * from Inventory);

but Access gave me an error message: "You tried to execute a query that does not include the specified expression 'ContainerID' as part of an aggregate function."

How can I get the results I'm looking for?

EDIT: I'm trying all of your solutions but all of them keep getting a lot of duplicate rows. I just tried to filter them out with DISTINCT but for some reason that froze up Access. I have to go home so I'll check these out again tomorrow morning. Thanks for offering suggestions.

Upvotes: 0

Views: 6833

Answers (4)

amit_g
amit_g

Reputation: 31250

Try the following query.

select
     I.*
    ,T.FolderCount
from
    Inventory I
inner join
(
    select
          ContainerID
        , Count(*) as FolderCount
    from
        Inventory
    group by
        ContainerID
    having
        Count(*) > 1
) T
on
    I.ContainerID = T.ContainerID

EDIT:

select
     I.*
    ,T.ContainerCount
from
    Inventory I
inner join
(
    select
          FolderID
        , Count(*) as ContainerCount
    from
        Inventory
    group by
        FolderID
    having
        Count(*) > 1
) T
on
    I.FolderID = T.FolderID

Upvotes: 1

mattd
mattd

Reputation: 1

Unfortunately I don't have access 2007, but in sql server the following works:

create table Inventory
(
 ContainerID int,
 FolderID int
)

insert into Inventory (ContainerID, FolderID)
select 1, 1
union all select 1, 2
union all select 1, 3
union all select 1, 1
union all select 2, 1
union all select 2, 2
union all select 3, 1

select distinct i1.ContainerID, i1.FolderID
from Inventory i1
inner join (select FolderID
            from Inventory a
            group by FolderID
            having count(distinct ContainerID) > 1) i2
on i1.FolderID = i2.FolderID

Added example for clarification of why I submitted this answer. From my understanding of the question the results were to have container/folder pairs that were multiple containerIDs not multiple rows.

Upvotes: 0

Christian Specht
Christian Specht

Reputation: 36421

Step 1: Get the FolderIDs which have more than one ContainerID:

select folderid 
from inventory 
group by folderid 
having count(folderid)>1

Step 2: Get a list of all these FolderIDs and their corresponding ContainerIDs:

select * 
from inventory 
where folderid in
(
   select folderid 
   from inventory 
   group by folderid 
   having count(folderid)>1
)
order by folderid, containerid

Upvotes: 1

DaveE
DaveE

Reputation: 3637

This should do the trick:

SELECT ContainerID, FolderID, count(FolderID) 
FROM    Inventory 
GROUP BY ContainerID, FolderID;

You need the GROUP BY for the count functions to have specific groupings to work with, otherwise it operates on all available data. Unless you had exactly one unique combination of ContainerID & FolderID, you can't select those fields along with the results of the aggregate function 'count'.

Upvotes: 4

Related Questions