[AccessD] Counting Co IDs

You can write it with parens, but the query engine will replace them
with square brackets and a period anyhow.

Charlotte Foust 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Monday, November 05, 2007 7:50 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Counting Co IDs

Bob,

I 'think' that you will need to replace the square brackets around your
subquery with parentheses.  Try that and then look at the QBE.  I have
written simple subqueries in the QBE, so they do display there.

Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bob Heygood
Sent: Monday, November 05, 2007 8:44 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [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

--
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