[AccessD] Counting Co IDs

Well thanks to the Friday morning Access users and beach watchers in SD we
came up with a solution. More accurately, Mike Dwyer whipped it out on a
napkin. Here goes:

SELECT a.jid, a.q1, Count(*) AS NumCompanies
FROM [select distinct jid,q1,coid from responses where q1 is not null]. AS a
GROUP BY a.jid, a.q1;


Funny tho, if you look at the QBE, no trace of the sub query.....
I guess it is only capable of so much.

Thanks again to all who contributed.

Bob Heygood


 -----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gary Kjos
Sent: Thursday, November 01, 2007 2:03 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Counting Co IDs

I would probably do two queries, one to select and to possibly group and the
second that uses the first as it's input that counts.

GK

On 11/1/07, Bob Heygood <bheygood at abestsystems.com> wrote:
> I think that that will just "count" the CoIDs, I want to know how many 
> different ones are represented.
> One of the issues is I won't know before hand how many to allot for....
>
> I may not be doing a good job of defining this either....
>
> TIA
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan 
> Harkins
> Sent: Thursday, November 01, 2007 1:04 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Counting Co IDs
>
> Totals view will do the trick, use the Count aggregate. That will 
> return just the one value though. If you need additional details, 
> you'll need a subquery.
>
> Susan H.
>
> > What I need is to count the number of companies.
> > It is the last piece of data in my example that I am looking for.
> > How many different companies responded to the question and many yes 
> > and how many no.
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


--
Gary Kjos
garykjos at gmail.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com