Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query with contradicting data (Access 2000)

    I have to solve an extremely difficult problem for me, which is the following. I must build a query consisting of all the customers from
    the affiliate 2 and only those customers from affiliate 1 which have customer numbers 118,124,2001,2015,2016,2020.There is no logical sequence in these
    customer numbers.
    Is there any trick doing a query like that ? Also. if it is not possible with a query, can i do it with code, since the query will be the record source of a report?

    Below is what i am doing for the time being,but i cannot get them together

    SELECT orders.orderid, orders.orderdate, Customers.Customerid, [order details].ProductID, Customers.afid
    FROM ((Customers INNER JOIN orders ON Customers.Customerid = orders.customerid) INNER JOIN [order details] ON orders.orderid = [order details].OrderID) INNER JOIN products ON [order details].ProductID = products.Productid
    WHERE (((Customers.Affiliate)=2));

    ================================

    SELECT orders.orderid, orders.orderdate, Customers.Customerid, [order details].ProductID, Customers.afid
    FROM ((Customers INNER JOIN orders ON Customers.Customerid = orders.customerid) INNER JOIN [order details] ON orders.orderid = [order details].OrderID) INNER JOIN products ON [order details].ProductID = products.Productid
    WHERE (((Customers.Customerid) In (118,124)) AND ((Customers.Affiliate)=1));

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query with contradicting data (Access 2000)

    You can us a union query for this. Union queries can only be created in SQL view, not in design view. There are two possible approaches:

    1. Save the two queries whose SQL you posted. Say that you name them qryAf1 and qryAf2. The SQL for the union query is

    SELECT * FROM qryAf1
    UNION
    SELECT * FROM qryAf2;

    2. Put the SQL for the two queries directly into the union query:

    SELECT orders.orderid, orders.orderdate, Customers.Customerid, [order details].ProductID, Customers.afid
    FROM ((Customers INNER JOIN orders ON Customers.Customerid = orders.customerid) INNER JOIN [order details] ON orders.orderid = [order details].OrderID) INNER JOIN products ON [order details].ProductID = products.Productid
    WHERE (((Customers.Affiliate)=2))
    UNION
    SELECT orders.orderid, orders.orderdate, Customers.Customerid, [order details].ProductID, Customers.afid
    FROM ((Customers INNER JOIN orders ON Customers.Customerid = orders.customerid) INNER JOIN [order details] ON orders.orderid = [order details].OrderID) INNER JOIN products ON [order details].ProductID = products.Productid
    WHERE (((Customers.Customerid) In (118,124,2001,2015,2016,2020)) AND ((Customers.Affiliate)=1));

    Note that there is no ; after the SQL of the first query.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query with contradicting data (Access 2000)

    Thank you so much for your reply. The uinion query you have suggested
    is superb ! !. I have now another question,with the grouping, but i will create
    a new trend, since actuallly this trend has received a magnifcient reply .

Posting Permissions

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