Results 1 to 4 of 4
  1. #1
    MeganDoak is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2010
    Posts
    1

    Queries

    Hello,


    I'm hoping someone might know how I can word what I need in a way access can perform it.
    I have a table that holds hundreds of case numbers. I'm currently working on a query that tells me how many cases we have. The tricky part is that I want it to tell me the number of cases and not just add up the case numbers. I also want it to subtract any duplicate numbers. Case numbers are often repeated and I want it to only count a number that is duplicated once. I'm not sure how to do this.
    Does anyone have any ideas?
    Thanks,
    Megan

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    I dont have Access at home, but see if this query works for you. I'm not 100% sure DISTINCT and Count() will work together. let me know how it goes.

    SELECT DISTINCT Count(CaseNo)
    FROM myTable
    Last edited by TheShabz; 04-21-2010 at 05:59 PM. Reason: fixed SQL

  3. #3
    ConneXionLost's Avatar
    ConneXionLost is offline Simulacrum
    Windows XP Access 2003
    Join Date
    Jan 2010
    Location
    Victoria, Canada
    Posts
    291
    Hi Megan,

    Give this a try:

    Code:
    SELECT Count(CaseNo) AS CountOfCaseNo
    FROM (SELECT DISTINCT CaseNo FROM tblCase);
    Cheers,

  4. #4
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    conn's will probably work better. I was going to offer that compound query had mine not worked. Thanks conn.

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

Similar Threads

  1. how i can run a sum queries
    By ahmed-aljawad in forum Queries
    Replies: 3
    Last Post: 04-17-2010, 11:06 AM
  2. Another problem with queries
    By Viggen66 in forum Queries
    Replies: 3
    Last Post: 02-23-2010, 05:01 PM
  3. Cross Tab Queries
    By Nixx1401 in forum Access
    Replies: 6
    Last Post: 02-16-2010, 11:55 AM
  4. Replies: 5
    Last Post: 02-08-2010, 06:12 PM
  5. need help with queries
    By gromit95 in forum Queries
    Replies: 1
    Last Post: 02-06-2009, 06:50 AM

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