Results 1 to 15 of 15

Thread: Record Counting

  1. #1
    Join Date
    Aug 2003
    Posts
    31

    Unanswered: Record Counting

    Hi All,

    Im sure this is a really easy question but im not sure how to do it.

    I have this data in a table

    Person Friend
    --------------------------
    Matt James
    Matt John
    Matt Lisa
    Lisa Vicky
    Lisa Tim
    John Steve
    John Harry

    With this data i would like to produce either a report or a query or form showing this:


    Person No of Friends
    --------------------------
    Matt 3
    Lisa 2
    John 2

    I think i have to use the count function but im not sure how to do it.

    Im quite a basic user so can you try and explain as much as poss.

    Cheers in advance

    Matt

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    If your data is always this predictable, you can run this query:

    SELECT Left([Person],InStr([Person]," ")) AS NameGroup, Count(tblNames.Person) AS CountOfPerson
    FROM tblNames
    GROUP BY Left([Person],InStr([Person]," "));


    Basically, you are looking for a space between the first name and the last name. To do this, restrict the view by using left and instr.

    Note: a more practical approach to this would be to have separate fields for first and last names, then just group by and count the first name field.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Aug 2003
    Posts
    31

    Hi

    Hi Mike,

    The two columns are called

    Person and friend.

    There isnt a first and last name column.

    What i was trying to say was that matthew has 3 friends (called John, James and Tim) then John has 2 friend (called ??? and ???)

    I would like a list of all the people in the person column and then a list of how many friends they have. I would like all the names on one page, not only showing one record.

    So it might have


    Matt 3
    John 2
    James 5
    Lisa 6

    I dont want it to just show 1 person ie:

    Matt 3

    I hope this explains it better.

    Cheers

    Matt

  4. #4
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    I'm pretty sure that's what my query does. Maybe I'm missing something.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Mike,
    Your query would work, unless Mattygg's data had a person with a
    space in their name (like Billy Bob). In that case, the query would
    return (assuming he had 4 friends):
    Billy 4

    I think the query can be reduced to:

    SELECT [Person] AS NameGroup, Count(tblNames.Person) AS CountOfPerson
    FROM tblNames
    GROUP BY Person;

    Mark
    Inspiration Through Fermentation

  6. #6
    Join Date
    Sep 2003
    Location
    Texas
    Posts
    13
    HomerBoo had the query right, but since the names are separated into two different fields you won't need the inStr function.


    SELECT [Person] AS NameGroup, Count(tblNames.Friend) AS CountOfFriend
    FROM tblNames
    GROUP BY [Person];

  7. #7
    Join Date
    Sep 2003
    Location
    Texas
    Posts
    13
    that was kinda cool. Yours posted first so I guess you beat me too it redneck.

  8. #8
    Join Date
    Aug 2003
    Posts
    31

    Thanks

    Thank to you both

    I will try that when i get home tonight.

    1 more question

    Where abouts do i type this in.

    Do i create a new query.

    Select the fields

    Person and friend. Then do i create a new field in the query.

    If so where abouts to i type this in.

    Do i have to use sql or can i just copy and paste that code (with the correct field names) into a specific column.

    Cheers

    matt

  9. #9
    Join Date
    Sep 2003
    Location
    Texas
    Posts
    13
    You can change the view of the query to "SQL view" and cut and paste the code that way. Or if you can change your current query to a "Group by" query and count the "friends" field.

  10. #10
    Join Date
    Aug 2003
    Posts
    31

    New question

    Hi All,

    That sounds easy enough, i will try it when i get home tonight.

    I have another thing to ask now.

    Say i add in another column (Date of birth) and i want the report to show only the latest date of birth, for example.

    Person-------Friend--------Date of birth

    Matt----------John----------10/10/70
    Matt----------James--------15/08/83
    Matt----------Neil-----------20/04/92
    Lisa----------Vicky---------25/12/51
    Lisa----------Leanne-------12/03/53

    So from this example the query should show

    Person-----No Friends-----Earliest Date

    Matt--------3----------------10/10/70
    Lisa--------2----------------25/12/25

    Cheers,

    Matt

  11. #11
    Join Date
    Aug 2003
    Posts
    31

    Error

    The lisa column's date of birth should be

    25/12/51 as its the latest date.

    Cheers

    matt

  12. #12
    Join Date
    Sep 2003
    Location
    Texas
    Posts
    13
    'Max" the date field and that should do it.

  13. #13
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    SELECT Left([Person],InStr([Person]," ")) AS NameGroup, Count(tblNames.Person) AS CountOfPerson, Min(tblNames.DOB) AS MinOfDOB
    FROM tblNames
    GROUP BY Left([Person],InStr([Person]," "));

    If you use a column for names and friends, still use the Min function, just don't use the left-instr combo, use Red Neck Geek's SQL instead.

    RNG: Please note my CYA qualifier of, "if your data is that predictable" (imply two words only separated by a space)

    kuuler: you really should parse your data into separate columns, even items like last name, first name. IMHO.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  14. #14
    Join Date
    Aug 2003
    Posts
    31
    Which column do i put "max" into, is it the criteria column?

    Is "Max" all i have to write, or do i have to put Max.dateofbirth

    Or something like that?

  15. #15
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    The SQL is in my post, just update the field names and table name to your SQl window. Min(BirthDayField) as WhateverYouWantToCallIt.

    If you use max, you won't get the results your post said you were looking for.
    All code ADO/ADOX unless otherwise specified.
    Mike.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •