Results 1 to 6 of 6
  1. #1
    udigold1 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2010
    Posts
    29

    Count number of records


    Hi,
    I have a a very basic query : "Select Tbl_customer, Tbl_city from Orders",
    but I want to add, using the Expression builder, another field that shows me the number of times each city appears on record. For example, if New York appears 5 times, London 4 times, etc, I want the query to show that.
    How do I do that?

    Thanks in advance!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    thats a different query...
    to count cities:
    select tbl_city, count(Tbl_city) from Orders",

    you want to count city, Customer has no point in the equation.
    now, if you want to count customers IN cities:
    select tbl_city, count(Tbl_customer) from Orders" (not unique customers)

  3. #3
    udigold1 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2010
    Posts
    29
    Doesn't work the way it should,
    I need something like "countif" in excel,
    Thanks

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You need an aggregate query. Click the TOTALS symbol in the ribbon.
    Add cities twice, the first time to group by and the second time to count
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Sorry udigold1 , I assumed you knew about the TOTALS setting.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    udigold1,

    That's a very interesting field naming convention.
    Here's some naming guidelines that may be useful with Access project(s).
    Good luck.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-19-2013, 10:36 AM
  2. Replies: 8
    Last Post: 02-03-2013, 01:19 AM
  3. Replies: 1
    Last Post: 01-24-2013, 05:50 PM
  4. Replies: 6
    Last Post: 07-25-2011, 01:54 PM
  5. count the number of records from a query
    By Nixx1401 in forum Queries
    Replies: 4
    Last Post: 05-24-2011, 06:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums