Select 'ONLY = TO'

Discussion in 'Access Table Design' started by ainese, Nov 15, 2011.

  1. ainese

    ainese Guest

    I have a table with ESN and SERVICE TYPE and SUBSCRIBER TYPE. ESN can
    appear more than once depending on the number of services associated.
    Each service type has a charge.

    To date I have the below code - where i'm trying to count esn where
    service type is only = basic and not equal to data push or
    tracking....


    SELECT Count(F.ESN) AS ["Basic Activations"]
    FROM Data AS F
    INNER JOIN Data AS G ON (F.ESN= G.ESN AND F.SERVICE= "Basic" AND
    G.SERVICE <>"Data Push" AND G.SUBSCR_TYPE = "Service Activation")
    WHERE G.ESN NOT IN
    (SELECT ESN FROM Data WHERE SERVICE = 'Tracking');


    I added G.SERVICE <> "Data Push" but with the inner join I'm getting
    some duplicates of 'Data Push' due to the
    INNER JOIN


    How do I omit them,


    Can anyone advise me on how to do this please?


    Thanks
     
    ainese, Nov 15, 2011
    #1
    1. Advertisements

  2. ainese

    SoftwareMatters

    Joined:
    Feb 9, 2012
    Messages:
    25
    Likes Received:
    0
    Location:
    Dorset
    You need to switch grouping on
     
    SoftwareMatters, Feb 20, 2012
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Nick Mirro

    Select query non-updateable

    Nick Mirro, Oct 7, 2003, in forum: Access Table Design
    Replies:
    7
    Views:
    168
    Nick Mirro
    Oct 10, 2003
  2. Kishor

    Select in to

    Kishor, Oct 30, 2003, in forum: Access Table Design
    Replies:
    1
    Views:
    79
    Duane Hookom
    Oct 31, 2003
  3. Özden Irmak
    Replies:
    1
    Views:
    127
    Michel Walsh
    Nov 25, 2003
  4. Nate Adams

    Adding new records to a select query

    Nate Adams, Jan 12, 2004, in forum: Access Table Design
    Replies:
    1
    Views:
    86
    Allen Browne
    Jan 12, 2004
  5. Andreea

    How to select multiple frm drop dn list?

    Andreea, Jan 22, 2004, in forum: Access Table Design
    Replies:
    1
    Views:
    82
    Immanuel Sibero
    Jan 22, 2004
  6. Siegfried Heintze

    How to select only most recent records?

    Siegfried Heintze, Aug 17, 2006, in forum: Access Table Design
    Replies:
    3
    Views:
    86
    KARL DEWEY
    Aug 18, 2006
  7. LindaC

    Select only records that are left.

    LindaC, May 15, 2007, in forum: Access Table Design
    Replies:
    1
    Views:
    85
    Jason Lepack
    May 15, 2007
  8. LindaC

    Select Only Some Records

    LindaC, May 25, 2007, in forum: Access Table Design
    Replies:
    4
    Views:
    64
    Steve
    May 28, 2007
Loading...