Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2007
    Posts
    17

    Unanswered: HELP WITH "NOT" queries

    Please Help!!

    I have say tables with customers who have bought certain products.

    I know how to make queries so that I can see if they purchased a particular product. Very easy.

    How on Earth do I make queries so that I get a list of customers who havent bought a certain product. Because if I have "Company A" bought 6 products A, B, C, D, E, & F..... if I search using a query to see if they bought "G" company A comes up 6 times because the query is ie" not G" etc. I just want it to list once if G has not been bought by company A.

    its driving me crazy!!

    Thanks so much!


  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    probably need to see your table design

    but
    I think you need to do either a sub select or join to a query which identifes which customers have bought the product do an appropriate left or right join and ont he company ID

    eg
    query1
    select CustomerID from mytable where ItemSold=/productcode/

    query 2
    select myalias.CustomerID from mytable as myalias left join query1 on query1.CustomerID=myalias.CustomerID
    where query1.CustomerID = null

    Query1 identifies all cusotmers who bought the product
    Query2 extracts all customers, and joins then to customers who bough product x, and extracts those customers who didn't buy product X, ie where the query1.customerid is null.. it cannot be found in the list of custoemrs who bought product X


    ..should work, but its gettign late on a bank holiday..

  3. #3
    Join Date
    Jan 2007
    Posts
    17
    Quote Originally Posted by healdem
    probably need to see your table design

    but
    I think you need to do either a sub select or join to a query which identifes which customers have bought the product do an appropriate left or right join and ont he company ID

    eg
    query1
    select CustomerID from mytable where ItemSold=/productcode/

    query 2
    select myalias.CustomerID from mytable as myalias left join query1 on query1.CustomerID=myalias.CustomerID
    where query1.CustomerID = null

    Query1 identifies all cusotmers who bought the product
    Query2 extracts all customers, and joins then to customers who bough product x, and extracts those customers who didn't buy product X, ie where the query1.customerid is null.. it cannot be found in the list of custoemrs who bought product X


    ..should work, but its gettign late on a bank holiday..

    Can I have that in English please LOL!

    Obviously, I am not as conversant with Access as you are, I understand your thought pattern but can you make it a bit simpler to understand for me pls.

    What I tried to do after reading your reply is

    Ran a query to make a table of ALL customers. (table1)

    Ran a query to make a table of ALL customers who bought product A. (Table2)

    Then I tried a delete query to remove customers listed in Table2 from Table1.

    Theoretically that should give me a list in Table1 of customers who did not but Product A. I matched them with the CustomerID to delete those records that matched.

    But blast, I keep getting an error "Could not delete from specified tables"

    What am I doing wrong. Hurry cos I am runing out of hair! LOL!

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Damned lucky THAT didn't run!

    DELETE queries delete the data from the tables themselves. Remember queries are dataless!!

    He's saying that once you have a query that gets all customers who bought product A (queryA), you can then:

    * create a new query based on your customers table AND queryA.
    * join the two by the customerID or PKF by dragging and dropping.
    * Once you have those two tables in your design related, you can change the way they relate (to showing all customers from Customer and only those customers who appear in queryA) by double-clicking the relationship line and selecting the appropriate option.
    * Then you can put the CustomerID into the grid from queryA and use a criteria of IS NULL.

    This should show you all the customers who have not bought product A.

    ST
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Suzywest
    Can I have that in English please LOL!
    error does not compute
    cannot understand
    reboot required

    Quote Originally Posted by Suzywest
    What I tried to do after reading your reply is...

    Ran a query to make a table of ALL customers. (table1)

    Ran a query to make a table of ALL customers who bought product A. (Table2)
    Then I tried a delete query to remove customers listed in Table2 from Table1.
    No thats not what I was suggesting at all

    what I was suggesting is define two queries, one query which identifies which companies purchased product A, and then use that query in another to find those who didn't buy product A. Effectivley once we know who bought product A, using the power of SQL we can use that to tell us who didn't buy Prodcut A

    so first step is to identify which custoemrs bought product A, lets call that query CustomersBuyingProductA
    Code:
    select CustomerID from mytable where ProductID=myparameter
    or if productID is a string/text value
    Code:
    select CustomerID from mytable where ProductID="myparameter"
    if you are pulling that from a form then Id use soemthing like
    Code:
    select CustomerID from mytable where ProductID= froms!myformname!mycontrolname
    save that query as CustomersBuyingProductA
    you will need to replace:-
    mytable: with the correct table name
    CustomerID & productID: with the correct column/field name in the table
    myparameter: with the correct value, however you derive that
    OR
    froms!myformname!mycontrolname: with the name of the form and control which has the value of the product ID you want to look at.... so you could use this query to look at any product ID you sold to find what customers didn't buy that product.

    you can probably quite happily do this in the query browser, however you will definitely need to do the next query in the SQL view of the query designer

    then create a new query lets call it CustomersNOTBuyingProductA
    Code:
    select myalias.CustomerID from mytable as myalias
    left JOIN CustomersBuyingProductA on CustomersBuyingProductA.CustomerID=myAlias.CustomerID
    where myAlias.CustomerID=NULL
    and save that as CustomersNOTBuyingProductA
    as before you will need to change CustomerID
    myAlias should be repalce with somethign that is easy on the eye, its needed to distinguish which CustomerID you want (as we have two tables which both have a customerID.. the alias could be "a" eg a.CustomerID.. its up to you, if the original table name is quite short I'd use that

    if you don't imderstand JOINS then have a google on JOINS in MS Access and see what they can do for you.

    running that query will (should) give you customers who haven't bought product A. you may need to tweak the CustomersBuyingProductA query to make sure you have unique values.. it may require a DISTINCT keyword after the Select.. I can't tell becuase I don't know your table design
    eg select DISTINCT(CustomerID)

  6. #6
    Join Date
    Jan 2007
    Posts
    17
    YOU GUYS ROCK!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!


    Thank you StarTrekker & healdem, you're nothing short of inspirational!!!!

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're welcome
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •