Results 1 to 3 of 3
  1. #1
    ice051505 is offline Advanced Beginner
    Windows XP Access 2002
    Join Date
    Feb 2013
    Posts
    76

    Query critieria on Sum of Record

    Hi all, Here is my question
    I have a query example:
    Col1 Col2
    CA Cad
    CA Cas
    BC Bcd
    BC Bcs
    BC Bcf
    BC Bcg

    I want to write on the query expression to give the count of number in Column like,
    Col1 Col2 Count


    CA Cad 2
    CA Cas 2
    BC Bcd 4
    BC Bcs 4
    BC Bcf 4
    BC Bcg 4

    number 2 represent there is two same record in Col1,

    Can anyone tell me how to do that? Thanks!!!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,352
    Is there a unique ID field, such as an autonumber? If not, field can be added then maybe a nested subquery possible. Review http://allenbrowne.com/subquery-01.html

    Another approach is with DCount.

    SELECT *, DCount("Col1","tablename","Col1='" & [Col1] & "'") As CountCol1 FROM tablename;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ice051505 is offline Advanced Beginner
    Windows XP Access 2002
    Join Date
    Feb 2013
    Posts
    76
    Thanks, June 7
    Sorry for reply late! It worked perfact! Thank you so much!!!

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

Similar Threads

  1. Distinct Query Critieria
    By sanywisons232 in forum Queries
    Replies: 3
    Last Post: 01-29-2013, 01:18 PM
  2. Query Critieria using Combo Box Column
    By MintChipMadness in forum Queries
    Replies: 1
    Last Post: 08-23-2012, 02:10 PM
  3. Relating record in form with a record in query?
    By theorythree in forum Access
    Replies: 5
    Last Post: 12-05-2011, 10:05 PM
  4. Select as based on other critieria
    By raweber in forum Queries
    Replies: 2
    Last Post: 11-18-2011, 01:20 PM
  5. Replies: 5
    Last Post: 06-29-2010, 01:24 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