COUNT number of times text in records

Discussion in 'Microsoft Access Queries' started by gator, Jun 3, 2010.

  1. gator

    gator Guest

    I have a field of names where there are multiple records of the same name. I
    want to run a query on this showing a field of the name and a field of the
    number of time it is listed in the dataset. What SQL will do this? I've
    done it in Excel Pivot but I don't know how to convert it to SQL.
     
    gator, Jun 3, 2010
    #1
    1. Advertisements

  2. gator

    gator Guest

    something like......

    TRANSFORM Count(Debits.[Name]) AS [CountOfName]
    SELECT Debits.Name, Count(Debits.[Name]) AS NameTotal
    FROM Debits
    GROUP BY Debits.Name
    PIVOT Debits.NameTotal;

    .....this is as close as I can get but it doesn't work.

    "gator" wrote:

    > I have a field of names where there are multiple records of the same name. I
    > want to run a query on this showing a field of the name and a field of the
    > number of time it is listed in the dataset. What SQL will do this? I've
    > done it in Excel Pivot but I don't know how to convert it to SQL.
     
    gator, Jun 3, 2010
    #2
    1. Advertisements

  3. gator

    gator Guest

    the following SQL works, but it shows the Name as row header and field
    header. I don't nee or want the field header....just the fields Name and
    Total of Name......?

    TRANSFORM Count(Debits.[Name]) AS [CountOfName]
    SELECT Debits.Name, Count(Debits.[Name]) AS [Total Of Name]
    FROM Debits
    GROUP BY Debits.Name
    PIVOT Debits.Name;


    "gator" wrote:

    > I have a field of names where there are multiple records of the same name. I
    > want to run a query on this showing a field of the name and a field of the
    > number of time it is listed in the dataset. What SQL will do this? I've
    > done it in Excel Pivot but I don't know how to convert it to SQL.
     
    gator, Jun 3, 2010
    #3
  4. gator

    gator Guest

    never mind...I got it

    "gator" wrote:

    > I have a field of names where there are multiple records of the same name. I
    > want to run a query on this showing a field of the name and a field of the
    > number of time it is listed in the dataset. What SQL will do this? I've
    > done it in Excel Pivot but I don't know how to convert it to SQL.
     
    gator, Jun 3, 2010
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. John

    Count number of times and item appears.

    John, Aug 6, 2004, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    421
    MGFoster
    Aug 6, 2004
  2. Rebekah
    Replies:
    7
    Views:
    626
    Tom Ellison
    Sep 15, 2004
  3. Randy
    Replies:
    11
    Views:
    1,143
    SteveS
    Feb 5, 2005
  4. Guest

    Count Number of times date selected

    Guest, Apr 11, 2005, in forum: Microsoft Access Queries
    Replies:
    5
    Views:
    232
    Duane Hookom
    Apr 20, 2005
  5. Guest
    Replies:
    3
    Views:
    259
    John Spencer
    Feb 16, 2006
  6. Mario Krsnic
    Replies:
    2
    Views:
    979
    Mario Krsnic
    Oct 27, 2006
  7. BlueWolverine

    Count number of times an entry for list B shows up in List A (cont

    BlueWolverine, Jul 3, 2008, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    227
    Ken Sheridan
    Jul 3, 2008
  8. Jeff Boyce

    Re: Count the number of times a character appears in a field

    Jeff Boyce, Mar 31, 2010, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    455
    Jeff Boyce
    Mar 31, 2010
Loading...