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 online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,831
    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 Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    4,727
    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 (Mendip Data Systems): Website, email
    Try again. Fail again. Fail better.
    A
    sking for help isn't giving up. Its refusing to give up.

  5. #5
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,831
    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
    15,504
    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 - Senior Forums