I have a simple table that has the following structure:

location name
200608 GROUP Summary PPR.xls Response
200608 GROUP Summary PPR.xls RFP
Portfolio Management project (2).msg cognos
Portfolio Management project.msg cognos
picdarr1.doc Report
picdarr2.doc Status

What I am trying to create is the following result:

name Cnt1
Agenda 293
Analysis 144
Annual 11
Appendix 318
Approach 212

generated via the SQL:
SELECT name, count(name) AS Cnt1 FROM emxkx
GROUP BY name;

My actual query is slightly more complex as it is based on a sub query as follows:

SELECT name, count(*)
FROM emxkx
WHERE location IN (SELECT location FROM emxkx WHERE name ="Demo")
AND name <> "Demo"
GROUP BY name;

When I run this query Access system says it is running but takes forever and it is clearly not working.

If I make a slight mod and run the following query:

SELECT name
FROM emxkx
WHERE location IN (SELECT location FROM emxkx WHERE name ="Demo")
AND name <> "Demo"
ORDER BY name;

I get the following results:

name
Agenda
indexing
indexing
kpi
kpi
metrics
organigram
prototype
Prototype
prototype
Response
Response
RFP
RFP
roadmap
Service
Test
Test

So I seem to be close and yet not quite there. Any pointers greatfully received.

I have a simple table that has the following structure:

location name
200608 GROUP Summary PPR.xls Response
200608 GROUP Summary PPR.xls RFP
Portfolio Management project (2).msg cognos
Portfolio Management project.msg cognos
picdarr1.doc Report
picdarr2.doc Status

What I am trying to create is the following result:

name Cnt1
Agenda 293
Analysis 144
Annual 11
Appendix 318
Approach 212

generated via the SQL:
SELECT name, count(name) AS Cnt1 FROM emxkx
GROUP BY name;

My actual query is slightly more complex as it is based on a sub query as follows:

SELECT name, count(*)
FROM emxkx
WHERE location IN (SELECT location FROM emxkx WHERE name ="Demo")
AND name <> "Demo"
GROUP BY name;

When I run this query Access system says it is running but takes forever and it is clearly not working.

If I make a slight mod and run the following query:

SELECT name
FROM emxkx
WHERE location IN (SELECT location FROM emxkx WHERE name ="Demo")
AND name <> "Demo"
ORDER BY name;

I get the following results:

name
Agenda
indexing
indexing
kpi
kpi
metrics
organigram
prototype
Prototype
prototype
Response
Response
RFP
RFP
roadmap
Service
Test
Test

So I seem to be close and yet not quite there. Any pointers greatfully received.

I have a similar query based on my tables and fields. I tried to make it the same context as yours.
This works on my table and data.

My address-city equates to your "location" field.

See attached jpg for my data.

SELECT DateDiffStuff.name
,Count(DateDiffStuff.name) AS CountOfname
FROM DateDiffStuff
where
(((DateDiffStuff.name)<>"Sam") AND
((DateDiffStuff.[address-city]) In (SELECT [x.address-city] FROM datediffstuff as X
WHERE [x.name] ="sam")))
GROUP BY DateDiffStuff.name;

Result
====
name CountOfname
bill 1
samy 1

Many thanks jdraw. Your SQL syntax had the desired effect. I tried your SQL code on a much smaller data subset and it certainly worked (~200 rows). However, when I ran it against the 100k rows it is running very slowly (still hasn't completed and I started the run over 5 mins ago.

When I consider that the performance of

SELECT name
FROM emxkx
WHERE location IN (SELECT location FROM emxkx WHERE name ="Demo")
AND name <> "Demo"
ORDER BY name;

is nearly immediate, is there a way for me to take these results

name
Agenda
indexing
indexing
kpi
kpi
metrics
organigram
prototype
Prototype
prototype
Response
Response
RFP
RFP
roadmap
Service
Test
Test

and perform a count against that to get better performance?

Many thanks jdraw. Your SQL syntax had the desired effect. I tried your SQL code on a much smaller data subset and it certainly worked (~200 rows). However, when I ran it against the 100k rows it is running very slowly (still hasn't completed and I started the run over 5 mins ago.

When I consider that the performance of

SELECT name
FROM emxkx
WHERE location IN (SELECT location FROM emxkx WHERE name ="Demo")
AND name <> "Demo"
ORDER BY name;

is nearly immediate, is there a way for me to take these results

name
Agenda
indexing
indexing
kpi
kpi
metrics
organigram
prototype
Prototype
prototype
Response
Response
RFP
RFP
roadmap
Service
Test
Test

and perform a count against that to get better performance?

I'm not sure why it take so long. I know that IN can be long running if it's doing a lot of checking. But maybe there's another way.

How many locations do you get back from the subquery

SELECT location FROM emxkx WHERE name ="Demo"

If it's only 1, you could do something like this:

SELECT name, count(*)
FROM emxkx
WHERE location = "enter the proper location name"
AND name <> "Demo"
GROUP BY name;

Thanks again jdraw. The result set for the example is about 30 or so.

What I have done is broken down the task into smaller SQL elements and actually created a temporary table area which i purge prior to any call and insert the results of my "names" including duplicates. This is very fast.

I then run a simple

select name, count(name) ...

against this temp table and use those results for my combo box pull down. It's pretty quick albeit a bit more messy as more SQL calls required.

Appreciate your inputs and learning to play around with access. The earlier problem was bugging me tho and appreciate your solutions!