SQL query with GROUP BY option 
Author Message
 SQL query with GROUP BY option

Quote:

>I issued the following SQL command to group the surnames  (LNAME)

>  SELECT LNAME, COUNT(LNAME) AS TALLEY FROM EWRP002 GROUP BY LNAME

>I got the desired results.  Scrolling through the data control, I can see that there
>is only 1 surname per group.

>What I would like to know is how many occurances of each name in the group
>were found.  Is the COUNT value stored somewhere for each unique occurance of LNAME?

The value of Count(LName) is returned as a field in your dynaset/snapshot, just like
the Lname field.  It should be named 'Talley'.  You can access the value returned for
each row of your query by referencing

ds!Talley    or
ds.Fields("Talley") or any other variation.

The 'Talley' is just another field name, so you can reference it the same way that you
use for the 'Lname' field.

--
Jim Gilbert                    all normal disclaimers about my opinions

Monsanto Co., St. Louis, MO



Fri, 26 Dec 1997 03:00:00 GMT  
 SQL query with GROUP BY option
I'm try to summarize some data in a genealogy database that stores its information in a
FoxPro 2.5 database format.

I issued the following SQL command to group the surnames  (LNAME)

  SELECT LNAME, COUNT(LNAME) AS TALLEY FROM EWRP002 GROUP BY LNAME

I got the desired results.  Scrolling through the data control, I can see that there
is only 1 surname per group.

What I would like to know is how many occurances of each name in the group
were found.  Is the COUNT value stored somewhere for each unique occurance of LNAME?
or am I interpreting the meaning of the the SQL command incorrectly?

-----------------------------------------------------------------------------
Roger Lycke           Department of Computing Services, University of Waterloo



Fri, 26 Dec 1997 03:00:00 GMT  
 SQL query with GROUP BY option
Quote:


> >I issued the following SQL command to group the surnames  (LNAME)

> >  SELECT LNAME, COUNT(LNAME) AS TALLEY FROM EWRP002 GROUP BY LNAME

> >I got the desired results.  Scrolling through the data control, I can see that there
> >is only 1 surname per group.

> >What I would like to know is how many occurances of each name in the group
> >were found.  Is the COUNT value stored somewhere for each unique occurance of LNAME?

> The value of Count(LName) is returned as a field in your dynaset/snapshot, just like
> the Lname field.  It should be named 'Talley'.  You can access the value returned for
> each row of your query by referencing

> ds!Talley    or
> ds.Fields("Talley") or any other variation.

> The 'Talley' is just another field name, so you can reference it the same way that you
> use for the 'Lname' field.

> --
> Jim Gilbert                    all normal disclaimers about my opinions

> Monsanto Co., St. Louis, MO

Thanks for the info, after some trial & error, I got the information
I wanted from the Talley field. (I'm somewhat of an amatuer database
programmer).
Also thanks to "yngvesso" for the email response about this
problem, I couldn't reply to your email because our daemon mailer kept
rejecting my replies to you with unknown host.

-----------------------------------------------------------------------------
Roger Lycke           Department of Computing Services

-----------------------------------------------------------------------------



Sun, 28 Dec 1997 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. SQL Query - MAX, Grouping and Sub query

2. Option group/option button

3. Option Group/Option Button

4. Setting SQL Server query processing options via Access/VBA

5. SQL Query / Grouping

6. SQL Query group by month

7. how can I build 2 queries in 1 query(VB SQL query)

8. Option Group & SubForms

9. option group obstinancy

10. Yes/no option groups

11. Option buttons /Groups - Any other ActiveX Controls

12. Linking Option Groups with forms

 

 
Powered by phpBB® Forum Software